SQLAlchemy PostgreSQL Tutorial

by

The source code for this tutorial can be found on my GitHub: https://github.com/jmgraff/sqlalchemy-postgres-crud-example

This article will go over a simple example of how to use SQLAlchemy with PostgreSQL. You’re encouraged to clone the repository and run the example yourself. Once you’re familiar with how everything works, try modifying the main.py file by making your own tables and queries.

The Code

Below is the full code for the tutorial to follow:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base

Base = declarative_base()

class Thing(Base):
    __tablename__ = "thing"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

engine = create_engine("postgresql://postgres:postgres@postgresql/postgres")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

if __name__ == "__main__":
    with Session(engine) as session:
        # Add some things
        for ii in range(5):
            thing = Thing(name=f"Thing {ii}")
            session.add(thing)
        session.commit()

        # Get everything
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)

        # Change something
        print("Updating Thing 2...")
        thing = session.query(Thing).where(Thing.name == "Thing 2").first()
        thing.name = "Thing 2 (updated)"
        session.commit()

        # Get everything again
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)

        # Delete something
        print("Deleting Thing 3...")
        thing = session.query(Thing).where(Thing.name == "Thing 3").first()
        session.delete(thing)

        # Get everything again
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)
main.py

We’ll be going through this a section at a time to fully explain what’s going on here.

Expected Output

If you clone the repo and follow the directions to run it, you’ll see the following output:

Thing 0
Thing 1
Thing 2
Thing 3
Thing 4
Updating Thing 2...
Thing 0
Thing 1
Thing 2 (updated)
Thing 3
Thing 4
Deleting Thing 3...
Thing 0
Thing 1
Thing 2 (updated)
Thing 4

The Requirements

In order to use SQLAlchemy with PostgreSQL, you’ll need the following:

  • A running PostgreSQL server (the GitHub repo comes with a Docker container for this)
  • Python 3+
  • The psycopg2 (PostgreSQL) Python package: pip install psycopg2[binary]
  • The sqlalchemy Python package: pip install sqlalchemy
  • A working knowledge of relational database concepts
  • Intermediate Python skills

Importing the packages

Before we can use SQLAlchemy, we have to import a few things first:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base

Base = declarative_base()
main.py

In the above code, we’re importing:

  • sqlalchemy.Column: this class is used to make an attribute of our ORM class (next section) a column in the table
  • sqlalchemy.Integer: this is a data type used for columns that need to store integers
  • sqlalchemy.String: this is a data type used for columns that need to store strings
  • sqlalchemy.create_engine: this function is used to create the engine, which is used to connect to the database
  • sqlalchemy.orm.Session: this is used with the engine we create to interact with our database
  • sqlalchemy.orm.declarative_base: this function will create a class we can use as the base class for our ORM class (next section)

Creating a Schema

Here is where we actually define our ORM class, as well as the Base class that it will inherit from:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base

Base = declarative_base()

class Thing(Base):
    __tablename__ = "thing"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

engine = create_engine("postgresql://postgres:postgres@postgresql/postgres")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
main.py

In this snippet, we’re creating our base class for our ORM class and calling it Base. Then, we define our actual ORM class Thing which inherits from that class with the following attributes:

  • __tablename__: this is the actual name of the table in the PostgreSQL database
  • id: this will serve as the unique ID we will use to reference each row in thing. It’s an Integer type, since it’s a number, and it’s also the primary_key for this table. You don’t have to manually specify this when creating a new Thing, PostgreSQL will generate one for us
  • name: this is a simple String column. We set nullable to False because we don’t want to be able to create a new Thing without specifying the name

Creating the Engine

Next, we’ll create the engine that will connect to our PostgreSQL database:

class Thing(Base):
    __tablename__ = "thing"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

engine = create_engine("postgresql://postgres:postgres@postgresql/postgres")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
main.py

The create_engine method is pretty simple in this example, we just give it a specially formatted URI with all the information it needs to connect to our database.

Here’s what each part of the URI means:

  • postgresql://postgres:postgres@postgresql/postgres: this is the database plugin we want to use. Note that this has to be postgresql and not postgres – SQLAlchemy no longer supports that one.
  • postgresql://postgres:postgres@postgresql/postgres: this is our username for the database
  • postgresql://postgres:postgres@postgresql/postgres: this is our password for the database
  • postgresql://postgres:postgres@postgresql/postgres: this is the domain name or IP address of the server – here, we’re using the domain name postgresql since that’s how we get to it on our Docker network.
  • postgresql://postgres:postgres@postgresql/postgres: this is the name of the database we want to use. Since postgres is created automatically in the postgres container image we’re using, we’ll use that one

Creating the Table

Next we actually create the table(s) based on our schema:

engine = create_engine("postgresql://postgres:postgres@postgresql/postgres")
Base.metadata.drop_all(engine) # testing only - don't do this in production!
Base.metadata.create_all(engine)

if __name__ == "__main__":
    with Session(engine) as session:
main.py

We drop all tables first in the 3rd line of this snippet so that the program executes the same way every time, i.e. so we don’t keep adding rows to the thing table. You probably don’t want to drop all tables as soon as your app starts up!

The Base base class that our Thing ORM class inherits from functions as a sort of registry. It automatically tracks classes’ metadata that inherit from it. By calling Base.metadata.create_all and passing it the engine object we created, it’ll create the schema we have defined by inheriting from it with our ORM classes if it doesn’t already exist.

Creating a Session

In order to interact with our newly created SQLAlchemy schema in the PostgreSQL database, we need to create a session object. We can use the with block syntax to make sure it gets closed when we’re done with it:

if __name__ == "__main__":
    with Session(engine) as session:
        # Add some things
        for ii in range(5):
            thing = Thing(name=f"Thing {ii}")
            session.add(thing)
        session.commit()

        # Get something
main.py

Note that we have to pass in the engine object we created, so it’ll have to be imported wherever you need to use a Session if you move your database logic to another file.

Adding Rows

Within our with block, we can now interact with the database:

if __name__ == "__main__":
    with Session(engine) as session:
        # Add some things
        for ii in range(5):
            thing = Thing(name=f"Thing {ii}")
            session.add(thing)
        session.commit()

        # Get everything
main.py

What we’re doing here is creating 5 Things and adding them to the database via the session.add method. This isn’t enough to create them, though. For that, we have to call session.commit. This will also be necessary when updating as you’ll see in a minute.

Fetching Rows

Now that we have some things added to the PostgreSQL database, we can query them:

        session.commit()

        # Get something
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)

        # Change something
        print("Updating Thing 2...")
main.py

We create a SELECT query by using the session.query method, and passing it the class of the ORM object we want returned to us. Notice that we’re chaining several methods here.

The second method in the chain is .order_by, which takes the property of the class of ORM object we want to order the results by. This will default to alphabetical order since Thing.name is a String type column.

Finally, we call the .all() method to return all rows of the table that match our query parameters. We can then loop through all of them with a for loop and print them out.

Updating Rows

Updating rows is even easier than selecting them:

            print(thing.name)

        # Change something
        print("Updating Thing 2...")
        thing = session.query(Thing).where(Thing.name == "Thing 2").first()
        thing.name = "Thing 2 (updated)"
        session.commit()

        # Get everything again
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)

        # Delete something
main.py

Here, we’re doing another SELECT query with the .query method, but we define a WHERE clause. The syntax here is a bit tricky, since SQLAlchemy has done a great job at becoming a domain-specific language (DSL) of sorts. Putting Thing.name == "Thing 2" doesn’t look like it should work, but it does. We actually get the row with the .first method. Note that if there’s more than one row returned by our query, this will only get the first one. We only have one that will match, so we don’t have to worry about that.

Next, we simply update that ORM object’s name property to whatever we want, then call session.commit to save the changes in the database.

Deleting Rows

Deleting rows is also very simple:

            print(thing.name)

        # Delete something
        print("Deleting Thing 3...")
        thing = session.query(Thing).where(Thing.name == "Thing 3").first()
        session.delete(thing)

        # Get everything again
        things = session.query(Thing).order_by(Thing.name).all()
        for thing in things:
            print(thing.name)
main.py

Here we want to delete the Thing whose name is “Thing 3”. So, we select it out of the database, and then just call session.delete on it. Very simple.

Conclusion

In this tutorial, we covered a simple Create, Read, Update, Delete (CRUD) example for using SQLAlchemy with PostgreSQL. While we did use Postgres with this example, you can swap it out for any supported database you’d like, such as MySQL or even SQLite.