SQLite

SQLite Python Examples

The database is used to store the application data in a structured format so that the data can be easily accessed, changed, and deleted by executing the appropriate SQL statement. Python supports many database engines to store the data. SQLite is one of them. Python3 has an sqlite3 module to connect with the database of the SQLite and perform different types of database operations. The methods of connecting with the SQLite database and performing the CRUD operations using the Python script are shown in this tutorial.

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:

$ python3 --version

The following output shows that Python version 3.10.6 is installed on the system:

p0-1

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:

$ sqlite3 --version

The following output shows that SQLite version 3.37.2 is installed on the system:

p0-2

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:

  1. Make an SQLite Database Connection
  2. Create a New Table
  3. Insert the Data into the Table
  4. Read the Data from the Table
  5. Update the Data of the Table
  6. 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.

#!/usr/bin/python

#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.

p1

Go to top

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.

#!/usr/bin/python

#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:

p2

Go to top

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.

#!/usr/bin/python

#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', 'nahar@gmail.com'),

(2, 'Rahat Electronics', '105, Mogbazar, dhaka', 'rahat@gmail.com'),

(3, 'Vision Emporium', '45/A, Dhanmondi, dhaka', 'vision@gmail.com'),

(4, 'Walton Plaza', '78, Jigatala, dhaka', 'walton@gmail.com');'''


#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:

p3

Go to top

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.

#!/usr/bin/python

#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:

p4

Go to top

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.

#!/usr/bin/python

#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 = "text@example.com" 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 “vision@gmail.com” before executing the script. The following output shows that the value of the email field of the “vendor” table is changed to “text@example.com” which contains the “id” value of 3 after executing the script:

p5

Go to top

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.

#!/usr/bin/python

#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:

p6

Go to top

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.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.