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