Prerequisites:
You have to complete the following tasks before executing the code of this tutorial:
Install Python 3+ in the system if it is not installed before. Python3 is installed by default on Ubuntu 20+. Run the following command to check the installed version of Python:
The following output shows that Python version 3.10.6 is installed on the system:
Install the sqlite3 in the system if it has not been installed before. Sqlite3 is installed by default on Ubuntu 20+. Run the following command to check the installed version of Python:
The following output shows that SQLite version 3.37.2 is installed on the system:
Database Operations Using Python and SQLite
The basic database operations using the SQLite database and Python script are shown in this tutorial. The list of database tasks that are covered in this tutorial is mentioned in the following:
- Make an SQLite Database Connection
- Create a New Table
- Insert the Data into the Table
- Read the Data from the Table
- Update the Data of the Table
- Delete the Data from the Table
SQLite Database Connection Using Python
You can create a new SQLite database file or use an existing SQLite database file to work with the Python script. The sqlite3 module of Python is used to work with SQLite database using the Python script. Create a Python file with the following script that shows the method of connecting with the SQLite database. The database filename is taken from the user. If the database filename exists in the current location, Python creates a valid database object to work on that database. If the database filename does not exist in the current location, a new SQLite database file is created in the current location and makes the connection with the database. A success message is printed after creating the valid database object and the database connection is closed at the end of the script.
#Import the required module
import sqlite3
#Take the SQLite database filename from the user
filename = input ("Enter a sqlite filename:")
#Make connect with the existing database or after creating the database
db = sqlite3.connect(filename)
#Print the success message
print ("Database connected successfully.");
#Close the database connection
db.close();
The following output shows that the “company.db” is taken as the database filename and the database is connected successfully because the success message, “Database connected successfully”, is printed.
Create an SQLite Database Table Using Python
If you want to create a new table, it is required to remove the table from the database if the table exists earlier. Create a Python file with the following script that shows the method of creating a new table by removing the existing table and checking whether the new table is created or not in the SQLite database. At the beginning of the script, the database connection is created with the “company.db” database file. If the “vendor” table exists in the database, it is deleted by the DROP TABLE statement. Next, the “vendor” table is created with four fields and a primary key. The cursor object is created to check whether the new table is created or not. The sqlite_master table contains all the table information of the connected database. So, if the “vendor” table is created, the returned value of the SELECT query is more than 0. The count() function is used in the SELECT query to check the existence of the “vendor” table.
#Import the required module
import sqlite3
#Make connection with the existing database
db = sqlite3.connect('company.db');
#Define the table name
tablename = 'vendor'
#Drop the table if exists in the database
db.execute('DROP TABLE IF EXISTS ' + tablename)
#Define the query to create the table
query = '''CREATE TABLE vendor
(id INT PRIMARY KEY NOT NULL,
name CHAR(50) NOT NULL,
address TEXT NOT NULL,
email CHAR(50));'''
#Execute the query to create the table
db.execute(query)
#Declare cursor
cr = db.cursor()
#Execute the query to check whether the table is created or not
cr.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='vendor' ''')
#Check the counter value
if cr.fetchone()[0]==1 :
#Print the success message
print ("%s table created successfully." %tablename)
#Close the database connection
db.close();
The following output appears if the vendor table is created in the database:
Insert the Data into the SQLite Table Using Python
One or multiple records can be inserted into the database table using single or multiple INSERT queries. Create a Python file with the following script that inserts four records into the “vendor” table using a single INSERT query. The connect() method is used to connect with the “company.db” database file. The execute() method is used to execute the INSERT INTO query to insert the records into the “vendor” tables. Next, the cursor object is created to check whether the records are inserted successfully or not. The SELECT query is used to count the total number of records of the “vendor” table that contains the inserted “id” values. If the records are inserted successfully, the total number of rows that are returned by the SELECT query is more than 0 and a success message is printed. The commit() method is used in the script to confirm the insertion task.
#Import the required module
import sqlite3
#Make connection with the existing database
db = sqlite3.connect('company.db');
#Define the query to insert records into the table
query = '''INSERT INTO vendor (id, name, address, email)
VALUES
(1, 'Nahar Enterprise', '56, Motijeel, dhaka', '[email protected]'),
(2, 'Rahat Electronics', '105, Mogbazar, dhaka', '[email protected]'),
(3, 'Vision Emporium', '45/A, Dhanmondi, dhaka', '[email protected]'),
(4, 'Walton Plaza', '78, Jigatala, dhaka', '[email protected]');'''
#Execute the query to insert data
db.execute(query)
#Declare cursor
cr = db.cursor()
#Execute the query to check the inserted number of rows
cr.execute('SELECT count(*) FROM vendor WHERE id between 1 and 4;')
#Count the total number of rows of the table
rows = cr.fetchone()[0];
#Check the counter value
if rows > 0 :
#Print the success message
print ("%s rows inserted into the table." %rows)
#Confirm the insertion
db.commit()
#Close the database connection
db.close();
The following output shows that the records are inserted successfully:
Read the Data from the SQLite Table Using Python
All or the particular data can be retrieved from the SQLite database table using the SELECT query and it is one of the major parts of the CRUD operation. Create a Python file with the following script that reads all records and the particular records of the “vendor” table where the value of the name field partially match the input value that is taken from the user. The connect() method is used to connect with the “company.db” database file. The cursor object is created to read the result set that is returned by the SELECT query and the execute() method is used to execute the SELECT query. The fetchall() method is used to read the result set of the query in an array. The “for” loop is used to read and print the values of the result set that is returned by both SELECT queries.
#Import the required module
import sqlite3
#Make connection with the existing database
db = sqlite3.connect('company.db')
#Define the query to read all records from the vendor table
query = 'SELECT * FROM VENDOR;'
#Declare cursor
cr = db.cursor()
#Execute the query to read all records
cr.execute(query)
#Store all retrieved data into the array
rows = cr.fetchall()
print ("All records of vendor table:")
#Iterate the array values and print
for row in rows:
print(row)
name = input("Enter the vendor name:");
#Define the query to read all records from the vendor table
query = 'SELECT * FROM vendor WHERE name LIKE "%' + name + '%";'
#Execute the query to read specific records
cr.execute(query)
#Store all retrieved data in the array
rows = cr.fetchall();
print ("Specific records of vendor table:")
#Iterate the array values and print
for row in rows:
print(row)
# Close the connection
db.close()
The “Walton” string value is taken as an input value from the user. The following output shows that all records of the “vendor” table are printed for executing the first SELECT query and the records of the “vendor” table that contains the “Walton” word is printed for executing the second SELECT query:
Update the Data of the SQLite Table Using Python
Updating the data from the SQLite database table is a major part of the CRUD operation. All records can be updated or the particular records can be updated from the table based on the condition. Create a Python file with the following script that updates the email field of the “vendor” table where the “id” value matches with the input value that is taken from the user. The connect() method is used to connect with the “company.db” database file. The execute() method is used to execute the UPDATE query. The cursor object is created to read the result set that is returned by the SELECT query after updating the record of the table. The fetchall() method is used to read the result set of the SELECT query in an array like the previous example. The “for” loop is used to read and print the values of the result set that is returned by the SELECT query. The commit() method is used to confirm the update operation of the database.
#Import the required module
import sqlite3
#Make connection with the existing database
db = sqlite3.connect('company.db')
id = input("Enter the vendor id:")
#Define the query to update the specific record of the vendor table
query = 'UPDATE vendor SET email = "[email protected]" WHERE id = ' + id + ';'
db.execute(query)
#Confirm the update task
db.commit()
#Define the query to read all records from the vendor table
query = 'SELECT * FROM VENDOR;'
#Declare cursor
cr = db.cursor()
#Execute the query to read all records
cr.execute(query)
#Store all retrieved data in the array
rows = cr.fetchall()
print ("All records of the vendor table after update:")
#Iterate the array values and print
for row in rows:
print(row)
# Close the connection
db.close()
Three (3) is taken as the “id” value from the user. The value of the email field is “[email protected]” before executing the script. The following output shows that the value of the email field of the “vendor” table is changed to “[email protected]” which contains the “id” value of 3 after executing the script:
Delete the Data from the SQLite Table Using Python
Removing the data from the database is one of the major parts of the CRUD operation. All records can be removed from the table or the particular records can be removed based on the condition. Create a Python file with the following script that deletes the record of the “vendor” table where the “id” value matches with the input value that is taken from the user. The connect() method is used to connect with the “company.db” database file. The execute() method is used to execute the DELETE query. The first SELECT query is used here to check whether any record exists or not with the “id” value that is taken from the user. The second SELECT query is used to print all records after deleting the record. The cursor object is created to read the result set that is returned by the SELECT query of the “vendor” table. The fetchall() method is used to read the result set of the SELECT query in an array like the previous example. The “for” loop is used to read and print the values of the result set that is returned by the SELECT query. The commit() method is used to confirm the delete operation of the database. If the commit() command is not used for INSERT, UPDATE, and DELETE query, the original database will not be modified. After executing the commit() method, you can call the rollback() method to revert the last change that was not shown in this tutorial.
#Import the required module
import sqlite3
#Make connection with the existing database
db = sqlite3.connect('company.db')
id = input("Enter the vendor id to delete:")
#Declare cursor
cr = db.cursor()
#Execute the query to check the inserted number of rows
cr.execute('SELECT count(*) FROM vendor WHERE id = ' + id + ';')
#Count the total number of rows of the result set
rows = cr.fetchone()[0];
#Check the counter value
if rows == 0 :
#Print the success message
print ("No vendor exists of this id")
else:
#Define the query to delete the specific record of the vendor table
query = 'DELETE FROM vendor WHERE id = ' + id + ';'
db.execute(query)
#Confirm the delete task
db.commit()
#Define the query to read all records from the vendor table
query = 'SELECT * FROM VENDOR;'
#Execute the query to read all records
cr.execute(query)
#Store all retrieved data in the array
rows = cr.fetchall()
print ("All records of the vendor table after delete:")
#Iterate the array values and print
for row in rows:
print(row)
# Close the connection
db.close()
The following output shows that 4 is taken as the “id” value from the user and the 4th record of the “vendor” table is removed after executing the script:
Conclusion
Different types of applications can be easily developed by the Python language. Python supports both the structured programming and object-oriented programming. If it is required to develop a Python application with a very lightweight database, the SQLite database tool is better to use. The methods of creating tables, inserting the data into the table, reading the data from the table, updating the data of the table, and deleting the data from the table are shown in this tutorial using multiple Python scripts. The new SQLite users will be able to work with the SQLite database using the Python script after reading this tutorial properly.
