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 9 (modified by Karl Guertin, 9 years ago) (diff)

minor

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.

SQLAlchemy: http://www.sqlalchemy.org/

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

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.

Configuration

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 devcfg.py and prodcfg.py files:

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

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

from sqlalchemy import *
import cherrypy

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

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

ActiveMapper: http://groups.google.com/group/turbogears/browse_frm/thread/cd5e46d9359029b8