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 sqlite3
.
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 SELECT
, UPDATE
or DROP
.
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:
id | name | surname |
---|---|---|
1 | John | Smith |
2 | Lucy | Jacobs |
3 | Stephan | Taylor |
After the 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 conn.close()
method.
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
:
records = cursor.execute("SELECT * FROM student")
for row in findrecords:
print(row)
After executing this, we’ll see all of the records to stdout
:
(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 UPDATE
or DROP
.
The Placeholders
The 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.
Each ?
inside the command
variable represents a placeholder. This means that, if you call the insert_command
function with student_id=1
, name='Jason'
and surname='Green'
, the 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.
Transactions
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.
Python’s sqlite3
module starts a transaction before execute() and executemany() executes INSERT
, UPDATE
, DELETE
, or REPLACE
statements. This implies two things:
- We must take care of calling the
commit()
method. If we callConnection.close()
without doing acommit()
, all of the changes we made during the transaction will be lost. - We can’t open a transaction in the same process using
BEGIN
.
The solution? Handle transactions explicitly.
How? By using the function call sqlite3.connect(dbName, isolation_level=None)
instead of sqlite3.connect(dbName)
. By setting isolation_level
to 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()
Conclusion
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 sqlite3
.
Related reading:
- Getting Started with SQLite3: Basic Commands
- An Introduction to Python Unit Testing with unittest and pytest
- Managing Data in iOS Apps with SQLite
- A Beginner’s Guide to HTTP Python Requests
- SQL vs NoSQL: The Differences
Frequently Asked Questions About Using SQLite With Python
SQLite is a lightweight, file-based relational database management system. It is widely used for embedded database applications due to its simplicity and minimal configuration. Using SQLite with Python provides a convenient way to integrate a database into Python applications without requiring a separate database server.
You can use the sqlite3
module, which comes pre-installed with Python, to connect to an SQLite database. Use the connect()
method to establish a connection and obtain a connection object, and then create a cursor to execute SQL commands.
You can use the execute()
method on the cursor object to run SQL commands. To create a table, use the CREATE TABLE
statement.
Use the INSERT INTO
statement to insert data into a table. The placeholders %s
or ?
can be used for parameterized queries to avoid SQL injection.
Transactions in SQLite are managed using the commit()
and rollback()
methods on the connection object. Wrap multiple SQL commands between a begin
and commit
to ensure they are treated as a single transaction.
I am a computer science student fond of asking questions and learning new things. Traveller, musician and occasional writer. Check me out on my website.