wiki:UsingSqlAlchemy
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 15 (modified by alberto, 13 years ago) (diff)

Mentioning that SA transactions are automatically comitted like with SO since [1600].

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 formmaker.py. 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). (I believe this isn't true since [1600], transactions are automatically committed by run_with_transaction)

This document initially covered 0.1.0 release and was written on February 13, 2006. It was updated on August 21, 2006 for SQLAlchemy 0.2 and improved TurboGears' support.

Getting SQLAlchemy

You can SQLAlchemy 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.

Getting started with SQLAlchemy

The simplest route is to quickstart new project with --sqlalchemy switch. This will put appropriate for SQLAlchemy code for your model.py and sample SQLAlchemy uris in configuration files. If you have a project at hand which you want to migrate to SQLAlchemy I still suggest quickstart new project and then incorporate relevant pieces into your code.

After you edited sqlalchemy.dburi to point to valid database connection you need to run 'tg-admin sql create' to init database schema. Unlike SQLObject, identity tables are not created automatically (at least, not yet).

SQLAlchemy in TG

After reading SQLAlchemy docs you need to know how it maps to TurboGears.

Here it goes, taken from  this thread:

  • use the predefined "metadata" and "session" (from turbogears.database import metadata, session)
  • The context is "session.context"
  • Use the activemapper layer if requirements are simple
  • Use assign_mapper if activemapper is not sufficient to handle the requirements, or there is a liking to separate table definitions etc. from business logic

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(). (I believe this isn't true since [1600], transactions are automatically committed by run_with_transaction)

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):
          try:
              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.