A step-by-step SQLAlchemy tutorial

SQLAlchemy is a very useful database-access library for Python. It’s got excellent documentation; but what it was missing until recently was a tutorial. I wrote a step-by-step SQLAlchemy tutorial to fill in the gap. Of course, the day after I wrote it, SQLAlchemy’s author posted the tutorial that he’d been working on, so I just duplicated his efforts. :-)

Nevertheless, it might be useful to someone, so I put it up anyway.

15 Comments

  • ALex Greif says:

    Hi,
    thanks for your tutorial!
    I have a question on engine.begin() and engine.rollback(). If I use global_connect, how can I get a reference to the engine to begin a transaction?

    On the other hand objectstore.begin() retuns an instance of SessionTrans that denotes a transactionalized UnitOfWork instance which has commit() and rollback() methods. So I dont understand your sentence in the tutorial:
    “If you want to use a transaction, by the way, you should call the begin() method on your SQLEngine object. In our case, we called that object db, so to start a transaction, we’d call db.begin(). Then db.rollback() and db.commit() would either rollback or commit the transaction, respectively.”

    O is it like this, that SessionTrans.rollback() only rolls back changes on objects, but not the creation or dropping of tables? For the latter one we need engine.rollback?

    Alex.

  • Robin Munn says:

    objectstore.begin() acts like a transaction, but it’s not a real database transaction, despite the name. What it does is similar to, but not the same as, a database transaction: it keeps track of which SQLAlchemy-managed objects were created or modified since the objectstore.begin() was called, and if you call objectstore.rollback(), it will reset those changes (which have not yet been written to the database at that point). objectstore.commit() will write the changes to the database via the usual INSERT or UPDATE SQL statements. Where they will act within the current database transaction, and therefore could be COMMITted or ROLLBACK’ed by a database transaction — e.g., by engine.commit() or engine.rollback().

    Confusing, isn’t it? That’s why the “transaction-like” feature of sessions is going away in SQLAlchemy 0.2. No more objectstore.begin() and objectstore.rollback(); they’ll be replaced by explicit session management if you want it. In other words, if you want “UPDATE TABLE accounts SET balance = balance+100 WHERE account_number = 123″ and “UPDATE TABLE accounts SET balance = balance-100 WHERE account_number = 456″ to be sent to the database together, you’ll create a separate session object and do the manipulations within that object. See http://www.mail-archive.com/sqlalchemy-users@lists.sourceforge.net/msg01434.html for more details.

    If you’re still confused, then just remember this: objectstore.begin() puts the SQL statements “on hold” until you call objectstore.commit(). Only once commit() is called will any SQL statements hit the database. And if you call objectstore.rollback(), then the SQL statements that were “on hold” are simply wiped out and never executed.

    As for your question about glocal_connect: the docs say “There is an instance of ProxyEngine available within the schema package as default_engine.” So you just need to use sqlalchemy.schema.default_engine.begin() (or just “schema.default_engine.begin()” if you did “from sqlalchemy import *”) and you’ll have a database transaction.

  • Fred Wilmes says:

    Hi, I found your tutorial very helpful!

    Just one question. I had to change the function run(stmt) from

    def run(stmt):
    rs = stmt.execute()
    for row in rs:
    print row

    into

    def run(stmt):
    r = stmt.execute()
    rs=r.fetchall()
    for row in rs:
    print row

    to avoid the error “for row in rs:
    TypeError: iteration over non-sequence”

    Is this because I still use 0.1.4 (that’s what easy_install gave me)?

  • Robin Munn says:

    Yes; looking at the CHANGES file, the iterator interface was added to ResultProxy objects (which is what stmt.execute() will return) in 0.1.5.

    0.1.4 is quite old, and I’m surprised that’s the version easy_install gave you. (Unless you ran it quite some time ago). Try “sudo easy_install -U SQLAlchemy” to upgrade to the latest release (0.1.7 right now), and the tutorial should work for you with no further changes.

  • Fred Wilmes says:

    Thanks a lot, it worked!

    Unfortunately(?), with 0.2 it seems some things have changed again…

  • Robin Munn says:

    I’m currently updating my tutorial to fit SQLAlchemy 0.2. Check back in a couple of days and it should be ready.

  • Robin Munn says:

    I got on a roll and finished it earlier than I thought. My SQLAlchemy tutorial is now current and the examples should all work with SQLAlchemy 0.2. Check it out and let me know what you think.

  • Fred Wilmes says:

    Did so, still finding it helpful. I especially like the parts where you explain the “Python way” to do things. You really close a gap between mere SQL users and the original SQLAlchemy tutorial which, IMHO, is often too “implicit”…

    However, I think the “explicit vs. implicit” parts in the “Mapping” section are probably a little bit redundant.

    But thanks again – it was your tutorial which brought me to SQLAlchemy!

  • Robin Munn says:

    Off-topic comment moved to a more appropriate thread. – Admin

  • francois says:

    Can you use real number and date in sqlalchemy?

    Would you happen to have an example?

    Many thanks

    Francois

  • David G. says:

    Great tutorial, but was wondering if you had any plans to update it for SQLAlchemy 0.4? I would be willing to send my changes needed to make things work with 0.4.

  • Malander says:

    Thank very much for that, but was wondering if you had any plans to update it for 0.4 version.

  • shakir says:

    Thanks a lot for the tutorial.
    BoundMetaData however is deprecated and should be replaced with MetaData, and you might want to update your tutorial for that.

  • Dan says:

    Your SQLAlchemy tutorial is written for version 0.2. I have version 0.5.5. Are you going to update yout tutorial for version 0.5.5?

  • Dan says:

    There are many, many changes from ver. 0.2 to ver. 0.5.5 and I’m afraid your tutorial is no longer relevant as many modules have been deleted and I can’t find equivalents within the SQLAlchemy documentation.