Mastering the SQLite Database in Python

SQLite is a relational database management system based on the SQL language; it is a serverless, Zero-configuration database engine. It is one of the most popular database engines and very easy to be used in small applications. It creates only one disk file to store the entire database, which makes the file portable. It is used in the Android OS as the primary source to store data. It is also used by Google Chrome to store site data and user data, including passwords in the local machine.

Advanced working with SQLite Database in Python

In this tutorial, the topics that will be covered are: inserting images in an SQLite table, Listing the tables present in a database, Identify total changes since the database is connected, Backup a database, Dumping an SQLite database, Rollback in SQLite, Deleting records from a table, Dropping a table, and SQLite database exceptions.

You may also want to see the first part of this tutorial, which presents the basics of SQLite, Advantages of using it, Connecting to a database file, Creating a table in the database, Inserting data into the table, Querying data from the table, updating the table and many more.

Files and images in the SQLite database

While working with databases, there are situations where you need to insert images or files into a database or export from it. For instance, If you are creating a database to store employee data, you may also need to insert pictures of every employee in the database.

To add images in an SQLite database, we need to use the BLOB data type of SQLite. The BLOB() data type is used to store large objects typically large files such as images, music, videos, documents, PDF, etc. The first step is to convert the data and images into the byte object of Python, which is similar to the BLOB data type of SQLite. Before proceeding, create a table named student in the database with the fields id, name, images, marks. Run the following code to create the table.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
table = cur.execute(""" CREATE TABLE student( id INT PRIMARY KEY, name TEXT, images BLOB, marks TEXT ); """)
print("\n [+] The table has been created successfully")
cur.close()
conn.commit()
conn.close()

This program will create a new table with the name student. You will see the following output in the terminal.

Inserting an Image

To insert an image in an SQLite database, transform the image in a python byte object and then insert it into the images column, which accepts BLOB data. Run the following code to add an image img.png in the database using Python.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
with open("img.png","rb") as file:
      data = file.read()

python_tuple = (101,"robin",data,"90")
print("\n [+] The image has been successfully imported")
print("\n [+] Now inserting in the database")
cur.execute("INSERT INTO student (id,name,images,marks) VALUES (?,?,?,?)", python_tuple)
print("\n [+] The Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close()

This program will insert the image into the student database that you created. You will see the following output.

In the above program, we have opened the file in binary mode and read every byte and store it in variable data. Then we use that variable in the INSERT statement to insert the image in the database.

Retrieving an image

To retrieve an image from a database, fetch the row using a select statement and then access the binary data of the image into a python variable, which will be stored in an image file. See the following code for illustration.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
print("\n [+] Retrieving the image")
cur.execute("SELECT * FROM student")
ret = cur.fetchall()
for i in ret:
     data = i[2]

with open("img2.png","wb") as file:
          file.write(data)

print("\n [+] The image has been saved")
cur.close()
conn.commit()
conn.close()

This simple program will retrieve the image from the database and save it in the disk named img2.png. You can also choose another name for the image file. The output of the program is shown below.

List all the Tables of a Database

In a database, we can create numerous amount of tables. So there is also a need to list all the tables present in a database. To list the tables present in a database, query the sqlite_master table using the SELECT statement of SQL. The syntax of the query will be:

SELECT name FROM sqlite_master WHERE type='table'

Here is how we use this query to list all the tables present in our database.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT name from sqlite_master where type='table'")
rows = cur.fetchall()
print(rows)
cur.close()
conn.commit()
conn.close()

The above code will list all the tables present in our database. The output produced by the code when executed is as follows. You may see some other output depending on the tables you have created in the database.

Identifying total changes since connected to the database

In any situation, it is useful to identify the number of rows that have been modified, inserted, or deleted since the database has been connected. For that, use the total_changes() method of the connection object, which will return the total number of database rows that have been affected since the connection. Let us see an example demo to know how it works.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to both the database")
cur = conn.cursor()
print("\n [+] Both the Cursor has been set up successfully")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',' ',99 )")
cur.execute("INSERT INTO student (id, name,images, marks) VALUES (150, 'Sam', ' ', 97)")
changes = conn.total_changes
print("\n [+] Total now of rows changes is :",changes)
conn.commit()
cur.close()
conn.close()

The above program will print the number of rows changes in the current connection. You will see the following output.

Rollback in SQLite

When it comes to undoing some tasks, you can use the rollback() function. This method can be used to undo task which has been done after the last commit. See the below example for an illustration.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to both the database")
cur = conn.cursor()
print("\n [+] Both the Cursor has been set up successfully")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',' ',99 )")
cur.execute("INSERT INTO student (id, name,images, marks) VALUES (100002, 'Sam', ' ', 97)")
conn.commit()
print("\n [+] The Two row has been inserted successfully")
cur.execute("SELECT * FROM student")
first = cur.fetchall()
print("\n [+] The new records in the database are :")
for i in first:
    print(i)

cur.execute("INSERT INTO student (id, name, images, marks) VALUES (10003, 'Kishan', ' ', 100)")
cur.execute("INSERT INTO student (id, name, images, marks) VALUES (10004, 'Ankit', ' ', 100)")
print("\n [+] The Two row has been inserted successfully but not committed")
conn.rollback()
print("\n [+] We have roll back the previous commands so the new data will not been inserted")
conn.commit()
cur.execute("SELECT * FROM student")
second = cur.fetchall()
print("\n [+] The new records in the database are :")
for i in second:
     print(i)

cur.close()
conn.close()

In the above example, the first two insertion statements will insert the data as given, but the last two insert statements will be rollback so they will not add any data into the table. The output will be as shown below.

Backup a Database

While working with the database, it is essential to take a backup of the database. The sqlite3 module provides a function to take the backup of the database. Using the backup() method of the connection object, we can make the backup of the SQLite database. The basic syntax of the backup method is:

backup(target, *, pages=0, progress=None, name="main", sleep=0.250)

By default, or when pages are either 0 or a negative integer, the entire database is copied in a single step, which is preferable for a small database; otherwise, the method performs a loop copying up to pages at a time that could be done with the extensive database. The name argument shows the database name that will be copied: it must be a string containing either the default, to indicate the main database, or to indicate the temporary database. The sleep argument specifies the time in seconds to sleep by between attempts to backup the remaining pages. It can be either as an integer or a floating-point value.

Let us take a backup of the database.db database we have been using in the tutorial.

import sqlite3
conn_main = sqlite3.connect("sample.db")
conn_backup = sqlite3.connect("sample_backup.db")
print("\n [+] Successfully connected to both the database")
cur_main = conn_main.cursor()
cur_backup = conn_backup.cursor()
print("\n [+] Both the Cursor has been set up successfully")
conn_main.backup(conn_backup, pages=0, progress=None, name="main")
print("The database has been backup successfully")
cur_main.close()
cur_backup.close()
conn_main.commit()
conn_backup.commit()
conn_main.close()
conn_backup.close()

In the above code, the two databases are connected, one is the database of which we want to make a backup, and the second is the database in which we will take the backup. Use the backup() method of the first database connection object to make a backup. This function accepts the connection object of the second database as the target to create a backup on the other database. Use the pages = 0 arguments, so the process will take place in one step, which is recommended for small databases. This program will create a new database name sample_backup.db and fill it with the backup of the first database. You may see a new database has been created in the current folder with the same file size as the previous.

Dumping an SQLite database

Dumping databases is an important task. Usually, a dump file is a set of SQL statements for the data, which is generally used for backup. We can dump a database using the dump() method. See the below example to know how to drop an SQLite database.

import sqlite3

con = sqlite3.connect("database.db")
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

The above program will dump the database sample.db, and it will save the dumped data into a file named dump.sql. You can see the data present in the directory where the python files are current and open it using any text editor.

executemany() method of SQLite3

The executemany() method executes an SQL command against all parameter sequences or mappings found in the sequence seq_of_parameters. For simplicity, this method can be used to execute most SQL commands in one line. E.g., We can insert any number of rows through a python list using this command. See the below example for the illustration.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
python_list = [(10000000 ,'vivek',' ','10'),
               (100000001,'rose',' ','21'),
               (100000002,'robin',' ','31'),
               (100000003,'Dev',' ','4'),
               (100000004,'michael',' ','52')
]
cur.executemany("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?)",python_list)
print("\n [+] All the Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close()

The above program will insert all the data given in the python list. The output produce by the program is shown below.

Delete Records from a Table

We can use the DELETE operation to delete records from a table. We can quickly remove a row utilizing the DELETE operation with the WHERE clause. The basic syntax for the DELETE statement is:

DELETE from table_name WHERE some_condition;

Let’s see an example. We will delete the row with id 1001 from the employee table of our database.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("DELETE FROM student WHERE id=1001")
print("\n [+] The row has been deleted Successfully ")
cur.execute("SELECT * FROM student")
data = cur.fetchall()
for row in data:
   print(row)

cur.close()
conn.commit()
conn.close()

The above code will Delete the row with ID 1001. You can see from the return of the SELECT statement that the row has been removed. The output of the program is as shown below.

Drop a Table

We can quickly drop or delete a table using the SQLite DROP statement. The syntax of the DROP statement is as shown below:

DROP table table_name 

If the table does not exist, then SQLite will throw an error, so to prevent this, we can use the if exist tag with the DROP statement. See the below syntax:

DROP table if exists table_name

Let us see how we can use this statement with the python sqlite3 module to delete a table. In this program, we will remove the student table we have created earlier.

import sqlite3

conn = sqlite3.connect("sample.db")
conn.text_factory = str
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("DROP TABLE IF EXISTS student")
print("\n [+] The table has been drop successfully")
cur.close()
conn.commit()
conn.close()

The above program will delete the table student from the sample database. We can use the list table command we have seen earlier to see if the table is deleted. The output of the program is as shown below.

SQLite Database Exceptions

Some SQLite database exceptions may be raised due to some error. Let’s see a little bit of when those errors were raised.

• sqlite3.Warning: It is a subclass of exception. This exception shows some warnings, and those can be ignored in many cases.
• sqlite3.Error: It is also a subclass of exception. It is the base class of all other exceptions in the sqlite3 module.
• sqlite3.DatabaseError: These are the errors that are raised due to some errors in databases. For Eg:- If we try to connect to an encrypted database or a wrong database file, then it will show a DatabaseError that the data is encrypted or not a valid database file.
• sqlite3.IntegrityError: This exception is a subclass of a DatabaseError. We will get this exception when the relational integrity of a database is affected, e.g., A failure in the foreign key check.
• sqlite3.ProgrammingError: This exception is also a subclass of DatabaseError. This exception is raised because of programming errors, e.g., creating a table with the same name which already exists, syntax error in the SQL queries, etc.
• sqlite3.OperationalError: It is also a subclass of DatabaseError. This exception raised for errors that are related to the database’s operation and are not in our control. For example, an accidental disconnect wit the system, server down, a timeout occurs, data source issues, machine shutdown, etc.
• sqlite3.NotSupportedError: This exception is raised when the database does not support a database API that used.

This is the list of all SQLite exceptions; we can handle those exceptions in our programs using basic try/except error handling method of Python.

Conclusion