• PostgreSQL 9.5.6 Documentation
• SQLAlchemy 1.1.10 Documentation
• SQLAlchemy with PostgreSQL Blog Post

Context Mostly I’ve worked on scrapers/crawlers and scripts, and those usually don’t require a full blown database. Flat file and/or MongoDB are usually sufficient. However, now that I’m building a database web service for a client, I need to get familiar with a relational database.

Requirements ¶

Using sqlalchemy with a postgres database requires a Database API (DBAPI) and two objects - the database engine and a metadata object. The most well known DBAPI for Postgres is psycopg2, so install it pip install psycopg2. SQLAlchemy will use this by default.

If postgres role you’re using, has a ‘trust’ connection, you will need to set a password for the role. If you’re using the ‘postgres’ user, then you may need to switch users. Fire up the psql shell as a superuser and set a password:

username@local $ sudo su - postgres  # change user to postgres
postgres@local $ psql                # fire up the postgres shell

postgres=# \password newrole         # set password for role 'newrole'
Enter new password:
Enter it again: 
postgres=# 

Using SQLAlchemy ¶

Assuming a postgres database is up and running (til to help with this):

from sqlalchemy import create_engine  
from sqlalchemy import Column, String  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy.orm import sessionmaker

db_string = 'postgresql+psycopg2://{}:{}@{}:{}/{}'      # don't need to specify psycopg2, but keeping it explicit

user = "newrole"
password = "newrolepassword"                            # the password you set above
host = "localhost"                                      # going to connect to a database on the same machine as this code
port = 5432                                             # default port the database runs on
database = "newdatabase"


db_string = db_string.format(user, password, host, port, database)

db = sqlalchemy.create_engine(url, client_encoding='utf-8')
base = declarative_base()


class Film(base):  
    __tablename__ = 'films'

    title = Column(String, primary_key=True)
    director = Column(String)
    year = Column(String)

Session = sessionmaker(db)  
session = Session()
base.metadata.create_all(db)

# Create 
doctor_strange = Film(title="Doctor Strange", director="Scott Derrickson", year="2016")  
session.add(doctor_strange)  
session.commit()

# Read
films = session.query(Film)  
for film in films:  
    print(film.title)

# Update
doctor_strange.title = "Some2016Film"  
session.commit()

# Delete
session.delete(doctor_strange)  
session.commit()