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

Changes between Initial Version and Version 1 of UsingSqlAlchemy

02/14/06 00:36:52 (13 years ago)
Karl Guertin



  • UsingSqlAlchemy

    v1 v1  
     5Using SQLAlchemy 
     8`SQLAlchemy`_ is a new ORM library developed by Michael Bayer. Michael claims 
     9that SQLAlchemy is not a framework and does not need to dictate program 
     10architecutre in order to work. The fundamental concept (borrowed from Java's 
     11Hibernate) is the concept of a Unit of Work, which batches related operations 
     12in order to produce the most efficient SQL queries. 
     14_`SQLAlchemy`: http://www.sqlalchemy.org/ 
     16Notable advantages of SQLAlchemy over SQLObject are the ability to map 
     17arbitrary selects, the ability to map muliple tables/selects onto a single 
     18object, and support for composite primary keys. 
     20The disadvantage is that SQLAlchemy is more complex than SQLObject, requiring 
     21separate table description, object declaration, and object mapping statements. 
     22SQLAlchemy objects by default retain their database_names rather than 
     23SQLObject's pythonicNames. 
     25If you use SQLAlchemy, you will lose access to fastdata. This could be fixed 
     26rather simply by adding sqlalchemy-knowledgable conditions to the RuleDispatch 
     27checks in ``formmaker.py``. There are no other disadvantages to using 
     28SQLAlchemy with TurboGears. 
     30This document covers the 0.1.0 release and was written on February 13, 2006. 
     32Getting SQLAlchemy 
     34SQLAlchemy made its first public release (0.1.0) on February 13, 2006. You can 
     35get it from the cheeseshop:: 
     37  easy_install SQLAlchemy 
     39Or grab it some other way via the `SQLAlchemy Download Page`_. Michael 
     40provides thorough documentation_ and the pages on python `sql construction`_, 
     41`basic data mapping`_, and `advanced data mapping`_ are well worth the read. 
     43.. _`SQLAlchemy Download Page`: http://www.sqlalchemy.org/download.myt 
     44.. _documentation: http://www.sqlalchemy.org/docs/ 
     45.. _`sql construction`: http://www.sqlalchemy.org/docs/sqlconstruction.myt 
     46.. _`basic data mapping`: http://www.sqlalchemy.org/docs/datamapping.myt 
     47.. _`advanced data mapping`: http://www.sqlalchemy.org/docs/adv_datamapping.myt 
     49The documentation is slightly confused in some places, so be sure you notice 
     50the 'monkeypatch' version of ``mapper``, ``assign_mapper``. Also don't miss 
     51the arguments to ``relation``, which are hidden towards the top of the 
     52advanced mapping page. 
     54For the basics of how SQLAlchemy works and how to perform mapping, refer to 
     55the main SQLAlchemy docs. This document only covers points relevant to TG. 
     59This section describes how to set up SQLAlchemy so that it will use a seperate 
     60config in development and production mode. 
     62The first thing you need to be aware of when coming from SQLObject is that 
     63dburis in SQLAlchemy have schemas that vary based on the backend. Yes, this 
     64entirely defeats the purpose of having them in uri format. Pick your format 
     65from one of the uris shown below:: 
     67  sqlite://filename=querytest.db 
     68  sqlite://filename=:memory: 
     69  postgres://database=test&host=localhost&user=scott&password=tiger 
     70  mysql://db=test&host=localhost&user=scott&passwd=tiger 
     71  mysql://db=test&unix_socket=/tmp/foo.socket&user=scott&passwd=tiger 
     72  oracle://dsn=mydsn&user=scott&passwd=tiger 
     74And insert the following into your ``devcfg.py`` and ``prodcfg.py`` files:: 
     76  sqlalchemy.dburi = *<your connection uri>* 
     77  sqlalchemy.echo = 0                        #Set to 1 for echo 
     79Now flip over to your ``<project>/model.py`` file and replace the contents 
     82  from sqlalchemy import * 
     83  import cherrypy 
     85  engine = create_engine(cherrypy.config.get('sqlalchemy.dburi'), 
     86                         echo=cherrypy.config.get('sqlalchemy.echo',0)) 
     88With those lines, you're ready to go. 
     90Tips and Gotchas 
     92You may want to keep echo off by default and turn it on when you're debugging 
     93in ``tg-admin shell`` by simply ``engine.echo = 1`` in the interpreter. 
     95Be aware that SQLAlchemy has implicit transactions on all the time via the 
     96Unit of Work abstraction. This means that you need to call 
     97``objectstore.commit()`` to push your changes to the db. If you're running an 
     98``assign_mapper`` object, you can call the object's ``commit()`` directly. A 
     99rollback is ``objectstore.clear()``. 
     101You can change a column's name using the ``alias`` parameter of the ``Column`` 
     104Future Developments 
     107Jonathan LaCour is working on a declarative way of describing forms through 
     108his ActiveMapper project. 
     110_`ActiveMapper`: http://groups.google.com/group/turbogears/browse_frm/thread/cd5e46d9359029b8