In this Python SQLite article we want to learn about Python SQLite Tutorial, Python is a powerful programming language and it is commonly used for different applications, including web development, data analysis and scientific computing. in this tutorial we want to talk about SQLite database programming with Python.
What is SQLite ?
SQLite is popular database management system and it is mostly used by Python developers. SQLite is serverless, self contained, zero configuration and transactional database engine that is embedded in most modern operating systems. this means that it is lightweight and easy to use, and it is a great choice for small to medium sized applications.
In this Python SQLite tutorial, we want to cover the basics of working with SQLite databases in Python. we will go through the steps of creating and connecting to a database, inserting and retrieving data and updating and deleting data.
For working with SQLite database in Python, we need to first create a database and connect to it. for creating a new database, we simply need to import the SQLite3 module and execute this code.
|
1 2 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') |
In the above code, we are going to create a new database called geekscoders.db and after that we use connect() method of the sqlite3 module. if the database already exists, connect() method will connect to it. if it does not exist, it will create a new one.
We have our database, now we need to create our table in the database, we can use this code, this code will create employees table in our database, but right now we don’t have any data in our database.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') # create a table conn.execute('''CREATE TABLE employees (name TEXT, age INT, salary REAL)''') |
You can see that we have our SQLite table.
After that we have created and connected to a database, we can start inserting data into it. for inserting data into SQLite database, we use execute() method of the database connection object, we want to add two data in the table.
|
1 2 3 4 5 6 7 8 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') #Inserting data to the table conn.execute("INSERT INTO employees (name, age, salary) VALUES ('GeeksCoders', 20, 50000)") # commit changes to the database conn.commit() |
This will be the result
For retrieving data from SQLite database, we can use execute() method of the database connection object with SELECT statement, in this code we are selecting all records from a table called employees and print each row to the console. execute() method returns a cursor object that we can iterate over to retrieve the rows.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') cursor = conn.execute("SELECT * FROM employees") for row in cursor: print(row) |
This will be the result
For updating data in SQLite database, we use execute() method of the database connection object with an UPDATE statement, this code will update the name.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') conn.execute("UPDATE employees SET name = 'updated' WHERE name = 'Parwiz'") conn.commit() |
This will be the result
To delete data from SQLite database, we use execute() method of the database connection object with DELETE statement.
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect('geekscoders.db') conn.execute("DELETE FROM employees WHERE name = 'updated'") conn.commit() |
