An Introduction to SQLite with Python
In this article, we’ll kick the tires of SQLite. We’ll learn how to use SQLite through a Python library called sqlite3. At the very end, we’ll explore some more advanced features provided by
sqlite3 to make our job easier.
Note: before getting started, it’s good to be familiar with SQL. If you aren’t, you might want to check out Simply SQL.
What is SQLite?
The motto of SQLite is: “Small. Fast. Reliable. Choose any three.”
SQLite is an embedded database library written in C. You may be familiar with other database technologies like MySQL or PostgreSQL. These use a client-server approach: the database is installed as a server, and then a client is used to connect to it. SQLite is different: it’s known as an embedded database, because it’s included in a program as a library. All the data is stored in a file — usually with a
.db extension — and you have functions that allow you to run SQL statements or do any other operation on the database.
The file-based storage solution also provides concurrent access, meaning that multiple processes or threads can access the same database. Okay, so what are the usages of SQLite? Is it suitable for any kind of application?
Well, there are a few cases where SQLite excels:
Being included on most mobile operating systems, like Android and iOS, SQLite could be a perfect choice if you want a self-contained and serverless data storage solution.
Instead of using huge CSV files, you can exploit the power of SQL and put all your data into a single SQLite database.
SQLite can be used to store configuration data for your applications. In fact, SQLite is 35% faster than a file-based system like a configuration file.
On the other hand, what are some reasons for not choosing SQLite?
Unlike MySQL or PostgreSQL, SQLite lacks multi-user functionalities.
SQLite still a file-based data storage solution, not a service. You can’t manage it as a process, you can’t start or stop it, or manage the resource usage.
The Python interface to SQLite
As I said in the introduction, SQLite is a C library. There are interfaces written in a lot of languages though, including Python. The
sqlite3 module provides an SQL interface and requires at least SQLite 3.7.15.
The awesome thing is that
sqlite3 comes with Python, so you don’t need to install anything.
Getting Started with sqlite3
It’s time to code! In this first part, we’ll create a basic database. The first thing to do is create a database and connect to it:
import sqlite3 dbName = 'database.db' try: conn = sqlite3.connect(dbName) cursor = conn.cursor() print("Database created!") except Exception as e: print("Something bad happened: ", e) if conn: conn.close()
On line 1, we import the
sqlite3 library. Then, inside a
try/except code block, we call
sqlite3.connect() to initialize a connection to the database. If everything goes right,
conn will be an instance of the
Connection object. If the
try fails, we print the exception received and the connection to the database is closed. As stated in the official documentation, each open SQLite database is represented by a
Connection object. Each time we have to execute an SQL command, the
Connection object has a method called
cursor(). In database technologies, a cursor is a control structure that enables traversal over the records in a database.
Now, if we execute this code we should get the following output:
> Database created!
If we look at the folder where our Python script is, we should see a new file called
database.db. This file has been created automatically by
Create, read and modify records
At this point, we’re ready to create a new table, add the first entries and execute SQL commands like
To create a table, we just need to execute a simple SQL statement. In this example, we’ll create a students table that will contain the following data:
print("Database created!") line, add this:
# Create operation create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") # Insert and Read operation cursor.execute("INSERT INTO student VALUES (1, 'John', 'Smith')") print("Insert #1 done!") cursor.execute("INSERT INTO student VALUES (2, 'Lucy', 'Jacobs')") print("Insert #2 done!") cursor.execute("INSERT INTO student VALUES (3, 'Stephan', 'Taylor')") print("Insert #3 done!") conn.commit() conn.close()
We create a table and call the
cursor.execute() method, which is used when we want to execute a single SQL statement.
Then, we do an
INSERT for each row we want to add. After all of our changes have been done, we call
conn.commit() to commit the pending transaction to the database. Without calling the
commit() method, any pending change to the database will be lost. Lastly, we close the connection to the database by calling the
Okay, now let’s query our database! We’ll need a variable to save the results of our query, so let’s save the result of
cursor.execute() to a variable called
records = cursor.execute("SELECT * FROM student") for row in findrecords: print(row)
After executing this, we’ll see all of the records to
(1, 'John', 'Smith') (2, 'Lucy', 'Jacobs') (3, 'Stephan', 'Taylor')
At this point, you might have noticed that, inside the
cursor.execute() method, we put the SQL command that must be executed. Nothing changes in the Python syntax if we want to execute another SQL command like
cursor.execute() method needs a string as an argument. In the previous section, we saw how to insert data into our database, but everything was hard-coded. What if we need to store in the database something that’s in a variable? For this reason,
sqlite3 has some fancy things called placeholders. Placeholders allow us to use parameter substitution, which will make inserting a variable into a query much easier.
Let’s see this example:
def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute(command, (student_id, name, surname, )) conn.commit()
We create a method called
insert_command(). This method takes four arguments: the first one is a
Connection instance, and the other three will be used in our SQL command.
? inside the
command variable represents a placeholder. This means that, if you call the
insert_command function with
INSERT statement will become
INSERT INTO student VALUES(1, 'Jason', 'Green').
When we call the
execute() function, we pass our command and all of the variables that will be substituted to the placeholders. From now on, every time we need to insert a row in the student table, we call the
insert_command() method with the parameters required.
Even if you aren’t new to the definition of a transaction, let me give a quick recap of its importance. A transaction is a sequence of operations performed on a database that’s logically treated as a single unit.
The most important benefit of a transaction is ensuring data integrity. It might be useless in the example we introduced above, but when we deal with more data stored in multiple tables, transactions do make the difference.
sqlite3 module starts a transaction before execute() and executemany() executes
REPLACE statements. This implies two things:
- We must take care of calling the
commit()method. If we call
Connection.close()without doing a
commit(), all of the changes we made during the transaction will be lost.
- We can’t open a transaction in the same process using
The solution? Handle transactions explicitly.
How? By using the function call
sqlite3.connect(dbName, isolation_level=None) instead of
sqlite3.connect(dbName). By setting
None, we force
sqlite3 to never open transactions implicitly.
The following code is a rewriting of the previous code, but with the explicit usage of transactions:
import sqlite3 dbName = 'database.db' def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute("BEGIN") try: cur.execute(command, (student_id, name, surname, )) cur.execute("COMMIT") except conn.Error as e: print("Got an error: ", e) print("Aborting...") cur.execute("ROLLBACK") conn = sqlite3.connect(dbName, isolation_level=None) cursor = conn.cursor() print("Database created!") # Create operation create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") # Insert and Read operation insert_command(conn , 1, 'John', 'Smith') insert_command(conn , 2, 'Lucy', 'Jacobs') insert_command(conn , 3, 'Stephan', 'Taylor') insert_command(conn , 4, 'Joseph', 'Random') findRecords = cursor.execute("SELECT * FROM student") for row in findRecords: print(row) conn.close()
I hope you now have a good understanding of what SQLite is, how you can use it for your Python projects, and how some of its advanced features work. The explicit management of transactions might be a bit tricky at first, but it can certainly help you make the most of