SQLAlchemy tutorial: how to start#

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'] == row.id
    True

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(table.select(table.c.id < 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(Table.id < 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(Album.id == 10).count()
    12L
>>> # 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(Song.name, Album.name).join(Album).join(Artist).filter(Artist.id == 2).first()
    (u'Hex', u'Inflikted')
>>> # And this is how you look at query generated by SQLAlchemy
>>> print s.query(Song.name, Album.name).join(Album).join(Artist).filter(Artist.id == 2)
SELECT song.name AS song_name, album.name AS album_name
FROM song JOIN album ON album.id = song.album_id JOIN artist ON artist.id = album.artist_id
WHERE artist.id = %(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')]

Miscellaneous

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. :)