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 using SQLAlchemy. SQLAlchemy is a well-established and mature SQL generation and ORM library that is well worth the time invested in climbing its learning curve.

We will show how to integrate SQLAlchemy and Spyne object definitions, and how to do painless transaction management using Spyne events.

There are two ways of integrating with SQLAlchemy:

1. The first and supported method is to use the output of the spyne.model.complex.TTableModel.

   The TTableModel class is a templated callable that produces a ComplexModel that has enough information except table name to be mapped with a SQL table. It takes an optional metadata argument and creates a new one when one isn’t supplied.

   WARNING: While the machinery around TTableModel is in production use in a few places, it should be considered experimental as it’s a relatively new feature which is not as battle-tested as the rest of the Spyne code.

   Also, this is only tested with PostgreSQL and to some extent, SQLite. We’re looking for volunteers to test and integrate other RDBMSs, please open an issue and chime in.

2. The second method is to use spyne.model.table.TableModel as a second base class together with the declarative base class (output of the sqlalchemy.orm.declarative_base() callable). This is deprecated [1] and won’t be developed any further, yet it also won’t be removed in the foreseeable feature as apparently there are people who are quite fine with its quirks and would prefer to have it shipped within the Spyne package.

This document will cover only the first method. The documentation for the second method can be found in the spyne.model.table documentation or in the Spyne 2.9 documentation.

The semantics of SQLAlchemy’s and Spyne’s object definition are almost the same, except a few small differences:

1. SQLAlchemy’s Integer maps to Spyne’s Integer32 or Integer64, depending on the RDBMS. Spyne’s Integer, as it’s an arbitrary-size number, is converted to sqlalchemy.Decimal type as it’s the only type that can acommodate arbitrary-size numbers. So it’s important to use a bounded integer type like Integer32 or Integer64, especially as primary key.

2. SQLAlchemy’s UnicodeText is Spyne’s Unicode with no max_len restriction. If you need a length-limited UnicodeText, you can use Spyne’s Unicode object as follows:

   class(TableModel):
       __tablename__ = "some_table"
   
       # text
       some_text = Unicode(2048, db_type=sqlalchemy.UnicodeText)
   
       # varchar
       some_varchar = Unicode(2048)
   
       # text
       some_more_text = Unicode

   Default mapping for text types is varchar. Note that the limit is only enforced to incoming data, in this case the database type is bounded only by the limits of the database system.

3. Spyne does not reflect all restrictions to the database – some are only enforced to incoming data when validation is enabled. These include range and value restrictions for numbers, and min_len and pattern restrictions for Spyne types.

Okay, enough with the introductory & disclaimatory stuff, let’s get coding :)

There’s a fully functional example at examples/user_manager/server_sqlalchemy.py. in the source distribution.

First, we need a database handle:

db = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=db)
metadata = MetaData(bind=db)

Now, we must define our own TableModel base class. This must be defined for every MetaData instance.

TableModel = TTableModel(metadata)

Doing this is also possible:

TableModel == TTableModel()
TableModel.Attributes.sqla_metadata.bind = db

... but the first method is arguably cleaner.

We’re finally ready to define Spyne types mapped to SQLAlchemy tables. At this point, we have two options: Do everything with the Spyne markers, or re-use existing SQLAlchemy code we might already have.

class Permission(TableModel):
    __tablename__ = 'permission'

    id = UnsignedInteger32(pk=True)
    application = Unicode(values=('usermgr', 'accountmgr'))
    operation = Unicode(values=('read', 'modify', 'delete'))

class User(TableModel):
    __tablename__ = 'user'

    id = UnsignedInteger32(pk=True)
    user_name = Unicode(32, min_len=4, pattern='[a-z0-9.]+', unique=True)
    full_name = Unicode(64, pattern='\w+( \w+)+')
    email = Unicode(64, pattern=r'[a-z0-9._%+-]+@[a-z0-9.-]+\.[A-Z]{2,4}')
    last_pos = Point(2, index='gist')
    permissions = Array(Permission).store_as('table')

permissions = Array(Permission)

from spyne.model.complex import table

permissions = Array(Permission).store_as(table(
          multi='user_perm_rel',
          left='u_id', right='perm_id',
      ))

class Permission(ComplexModel):
    application = Unicode(values=('usermgr', 'accountmgr'))
    operation = Unicode(values=('read', 'modify', 'delete'))

class User(TableModel):
    __tablename__ = 'user'

    id = UnsignedInteger32(pk=True)
    user_name = Unicode(32, min_len=4, pattern='[a-z0-9.]+')
    full_name = Unicode(64, pattern='\w+( \w+)+')
    email = Unicode(64, pattern=r'[a-z0-9._%+-]+@[a-z0-9.-]+\.[A-Z]{2,4}')
    permissions = Array(Permission).store_as('json')

class User(DeclarativeBase):
    __tablename__ = 'spyne_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))

class User(TableModel):
    __table__ = User.__table__

class User(TableModel):
    __table__ = User.__table__

    id = UnsignedInteger32
    user_name = Unicode(32, min_len=4, pattern='[a-z0-9.]+')
    full_name = Unicode(64, pattern='\w+( \w+)+')
    email = Unicode(64, pattern=r'[a-z0-9._%+-]+@[a-z0-9.-]+\.[A-Z]{2,4}')