Python SQLite Module is a lightweight library that provides an easy way to do the often difficult task of SQL type Database Management. This, unlike other Database Systems, does not require a dedicated server process/machine.
This combines the easy to use SQL type queries for Database Management, but uses it for internal storage of objects, in the most Pythonic way possible!
Let’s quickly go through this library, and make our own database for our application!
Python SQLite
Python SQLite comes along with any installed version of Python, so there’s no need to install it with pip. That’s right, you already have it!
To import it, since we’ll be using Python3, we will import the sqlite3 module.
import sqlite3
Let’s start with the main module methods now.
Create the DataBase
Although we did mention that there’s no external server running, and everything is inside the current machine, since this is the standard protocol for Database Systems, this module also acts like one by doing that!
We’ll create a connection to the Python SQLite Database. This only allocates memory for the database and retrieves any data from the database file, so that the Database is constructed from the db file.
import sqlite3
# Establish a connection to the Database and create
# a connection object
conn = sqlite3.connect('database.db')
Here, database.db is the database file, to where the data will be stored. So we invoke the database from this file.
NOTE: If this file does not exist, the database will automatically create it for you, and use that file to update the database from the next time onward.
If we already have the file, we must make sure we accidentally don’t overwrite it. So, we must check if the file already exists in the system, using the below function.
import os
def check_db(filename):
return os.path.exists(filename)
There is another option, though, to directly construct the database in RAM, if you simply want a temporary database, by using the special file :memory:.
import sqlite3
conn = sqlite3.connect(':memory:')
The problem with the above method is that we need to explicitly close the connection at the end using conn.close(). Python has a very good solution to that – context managers. A with statement context manager will automatically do that for you, so our modified code will now be:
import sqlite3
db_file = 'database.db'
with sqlite3.connect(db_file) as conn:
print('Created the connection!')
print('Automatically closed the connection!')
Output
Created the connection!
Automatically closed the connection!
Create the Cursor Object
Once the connection is established, we have a connection object, from which we can construct a Cursor object.
# Create a cursor to the Database
c = conn.cursor()
This cursor is a pointer to the database, which is used to navigate it so that we can move to locations and execute SQL queries.
Create our Database Scheme
Before we start executing queries, we need to first create a schema based on how the records will be stored. This comes as a Database Table, which is a structure having rows and columns.
The rows correspond to the actual data values, while the columns correspond to its attribute names.
Let’s create a simple schema for storing images. We’ll define any image as having a Name, a Size, and the Date of creation of the image. The actual data will be somewhere in memory. The schema only defines how the data is organized.
Since we’re going to define the table like this, the schema for the table will look like the below figure.
We can execute queries using the cursor, by passing cursor.execute('SQL_QUERY'), using an SQL query.
Let’s write the SQL query for creating this schema.
CREATE TABLE images(
name text primary key,
size text,
date date
);
We can pass this query as a string, but if you want to perform longer queries, it is easier to simply create a .sql file and then read from the file instead, using cursor.executescript().
So let’s put our query into a file called schema.sql, and read from that.
import sqlite3
import os
def check_db(filename):
return os.path.exists(filename)
db_file = 'database.db'
schema_file = 'schema.sql'
if check_db(db_file):
print('Database already exists. Exiting...')
exit(0)
with open(schema_file, 'r') as rf:
# Read the schema from the file
schema = rf.read()
with sqlite3.connect(db_file) as conn:
print('Created the connection!')
# Execute the SQL query to create the table
conn.executescript(schema)
print('Created the Table! Now inserting')
conn.executescript("""
insert into images (name, size, date)
values
('sample.png', 100, '2019-10-10'),
('ask_python.png', 450, '2019-05-02'),
('class_room.jpeg', 1200, '2018-04-07');
""")
print('Inserted values into the table!')
print('Closed the connection!')
Output
Created the connection!
Created the Table! Now inserting
Inserted values into the table!
Closed the connection!
Get the values from the Table
Now that we have our table with initial values, let’s query the table to get all of its rows. We cannot just print the cursor object, so we need to explicitly get the attributes using cursor.fetchall(), and print them out by iterating through it.
import sqlite3
db_file = 'database.db'
with sqlite3.connect(db_file) as conn:
cursor = conn.cursor()
cursor.execute("""
select * from images
""")
for row in cursor.fetchall():
name, size, date = row
print(f'{name} {size} {date}')
Output
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
Transactions in SQLite
Transaction management is one of the features of SQL databases, and SQLite also handles them. A transaction is basically the sequence of changes where you can modify the database safely, by executing a query, and then placing a commit.
If for some reason, just before the commit, you don’t want to complete the transaction, you can go back to the previous state before the commit, using rollback.
Similarly, we can also view the state of the database through these types of changes.
import sqlite3
db_filename = 'database.db'
def display_table(conn):
cursor = conn.cursor()
cursor.execute('select name, size, date from images;')
for name, size, date in cursor.fetchall():
print(name, size, date)
with sqlite3.connect(db_filename) as conn1:
print('Before changes:')
display_table(conn1)
cursor1 = conn1.cursor()
cursor1.execute("""
insert into images (name, size, date)
values ('JournalDev.png', 2000, '2020-02-20');
""")
print('\nAfter changes in conn1:')
display_table(conn1)
print('\nBefore commit:')
with sqlite3.connect(db_filename) as conn2:
display_table(conn2)
# Commit from the first connection
conn1.commit()
print('\nAfter commit:')
with sqlite3.connect(db_filename) as conn3:
display_table(conn3)
cursor1.execute("""
insert into images (name, size, date)
values ('Hello.png', 200, '2020-01-18');
""")
print('\nBefore commit:')
with sqlite3.connect(db_filename) as conn2:
display_table(conn2)
# Revert to changes before conn1's commit
conn1.rollback()
print('\nAfter connection 1 rollback:')
with sqlite3.connect(db_filename) as conn4:
display_table(conn4)
Output
Before changes:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
After changes in conn1:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20
Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
After commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20
Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20
After connection 1 rollback:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20
Here, as you can see, the table gets modified only after we explicitly complete a transaction. Any changes before that do not actually modify the table.
Finally, we rollback the Hello.png record, so that is not inserted into the table.
Conclusion
Hopefully, this gave you a good idea of how you can exploit the SQL type database system with Python’s ease in sqlite3.
References
- Python SQLite Official Documentation
- JournalDev article on SQLite module