Working with SQLite

SQLite is a C-language library that implements a small, fast, independent, reliable, and serverless SQL database engine. Unlike other database engines like MySQL, Oracle, or PostgreSQL, you don't need to install and run an SQLite server to perform database operations.

By default, Python contains an SQL module called Sqlite3 that allows you to interact with an SQLite database. The sqlite3 module was created by Gerhard Häring. If your application needs internal data storage, SQLite is your main option. It is also used to prototype applications and then transfer the code to a larger database such as PostgreSQL. To define the purpose, we can give some examples:

• Mozilla Firefox browser uses SQLite to store bookmarks, history, cookies, and so on;

• Opera and Chrome use SQLite as well;

• Microsoft uses SQLite as a core component of Windows 10;

• Android and iOS adopt SQLite to enable data storage and retrieval;

• Django framework uses SQLite as the default database.

To see the list of sites and applications that use SQLite, you can take a look at the official website.

In this topic, we'll learn how to create a new database or connect to an existing one, execute several SQL statements such as creating tables, inserting data, committing the changes, selecting data, and finally, filtering data using the Sqlite3 module.

Creating a database

To use SQLite in Python, import the sqlite3 module first and then create a connection object that represents the database. This object can be created using the SQLite connect() function:

import sqlite3

con = sqlite3.connect('database.db')

If the database does not exist, the database.db file will be created automatically in the current directory. If you want to specify a directory, do the following thing:

con = sqlite3.connect(r'path-to-your-directory/name.db')

For education purposes, we are going to work with the following database:

Once you have a connection, you can create a cursor object and call its execute() method, which allows us to execute SQL statements:

cursor = con.cursor()

# use the execute method to create the first table
cursor.execute("""CREATE TABLE IF NOT EXISTS Department(
    id INTEGER PRIMARY KEY,
    name TEXT);""")

# create the second table and add a reference to the first
cursor.execute("""CREATE TABLE IF NOT EXISTS Student(
    id INTEGER PRIMARY KEY,
    full_name TEXT,
    email TEXT,
    gpa REAL,
    department INTEGER,
    FOREIGN KEY (department) REFERENCES Department(id)
    );""")

con.commit()
cursor.close()
con.close()

In the code above, we've done the following:

1. The cursor object is created using the connection object;

2. Using the cursor object, we've called the execute() method with the CREATE TABLE query as a parameter to create the Department and Student tables;

3. The IF NOT EXISTS statement will help us to reconnect to the database to avoid creating the table again;

4. Calling the commit() function to save the changes. In this way, the saved data is persisted and is available in subsequent sessions.

5. Close() the connection and the cursor. This is optional, but it is good programming practice to free the memory of unused resources.

We can also use a try-except block to control possible errors during the creation of our tables, and in general, during the execution of any SQL statement. For example, if we try to create the department table again, we will get an error because the table already exists:

try:
    cursor.execute("""CREATE TABLE Department(id INTEGER PRIMARY KEY, name TEXT);""")
except Exception as e:
    print("Error: ", e)

# Error:  the Department table already exists

After running the scripts, your database will have two tables. Now we are ready to start adding data!

Data types

Before adding data, it is important to note that SQLite supports only some of the types by default. To use the Python data types, you must adapt them to one of the sqlite3 supported types:

Adding data

Let's take a look at how we can add data to the database we've just created. Similar to table creation, we need the query to add data and the cursor object to execute the query:

cursor.execute("""INSERT INTO Department(name) VALUES ('Engineering')""")
cursor.execute("""INSERT INTO Department(name) VALUES ('Mathematics')""")
con.commit()  # saves the changes

When we work with Python, we have variables that contain the values we need. Don't use Python string operations in your query because it is insecure; it makes your program vulnerable to an SQL injection attack:

# Don't do this!
symbol = 'Physics'
cursor.execute("INSERT INTO Department(name) VALUES ('%s')" % symbol)

Instead, use the DB-API parameter substitution. Put a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method:

new_department = (45, 'Physics')
cursor.execute("INSERT INTO Department(id, name) VALUES (?, ?)", new_department)

We replaced all values with question marks and then added a parameter with the values we wanted to insert. It's important to note here that SQLite expects the values to be in tuple format.

On the other hand, if an exception occurs or you've entered a wrong value, the transaction can be rolled back by calling the rollback() method. This method rolls back any changes to the database since the last call to commit():

con.rollback()  # undo the changes

We can also insert multiple records at the same time using the executemany() function:

students_list = [
    (1, 'Alan Turing', '[email protected]', 9.5, 1),
    (2, 'Katherine Johnson', '[email protected]', 10.0, 2),
    (3, 'Helen Quinn', '[email protected]', 8.7, 45),
]

# Use executemany() to insert multiple records at the same time
cursor.executemany('INSERT INTO Student VALUES (?, ?, ?, ?, ?)', students_list)

Finally, after adding data we have the following:

Select and filter data

To obtain data, we can execute a SELECT statement following a similar structure as we've done to execute the queries above. Next, we will use the cursor as an iterator; call the fetchone() function to get a single record:

cursor.execute("SELECT * FROM Student;")
one_row = cursor.fetchone()
print(one_row)


# (1, 'Alan Turing', '[email protected]', 9.5, 1)

Likewise, we can call fetchmany() function to return multiple records:

cursor.execute("SELECT * FROM student;")
two_rows = cursor.fetchmany(2)
print(two_rows)


# [(1, 'Alan Turing', '[email protected]', 9.5, 1),
#  (2, 'Katherine Johnson', '[email protected]', 10.0, 2)]

Or we can use the fetchall() function to get a list of all records:

cursor.execute("SELECT * FROM student;")
all_rows = cursor.fetchall()
print(all_rows)


# [(1, 'Alan Turing', '[email protected]', 9.5, 1),
#  (2, 'Katherine Johnson', '[email protected]', 10.0, 2),
#  (3, 'Helen Quinn', '[email protected]', 8.7, 45)]

We also can add the SQL clause WHERE to filter our data:

average_gpa = (9.5 + 10 + 8.7) / 3
cursor.execute("SELECT * FROM student WHERE gpa >= ?", (average_gpa,))
all_rows = cursor.fetchall()

for student in all_rows:
    print(student[1])


# Alan Turing
# Katherine Johnson

Conclusion

In this topic, we have learned how to use the Python SQLite3 module. We've covered the following basics to get you started:

• How to import the module;

• How to create a new database, read from an existing file, or create a temporary one and connect to it;

• How to use the cursor object to execute several SQL statements;

• How to insert many records at the same time with the function executemany();

• How to use the cursor as an iterator with the functions fetchone(), fetchmany(),fetchall();

• About the supported data types;

• How to commit and rollback changes;

• How to close the connection and the cursor.