Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Version 13 (modified by anonymous, 13 years ago) (diff)


Using SQLAlchemy

 SQLAlchemy is a new ORM library developed by Michael Bayer. Michael claims that SQLAlchemy is not a framework and does not need to dictate program architecutre in order to work. The fundamental concept (borrowed from Java's Hibernate) is the concept of a Unit of Work, which batches related operations in order to produce the most efficient SQL queries.

Notable advantages of SQLAlchemy over SQLObject are the ability to map arbitrary selects, the ability to map muliple tables/selects onto a single object, and support for composite primary keys.

The disadvantage is that SQLAlchemy is more complex than SQLObject, requiring separate table description, object declaration, and object mapping statements. SQLAlchemy object properties retain their database name (for better or worse) while SQLObject provides the styles package to allow name reformatting to suit your taste. You can override the naming on a per-attribute basis but not algorithmically.

If you use SQLAlchemy, you will lose access to fastdata. This could be fixed rather simply by adding sqlalchemy-knowledgable conditions to the RuleDispatch? checks in You will also lose the use of CatWalk/ModelDesigner? and will have to explicitly call objectstore.commit() in your controller (i.e. transactions are not implicit).

This document covers the 0.1.0 release and was written on February 13, 2006.

Getting SQLAlchemy

SQLAlchemy made its first public release (0.1.0) on February 13, 2006. You can get it from the cheeseshop::

easy_install SQLAlchemy

Or grab it some other way via the  SQLAlchemy Download Page. Michael provides thorough  documentation and the pages on python  sql construction,  basic data mapping, and  advanced data mapping are well worth the read.

The documentation is slightly confused in some places, so be sure you notice the 'monkeypatch' version of mapper, assign_mapper. Also don't miss the arguments to relation, which are hidden towards the top of the advanced mapping page.

For the basics of how SQLAlchemy works and how to perform mapping, refer to the main SQLAlchemy docs. This document only covers points relevant to TG.


This section describes how to set up SQLAlchemy so that it will use a seperate config in development and production mode.

The first thing you need to be aware of when coming from SQLObject is that dburis in SQLAlchemy have schemas that vary based on the backend. Yes, this entirely defeats the purpose of having them in uri format. Pick your format from one of the uris shown below::

  • sqlite://filename=querytest.db
  • sqlite://filename=:memory:
  • postgres://database=test&host=localhost&user=scott&password=tiger
  • mysql://db=test&host=localhost&user=scott&passwd=tiger
  • mysql://db=test&unix_socket=/tmp/foo.socket&user=scott&passwd=tiger
  • oracle://dsn=mydsn&user=scott&passwd=tiger

And insert the following into your and files::

  sqlalchemy.dburi = *<your connection uri>*
  sqlalchemy.echo = 0                        #Set to 1 for echo

Now flip over to your <project>/ file and replace the contents with:

  from sqlalchemy import *
  import cherrypy

  engine = create_engine(cherrypy.config.get('sqlalchemy.dburi'),

With those lines, you're ready to go. Keep everything SQLAlchemy related below the engine declaration.

Tips and Gotchas

You may want to keep echo off by default and turn it on when you're debugging in tg-admin shell by simply engine.echo = 1 in the interpreter.

Be aware that SQLAlchemy has transactions on all the time via the Unit of Work abstraction and they are NOT implicit. This means that you need to call objectstore.commit() to push your changes to the db, even in the controller methods. If you're running an assign_mapper object, you can call the object's commit() directly to commit the object. A UoW rollback is objectstore.clear().

You can change a column's name using the alias parameter of the Column constructor.

Custom Column Types

SQLObject uses formencode to perform python to database conversions. This shows 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.

  import time
  from datetime import datetime

  class TIMESTAMP(Numeric):
      def convert_bind_param(self,value,engine):
          return super(TIMESTAMP,self).convert_bind_param(time.mktime(value.timetuple()),engine)
      def convert_result_value(self,value,engine):
          return datetime.fromtimestamp(super(TIMESTAMP,self).convert_result_value(value,engine))
  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):
      def convert_bind_param(self,value,engine):
              return super(IPV4,self).convert_bind_param(struct.unpack('!L',inet_aton(value))[0],engine)
          except socket_error:
              raise IPv4AddrTypeError(value)
      def convert_result_value(self,value,engine):
          return inet_ntoa(struct.pack('!L',super(IPV4,self).convert_result_value(value,engine))

Future Developments

Jonathan LaCour? is working on a declarative way of describing forms through his  ActiveMapper project.