Using transactions with SQLAlchemy

Now that we got the terminology out of the way, let’s show how to use this package in a Python application. One of the most popular ways of using the transaction package is to combine transactions from the ZODB with a relational database backend. Likewise, one of the most popular ways of communicating with a relational database in Python is to use the SQLAlchemy Object-Relational Mapper. Let’s forget about the ZODB for the moment and show how one could use the transaction module in a Python application that needs to talk to a relational database.

Installing SQLAlchemy

Installing SQLAlchemy is as easy as installing any Python package available on PyPi:

$ pip install sqlalchemy

This will install the package in your Python environment. You’ll need to set up a relational database that you can use to work out the examples in the following sections. SQLAlchemy supports most relational backends that you may have heard of, but the simplest thing to do is to use SQLite, since it doesn’t require a separate Python driver. You’ll have to make sure that the operating system packages required for using SQLite are present, though.

If you want to use another database, make sure you install the required system packages and drivers in addition to the database. For information about which databases are supported and where you can find the drivers, consult http://www.sqlalchemy.org/docs/core/engines.html#supported-dbapis.

Choosing a data manager

Hopefully, at this point SQLAlchemy and SQLite (or other database if you are feeling adventurous) are installed. To use this combination with the transaction package, we need a data manager that knows how to talk to SQLAlchemy so that the appropriate SQL commands are sent to SQLite whenever an event in the transaction life-cycle occurs.

Fortunately for us, there is already a package that does this on PyPI, so it’s just a matter of installing it on our system. The package is called zope.sqlalchemy, but despite its name it doesn’t depend on any zope packages other than zope.interface. By now you already know how to install it:

$ pip install zope.sqlalchemy

You can now create Python applications that use the transaction module to control any SQLAlchemy-supported relational backend.

A simple demonstration

It’s time to show how to use SQLAlchemy together with the transaction package. To avoid lengthy digressions, knowledge of how SQLAlchemy works is assumed. If you are not familiar with that, reading the tutorial at http://www.sqlalchemy.org/docs/orm/tutorial.html will give you a good enough background to understand what follows.

After installing the required packages, you may wish to follow along the examples using the Python interpreter where you installed them. The first step is to create an engine:

1>>> from sqlalchemy import create_engine
2>>> engine = create_engine('sqlite:///:memory:')

This will connect us to the database. The connection string shown here is for SQLite, if you set up a different database you will need to look up the correct connection string syntax for it.

The next step is to define a class that will be mapped to a table in the relational database. SQLAlchemy’s declarative syntax allows us to do that easily:

 1>>> from sqlalchemy import Column, Integer, String
 2>>> from sqlalchemy.ext.declarative import declarative_base
 3>>> Base = declarative_base()
 4>>> class User(Base):
 5>>>     __tablename__ = 'users'
 6...
 7...    id = Column(Integer, primary_key=True)
 8...    name = Column(String)
 9...    fullname = Column(String)
10...    password = Column(String)
11...
12>>> Base.metadata.create_all(engine)

The User class is now mapped to the table named ‘users’. The create_all method in line 12 creates the table in case it doesn’t exist already.

We can now create a session and integrate the zope.sqlalchemy data manager with it so that we can use the transaction machinery. This is done by passing a Session Extension when creating the SQLAlchemy session:

1>>> from sqlalchemy.orm import sessionmaker
2>>> from zope.sqlalchemy import ZopeTransactionExtension
3>>> Session = sessionmaker(bind=engine, extension=ZopeTransactionExtension())
4>>> session = Session()

In line 3, we create a session class that is bound to the engine that we set up earlier. Notice how we pass the ZopeTransactionExtension using the extension parameter. This extension connects the SQLAlchemy session with the data manager provided by zope.sqlalchemy.

In line 4 we create a session. Under the hood, the ZopeTransactionExtension makes sure that the current transaction is joined by the zope.sqlalchemy data manager, so it’s not necessary to explicitly join the transaction in our code.

Finally, we are able to put some data inside our new table and commit the transaction:

1>>> import transaction
2>>> session.add(User(id=1, name='John', fullname='John Smith', password='123'))
3>>> transaction.commit()

Since the transaction was already joined by the zope.sqlalchemy data manager, we can just call commit and the transaction is correctly committed. As you can see, the integration between SQLAlchemy and the transaction machinery is pretty transparent.

Aborting transactions

Of course, when using the transaction machinery you can also abort or rollback a transaction. An example follows:

1>>> session = Session()
2>>> john = session.query(User).all()[0]
3>>> john.fullname
4u'John Smith'
5>>> john.fullname = 'John Q. Public'
6>>> john.fullname
7u'John Q. Public'
8>>> transaction.abort()

We need a new transaction for this example, so a new session is created. Since the old transaction had ended with the commit, creating a new session joins it to the current transaction, which will be a new one as well.

We make a query just to show that our user’s fullname is ‘John Smith’, then we change that to ‘John Q. Public’. When the transaction is aborted in line 8, the name is reverted to the old value.

If we create a new session and query the table for our old friend John, we’ll see that the old value was indeed preserved because of the abort:

1>>> session = Session()
2>>> john = session.query(User).all()[0]
3>>> john.fullname
4u'John Smith'

Savepoints

A nice feature offered by many transactional backends is the existence of savepoints. These allow in effect to save the changes that we have made at the current point in a transaction, but without committing the transaction. If eventually we need to rollback a future operation, we can use the savepoint to return to the “safe” state that we had saved.

Unfortunately not every database supports savepoints and SQLite is precisely one of those that doesn’t, which means that in order to be able to test this functionality you will have to install another database, like PostgreSQL. Of course, you can also just take our word that it really works, so suit yourself.

Let’s see how a savepoint would work using PostgreSQL. First we’ll import everything and setup the same table we used in our SQLite examples:

 1>>> from sqlalchemy import create_engine
 2>>> engine = create_engine('postgresql://postgres@127.0.0.1:5432')
 3>>> from sqlalchemy import Column, Integer, String
 4>>> from sqlalchemy.ext.declarative import declarative_base
 5>>> Base = declarative_base()
 6>>> Base.metadata.create_all(engine)
 7>>> class User(Base):
 8...     __tablename__ = 'users'
 9...     id = Column(Integer, primary_key=True)
10...     name = Column(String)
11...     fullname = Column(String)
12...     password = Column(String)
13...
14>>> Base.metadata.create_all(engine)
15>>> from sqlalchemy.orm import sessionmaker
16>>> from zope.sqlalchemy import ZopeTransactionExtension
17>>> Session = sessionmaker(bind=engine, extension=ZopeTransactionExtension())

We are now ready to create and use a savepoint:

1>>> import transaction
2>>> session = Session()
3>>> session.add(User(id=1, name='John', fullname='John Smith', password='123'))
4>>> sp = transaction.savepoint()

Everything should look familiar until line 4, where we create a savepoint and assign it to the sp variable. If we never need to rollback, this will not be used, but if course we have to hold on to it in case we do.

Now, we’ll add a second user:

1>>> session.add(User(id=2, name='John', fullname='John Watson', password='123'))
2>>> [o.fullname for o in session.query(User).all()]
3[u'John Smith', u'John Watson']

The new user has been added. We have not committed or aborted yet, but suppose we encounter an error condition that requires us to get rid of the new user, but not the one we added first. This is where the savepoint comes handy:

1>>> sp.rollback()
2>>> [o.fullname for o in session.query(User).all()]
3[u'John Smith']
4>>> transaction.commit()

As you can see, we just call the rollback method and we are back to where we wanted. The transaction can then be committed and the data that we decided to keep will be saved.

Managing more than one backend

Going through the previous section’s examples, experienced users of any powerful enough relational backend might have been thinking, “wait, my database already can do that by itself. I can always commit or rollback when I want to, so what’s the advantage of using this machinery?”

The answer is that if you are using a single backend and it already supports savepoints, you really don’t need a transaction manager. The transaction machinery can still be useful with a single backend if it doesn’t support transactions. A data manager can be written to add this support. There are existent packages that do this for files stored in a file system or for email sending, just to name a few examples.

However, the real power of the transaction manager is the ability to combine two or more of these data managers in a single transaction. Say you need to capture data from a form into a relational database and send email only on transaction commit, that’s a good use case for the transaction package.

We will illustrate this by showing an example of coordinating transactions to a relational database and a ZODB client.

The first thing to do is set up the relational database, using the code that we’ve seen before:

 1>>> from sqlalchemy import create_engine
 2>>> engine = create_engine('postgresql://postgres@127.0.0.1:5432')
 3>>> from sqlalchemy import Column, Integer, String
 4>>> from sqlalchemy.ext.declarative import declarative_base
 5>>> Base = declarative_base()
 6>>> Base.metadata.create_all(engine)
 7>>> class User(Base):
 8...     __tablename__ = 'users'
 9...     id = Column(Integer, primary_key=True)
10...     name = Column(String)
11...     fullname = Column(String)
12...     password = Column(String)
13...
14>>> Base.metadata.create_all(engine)
15>>> from sqlalchemy.orm import sessionmaker
16>>> from zope.sqlalchemy import ZopeTransactionExtension
17>>> Session = sessionmaker(bind=engine, extension=ZopeTransactionExtension())

Now, let’s set up a ZODB connection (you might need to install the ZODB first):

1>>> from ZODB import DB, FileStorage
2
3>>> storage = FileStorage.FileStorage('test.fs')
4>>> db = DB(storage)
5>>> connection = db.open()
6>>> root = connection.root()

We’re ready for adding a user to the relational database table. Right after that, we add some data to the ZODB using the user name as key:

1>>> import transaction
2>>> session.add(User(id=1, name='John', fullname='John Smith', password='123'))
3>>> root['John'] = 'some data that goes into the object database'

Since both the ZopeTransactionExtension and the ZODB connection join the transaction automatically, we can just make the changes we want and be ready to commit the transaction immediately.

>>> transaction.commit()

Again, both the SQLAlchemy and the ZODB data managers joined the transaction, so that we can commit the transaction and both backends save the data. If there’s a problem with one of the backends, the transaction is aborted in both regardless of the state of the other. It’s also possible to abort the transaction manually, of course, causing a rollback on both backends as well.