SQLAlchemy Cheat Sheet

Here is a quick reference guide to SQLAlchemy. It is based on the SQLAlchemy documentation.

Although this is a wiki, there is too much spam attacking Trac sites, so I don't allow anonymous edits. Email gerald@… for corrections or suggested additions.

Sessions

(full doc)

session.query(Object) - returns a Query object used for queries

session.get(object, pk[, entity_name]) - looks up the primary key of object.

session.load(object, pk[, entity_name]) - same as session.get() except an exception will be raised if the row doesn't exist

session.save(object) - marked this object to pending (for creates and updates)

session.flush() - execute all marked objects

session.close() - removes objects from the session and closes transactional resources

session.clear() - removes objects from the session and starts a new unit of work

session.delete(object) - mark this object to be deleted

Queries

select() - Select all rows

selectfirst(), selectfirst_by() - select with LIMIT 1 and return a single object instance

selectone(), selectone_by() - select with LIMIT 2, assert that only one row is present, and return a single object instance

filter(), filter_by() - apply the criterion to the Query object generatively, and return a new Query object with the criterion built in.

count(), count_by() - return the total number of object instances that would be returned.

Describing Databases with Meta Data

(full doc)

Quick Example

user_prefs = Table('user_prefs', metadata, 
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("users.user_id"), nullable=False, index=True),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100), default='moo'),
    Column('created', Integer, default=time.time()),
)

Common Column Types

(full doc)

Integer([size])

String([length])

Unicode()

Float()

Decimal()

Column options

ForeignKey("table.field")

ForeignKeyConstraint([,], [,], onupdate="", ondelete=""))

key=string

primary_key=bool

nullable=bool

default=... (see doc)

onupdate=...

index=bool

unique=bool

quote=bool

Data Mapping

Quick Example

mapper(Address, addresses_table)
mapper(User, users_table, properties = {
        'addresses' : relation(
            Address,
            cascade="all, delete-orphan",
            backref='user'
        )
    }
)

Database Engine URLs

(full doc)

postgres://username:password@localhost:5432/mydatabase

sqlite:////absolute/path/to/database.sqlite

sqlite:///relative/path/to/database.sqlite

sqlite:// (in-memory database)

mysql://username:password@localhost/foo

oracle://username:password@dsn

oracle://username:password@127.0.0.1:1521/sidname