SQLAlchemy tutorial: how to start

3 min read · python, programming, sql, db

SQLAlchemy is a best Python ORM by far, and it has really thorough and detailed documentation. Being like that has a flip side though: it explains a lot of basic concepts right through beginning. I personally find it much easier to start with some recipes and simple examples. Which is why I’ve decided to write small introductory tutorial: a shortest introduction to SQLAlchemy.

Level 1: hand-written SQL

First step is obtaining a connection to a database:

>>> from sqlalchemy import create_engine
>>> e = create_engine('mysql://user:pass@host/db')
>>> for row in e.execute('select * from table where id < %s', 2):
...     print dict(r)
{u'id': 1, u'info': u'first row'}

Of course, there are many more supported dialects, for example postgresql or sqlite.

If you want to use named parameters, there is text():

>>> from sqlalchemy import text
>>> result = e.execute(text('select * from table where id < :id'), {'id': 2})

From objects that you receive by iterating over result - RowProxy - data can be obtained by index, by key or by attribute:

>>> row[0] == row['id'] ==

Would you like to execute everything inside of transaction?

>>> conn = e.connect()
>>> conn.begin()
>>> # work work work
>>> conn.commit() # try/except: conn.rollback() optionally :)

That’s already better than using raw DB API, especially given that parameters will be escaped.

Level 2: SQL-expressions in Python

You can autoguess tables from database and then work with them, if that’s more suitable for you:

>>> from sqlalchemy import Table, MetaData
>>> meta = MetaData(bind=e, reflect=True)
>>> table = meta.tables['table']
>>> list(e.execute( < 2)))
    [(1, u'first row')]

That was the same query, but using Python DSL, without constructing SQL query from string.

Level 3: ORM

And if you prefer working with mapped objects, where you can add some behavior:

>>> from sqlalchemy import orm
>>> class Table(object):
...     pass
>>> orm.Mapper(Table, meta.tables['table'])
>>> s = orm.Session(bind=e)
>>> s.query(Table).filter( < 2).first().info
    u'first row'

In this case you can use full power of ORM:

>>> class Artist(object):
...     pass
>>> orm.Mapper(Artist, meta.tables['artist'])
>>> class Album(object):
...     pass
>>> orm.Mapper(Album, meta.tables['album'])
>>> class Song(object):
...     pass
>>> orm.Mapper(Song, meta.tables['song'])
>>> s.query(Song).join(Album).filter( == 10).count()
>>> # Song is first in a query, and we have to declare joins against it,
>>> # that's why we're joining here with Album
>>> s.query(, == 2).first()
    (u'Hex', u'Inflikted')
>>> # And this is how you look at query generated by SQLAlchemy
>>> print s.query(, == 2)
SELECT AS song_name, AS album_name
FROM song JOIN album ON = song.album_id JOIN artist ON = album.artist_id
WHERE = %(id_1)s

Also if you’re going to use Session.execute(), it accepts strings with named parameters by default:

>>> list(s.execute('select * from table where id < :id', {'id': 2}))
    [(1, u'first row')]


Should be noted that by default Engine already has a pool of connections, which is useful.

MetaData with reflection and early binding is not a preferred way to work with SQLA, it’s there mostly for ad-hoc scripts and working in REPL. Usually you want to construct your tables/classes at first and then bind Engine to MetaData somewhere later - when you are configuring your application, for example (by doing meta.bind = e).

Session often is not used directly, especially in multi-threaded application - there is orm.scoped_session, which creates thread-local session class.

That is basically all I wanted to tell you, for further information there is documentation. :)

If you like what you read — subscribe to my Twitter, I always post links to new posts there. Or, in case you're an old school person longing for an ancient technology, put a link to my RSS feed in your feed reader (it's actually Atom feed, but who cares).

Other recent posts

ngrok for the wicked, or expose your ports comfortably
PostgreSQL collation
History snapshotting in TwinSpark.js
Code streaming: hundred ounces of nuances
Useful shell prompt