# How to connect to SQLite with Python

Now that we have learned a bit about SQLite, let's connect to our database in our programming journal app.

To interact with SQLite from within Python, we'll use the built-in sqlite3 module. This comes with Python so there's nothing for us to install!

Inside database.py, I'll import it:

import sqlite3

Then we'll write a new function that will be in charge of creating the table we'll need for this application. I'm putting this into a function as opposed to creating the table manually, so that we can call it easily later on. This will come in handy, as when working with SQLite you'll often be deleting the database and re-creating it.

def create_table():
    connection = sqlite3.connect("data.db")
    connection.execute("CREATE TABLE entries (content TEXT, date TEXT);")
    connection.close()

In this function we're doing a few things:

First, we're connecting to the database. If the database doesn't exist, sqlite3.connect will create it.
Then we execute a query that would create a table.
Finally, we close the connection. In SQLite we should only ever have one connection open, as only one open connection can modify the database at a time. If we forget to close connections we won't be able to open other ones and insert data, for example.

# Creating one connection once

Since we should only ever have one connection open (for writing to the database, at least), it can be simpler to create the connection object at the top of the file instead of inside the function.

Particularly in this application that is single threaded (if you don't know what that is, don't worry), creating the connection inside the function has no benefits. It does have drawbacks though!

If we create the connection inside the function, that means that every time we call the function we're creating a new connection. This is slow.

We'll do this instead:

connection = sqlite3.connect("data.db")

def create_table():
    connection.execute("CREATE TABLE entries (content TEXT, date TEXT);")


def close_connection():
    connection.close()

Now, in app.py we must call close_connection() when the user exits the application.

...

while (user_input := input(menu)) != "3":
    if user_input == "1":
        prompt_new_entry()
    elif user_input == "2":
        view_entries(get_entries())

# This is only called when the while loop ends
close_connection()

...

# Using context managers for automatic commit and rollback

When we execute a SQL query, we might cause an error on the database. For example, if we insert some invalid data. Using sqlite3, all interactions with the database occur in transactions.

What is a transaction?

A transaction^[1] is a sequence of operations performed (using one or more SQL statements) on a database as a single logical unit of work. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).^[2]

So, are we using transactions?

Yes, whenever we call connection.execute() a transaction is created, and our SQL statement executed within that transaction.

Is our transaction being committed or rolled back?

No.

Try to run the application with the code above. Then, terminate the application by selecting the "Exit" option, and open the database file with DB Browser. You'll see that the table we created is not there!

The query we executed was not actually applied to the database because we did not commit it.

To commit we can call connection.commit() after we call connection.execute(), like so:

def create_table():
    connection.execute("CREATE TABLE entries (content TEXT, date TEXT);")
    connection.commit()

However if we produce a SQL error within our .execute() statement (e.g. by inserting invalid data or having a SQL syntax error), this code does not handle rolling back the transaction (undoing any changes).

The easiest way to handle both committing and rolling back in one go is by using the context manager:

def create_table():
    with connection:
        connection.execute("CREATE TABLE entries (content TEXT, date TEXT);")

When you use the context manager, with connection, it'll automatically commit for you at the end of the context manager, or roll back if an error was encountered.

# Wrapping up

This chapter has been quite theoretical, and I apologize for that! Now though, writing the rest of the database functions will be super simple.

All we'll have to do is use the context manager, and inside it execute our query.

Queries that retrieve data from the database will return Cursor objects. We'll be learning about those in the next chapter as they are key to getting data out of a database using sqlite3.

← CREATE TABLE: new tables with SQL What is a cursor? →