SQLAlchemy

Plain SQLAlchemy with contextual sessions

Use turbogears.database.mapper as the mapper:

from turbogears.database import metadata, mapper
from sqlalchemy import Table, Column, Integer

mytable = Table('mytable', metadata,
    Column('id', Integer, primary_key=True))
class MyTable(object):
    pass
mapper(MyTable, mytable)

The mapper function from turbogears.database is identical with SQLAlchemy's contextual mapper() if you use SQLAlchemy >= 0.4, and something similar if you use SQLAlchemy < 0.4

For more information, see http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual

In TurboGears <= 1.0.4b2, turbogears.database.mapper is not available; you must define it explicitly in one of the following ways:

# standard SQLAlchemy mapper
from sqlalchemy.orm import mapper

# contextual mapper (only for SQLAlchemy >= 0.4)
mapper = session.mapper

# assignmapper extension (deprecated)
from sqlalchemy.ext.assignmapper import assign_mapper
mapper = lambda *args, **kw: assign_mapper(session.context, *args, **kw)

The Elixir layer on top of SQLAlchemy

Elixir is a declarative layer on top of the SQLAlchemy library.

Using Elixir, you would define your model like that:

from elixir import Entity, Field, Unicode

class MyTable(Entity):
    name = Field(Unicode(30))

In Elixir < 0.4, this new syntax is not available, you have to use the old syntax:

from elixir import Entity, has_field, Unicode

class MyTable(Entity):
    has_field('name', Unicode(30))

The differences between Elixir 0.3 and 0.4 are explained here: http://elixir.ematia.de/trac/wiki/Migrate03to04

In future Elixir versions, you may need to create your mappings explicitly using:

setup_all()

For more information on Elixir, see the Elixir documentation.

Logging

The default dev.cfg files contains the following to configure SA logging. By default it will not log every SQL statement issued. This can be changed for debugging.:

[[[database]]]
# Set to INFO to make SQLAlchemy display SQL commands
level='ERROR'
qualname='sqlalchemy.engine'
handlers=['debug_out']
propagate=0

Custom Column Types

While SQLObject uses formencode to perform Python to database conversions, SQLAlchemy performs conversions through defining its column datatypes. This section demonstrates how to use the SQLAlchemy column datatypes.

Below are two examples, one which converts a number representing the system timestamp to a Python datetime while the other converts IPv4 addresses between integer and octet notation. Keep in mind that convert_bind_param is to the database while convert_result_value is from the database.

Creating a TIMESTAMP column type:

import time
from datetime import datetime

class TIMESTAMP(Numeric):
    # to the database
    def convert_bind_param(self,value,engine):
        return super(TIMESTAMP,self).convert_bind_param(time.mktime(value.timetuple()),engine)
    # from the database
    def convert_result_value(self,value,engine):
        return datetime.fromtimestamp(super(TIMESTAMP,self).convert_result_value(value,engine))

Creating an IPV4 column type:

import struct
from socket import inet_aton, inet_ntoa, error as socket_error

class IPv4AddrTypeError(TypeError):
    def __init__(self, addr):
        self.addr = addr
    def __str__(self):
        return "Illegal IPv4 address '%s'" % self.addr

class IPV4(Numeric):
    # to the database
    def convert_bind_param(self,value,engine):
        try:
            return super(IPV4,self).convert_bind_param(struct.unpack('!L',inet_aton(value))[0],engine)
        except socket_error:
            raise IPv4AddrTypeError(value)
    # from the database
    def convert_result_value(self,value,engine):
        return inet_ntoa(struct.pack('!L',super(IPV4,self).convert_result_value(value,engine))

Using Predefined Databases

There are times when you want to load your table definitions from a preexisting database. SQLAlchemy makes it easy to do this via an argument to the Table creation function and the addition of one function call:

from turbogears.database import metadata, bind_meta_data
# This function makes the metadata variable ready for us to use
bind_meta_data()

# Notice the autoload=True parameter.  This tells SQLAlchemy
# to load the table definition by introspecting the database
MyTable = Table('mytable', metadata, autoload=True)

Passing Parameters to create_engine

It is possible to specify parameters for create_engine in the configuration file (dev.cfg during development). The syntax looks like:

sqlalchemy.dburi = 'sqlite:///mydb'
sqlalchemy.pool_recycle = 30

In this example, pool_recycle=30 will be passed to create_engine. If multiple databases are used all create_engine calls get the same arguments.

'module' object has no attribute 'bind_meta_data'

If you are using a version of SQLAlchemy that is too old (or you did not install SQLAlchemy at all), you will receive the following error message:

Unhandled exception in thread started by <bound method Server._start of <cherrypy._cpserver.Server object at 0x832ce0c>>
Traceback (most recent call last):
  File ".../site-packages/CherryPy....egg/cherrypy/_cpserver.py", line 78, in _start
    Engine._start(self)
  File ".../site-packages/CherryPy....egg/cherrypy/_cpengine.py", line 108, in _start
    func()
  File ".../site-packages/TurboGears....egg/turbogears/startup.py", line 227, in startTurboGears
    database.bind_meta_data()
AttributeError: 'module' object has no attribute 'bind_meta_data'

If this is the case, you should update to a newer version of SQLAlchemy.

From SQLObject

To update the model, start by using AutoCode to generate SQLAlchemy code that matches your existing database. Then, by hand, move all the logic code from your old model into the new one.

You'll also need to update any other code that calls the model. The main change is that MyTable.get becomes MyTable.query.get, and .select becomes .filter.

From SQLAlchemy 0.3

See the SQLAlchemy docs: http://www.sqlalchemy.org/docs/04/intro.html#overview_migration

The most significant change is a change to query syntax that affects migrating from assign_mapper (or ActiveMapper) to the SQLAlchemy contextual mapper. The previous syntax of Object.get(id) is now Object.query.get(id), get_by() is gone, select() and select_by() are replaced by query.filter() and query.filter_by(). Elixir is affected in the same way, since it inherited its methods from assign_mapper; however the Object.get(id) and Object.get_by(id) shortcuts still remain if you are using Elixir.

There were several approaches for defining the model in SQLAlchemy 0.3, with different suggestions for migration

• Plain SA (manual sessions) - this will work in SQLAlchemy 0.4 without changes. Consider changing to contextual session, as that is the convenient new way.
• assign_mapper - will not work with TurboGears and SQLAlchemy 0.4. Change to contextual session.
• ActiveMapper - will work in SQLAlchemy 0.4. It is now deprecated and should not be used for new projects; use Elixir instead. Changes will be needed for TG 1.1, see below.
• Elixir - will work with SQLAlchemy 0.4

TurboGears projects that use req.sa_transaction will see no visible change. However, they should now use transaction controls on the turbogears.database.session object. req.sa_transaction will be removed at some point.

Also, in the TG config file, sqlalchemy.echo no longer works. There is an alternative log configuration syntax, see above.

In TG 1.0, ActiveMapper is bound to the turbogears database and session automatically. To use ActiveMapper with TG 1.1, you must add the following boiler plate to the top of your model:

from sqlalchemy.ext import activemapper
from turbogears.database import metadata, session
activemapper.objectstore = session
__metadata__ = metadata