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