Psycopg2 cursors and queries

Now we'll change the rest of the database.py code to use psycopg2.

These are the changes:

• psycopg2 can't do connection.execute(), so we will need to create a cursor each time instead.
• Whereas in SQLite INTEGER PRIMARY KEY gives us an auto-incrementing value, in PostgreSQL we must use the SERIAL data type instead.
• Whereas in SQLite we used ? to include parameters in queries, in with psycopg2 the correct syntax uses %s.

Using psycopg2 cursors throughout

Wherever we're using connection.execute() at the moment, we will need to use a cursor. For example, at the moment we're doing that in the create_tables() function:

def create_tables():
    with connection:
        connection.execute(CREATE_MOVIES_TABLE)
        connection.execute(CREATE_USERS_TABLE)
        connection.execute(CREATE_WATCHED_TABLE)

Now it'll have to be this. Two nested context managers so that the cursor is opened within the connection context manager, and the statements are executed within both the connection and the cursor context managers:

def create_tables():
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_MOVIES_TABLE)
            cursor.execute(CREATE_USERS_TABLE)
            cursor.execute(CREATE_WATCHED_TABLE)

That is because the psycopg2 library does not have an .execute() method in the Connection class, but the sqlite3 library did have it.

The cursor creation happens in a context manager (with connection.cursor() as cursor) instead of as we would traditionally (cursor = connection.cursor()) because that way they are automatically closed at the end of the context manager, releasing any resources associated with them^[1].

This is different from what happened in SQLite, where we did not have to close cursors.

We must do this for all functions in database.py.

create_tables()

Goes from this:

def create_tables():
    with connection:
        connection.execute(CREATE_MOVIES_TABLE)
        connection.execute(CREATE_USERS_TABLE)
        connection.execute(CREATE_WATCHED_TABLE)

To this:

def create_tables():
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_MOVIES_TABLE)
            cursor.execute(CREATE_USERS_TABLE)
            cursor.execute(CREATE_WATCHED_TABLE)

add_movie()

Goes from this:

def add_movie(name, release_timestamp):
    with connection:
        connection.execute(INSERT_MOVIE, (name, release_timestamp))

To this:

def add_movie(name, release_timestamp):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(INSERT_MOVIE, (name, release_timestamp))

get_movies()

Goes from this:

def get_movies(upcoming=False):
    with connection:
        cursor = connection.cursor()
        if upcoming:
            today_timestamp = datetime.datetime.today().timestamp()
            cursor.execute(SELECT_UPCOMING_MOVIES, (today_timestamp,))
        else:
            cursor.execute(SELECT_ALL_MOVIES)
        return cursor.fetchall()

To this:

def get_movies(upcoming=False):
    with connection:
        with connection.cursor() as cursor:
            if upcoming:
                today_timestamp = datetime.datetime.today().timestamp()
                cursor.execute(SELECT_UPCOMING_MOVIES, (today_timestamp,))
            else:
                cursor.execute(SELECT_ALL_MOVIES)
            return cursor.fetchall()

add_user()

Goes from this:

def add_user(username):
    with connection:
        connection.execute(INSERT_USER, (username,))

To this:

def add_user(username):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(INSERT_USER, (username,))

watch_movie()

Goes from this:

def watch_movie(username, movie_id):
    with connection:
        connection.execute(INSERT_WATCHED_MOVIE, (username, movie_id))

To this:

def watch_movie(username, movie_id):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(INSERT_WATCHED_MOVIE, (username, movie_id))

get_watched_movies()

Goes from this:

def get_watched_movies(username):
    with connection:
        cursor = connection.cursor()
        cursor.execute(SELECT_WATCHED_MOVIES, (username,))
        return cursor.fetchall()

To this:

def get_watched_movies(username):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SELECT_WATCHED_MOVIES, (username,))
            return cursor.fetchall()

search_movies()

Goes from this:

def search_movies(search_term):
    with connection:
        cursor = connection.cursor()
        cursor.execute(SEARCH_MOVIE, (f"%{search_term}%",))
        return cursor.fetchall()

To this:

def search_movies(search_term):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SEARCH_MOVIE, (f"%{search_term}%",))
            return cursor.fetchall()

Changes to queries

Using SERIAL

Whenever we were using INTEGER PRIMARY KEY to get an auto-incrementing primary key, we must now use SERIAL PRIMARY KEY instead.

This:

CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    id INTEGER PRIMARY KEY,
    name TEXT,
    release_timestamp REAL
);"""

Changes to this:

CREATE_MOVIES_TABLE = """CREATE TABLE IF NOT EXISTS movies (
    id SERIAL PRIMARY KEY,
    name TEXT,
    release_timestamp REAL
);"""

Using %s instead of ?

All queries that use parameters, such as this one:

INSERT_MOVIE = "INSERT INTO movies (name, release_timestamp) VALUES (?, ?)"

Must now use %s instead. It's a simple search-and-replace.

The queries look like this at the end of the changes:

INSERT_MOVIE = "INSERT INTO movies (name, release_timestamp) VALUES (%s, %s)"
SELECT_ALL_MOVIES = "SELECT * FROM movies;"
SELECT_UPCOMING_MOVIES = "SELECT * FROM movies WHERE release_timestamp > %s;"
INSERT_USER = "INSERT INTO users (username) VALUES (%s)"
INSERT_WATCHED_MOVIE = "INSERT INTO watched (user_username, movie_id) VALUES (%s, %s)"
SELECT_WATCHED_MOVIES = """SELECT movies.*
FROM users
JOIN watched ON users.username = watched.user_username
JOIN movies ON watched.movie_id = movies.id
WHERE users.username = %s;"""
SEARCH_MOVIE = """SELECT * FROM movies WHERE name LIKE %s;"""

That is all the changes we have to make to the code to make it work!

No changes required in app.py because we already made sure to separate the how from the what in database.py.

Since we haven't changed the what (what functions are available, also known as the interface), any user of database.py (in this case, app.py) does not have to change.

---

1. The with statement (psycopg2 Official Documentation) (opens new window) ↩︎