SQLAlchemy Integration¶

This tutorial builds on the User Manager tutorial. If you haven’t done so, we recommend you to read it first.

In this tutorial, we talk about using Spyne tools that make it easy to deal with database-related operations. We will show how to integrate SQLAlchemy and Spyne object definitions, and how to do painless transaction management using Spyne events.

The full example is available here: http://github.com/arskom/spyne/blob/master/examples/user_manager/server_sqlalchemy.py

Again, focusing on what’s different from previous User Manager example:

class User(TableModel, DeclarativeBase):
    __namespace__ = 'spyne.examples.user_manager'
    __tablename__ = 'spyne_user'

    user_id = Column(sqlalchemy.Integer, primary_key=True)
    user_name = Column(sqlalchemy.String(256))
    first_name = Column(sqlalchemy.String(256))
    last_name = Column(sqlalchemy.String(256))

Defined this way, SQLAlchemy objects are regular Spyne objects that can be used anywhere the regular Spyne types go. The definition for the User object is quite similar to vanilla SQLAlchemy declarative syntax, save for two elements:

1. The object also bases on spyne.model.table.TableModel, which bridges SQLAlchemy and Spyne types.
2. It has a namespace declaration, which is just so the service looks good on wsdl.

The SQLAlchemy integration is far from perfect at the moment:

• SQL constraints are not reflected to the interface document.
• It’s not possible to define additional constraints for the Spyne schema.
• Object attributes defined by mechanisms other than Column and limited uses of relationship (no string arguments) are not supported.

If you need any of the above features, you need to separate the Spyne and sqlalchemy object definitions.

Spyne makes it easy (to an extent) with the following syntax:

class AlternativeUser(TableModel, DeclarativeBase):
    __namespace__ = 'spyne.examples.user_manager'
    __table__ = User.__table__

Here, The AlternativeUser object is automatically populated using columns from the table definition.

The context object is also a little bit different – we start a transaction for every call in the constructor of the UserDefinedContext object, and close it in its destructor:

class UserDefinedContext(object):
    def __init__(self):
        self.session = Session()

    def __del__(self):
        self.session.close()

We implement an event handler that instantiates the UserDefinedContext object for every method call:

def _on_method_call(ctx):
    ctx.udc = UserDefinedContext()

We also implement an event handler that commits the transaction once the method call is complete.

def _on_method_return_object(ctx):
    ctx.udc.session.commit()

We register those handlers to the application’s ‘method_call’ handler:

application.event_manager.add_listener('method_call', _on_method_call)
application.event_manager.add_listener('method_return_object', _on_method_return_object)

Note that the method_return_object event is only fired when the method call completes without throwing any exceptions.