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 Version 15 and Version 16 of UsingSqlAlchemy


Ignore:
Timestamp:
06/29/07 11:24:11 (12 years ago)
Author:
Chris Arndt
Comment:

migration notice

Legend:

Unmodified
Added
Removed
Modified
  • UsingSqlAlchemy

    v15 v16  
    1 = Using SQLAlchemy = 
    2  
    3  
    4 [http://www.sqlalchemy.org/ SQLAlchemy] is a new ORM library developed by Michael Bayer. Michael claims 
    5 that SQLAlchemy is not a framework and does not need to dictate program 
    6 architecutre in order to work. The fundamental concept (borrowed from Java's 
    7 Hibernate) is the concept of a Unit of Work, which batches related operations 
    8 in order to produce the most efficient SQL queries. 
    9  
    10  
    11  
    12 Notable advantages of SQLAlchemy over SQLObject are the ability to map 
    13 arbitrary selects, the ability to map muliple tables/selects onto a single 
    14 object, and support for composite primary keys. 
    15  
    16 The disadvantage is that SQLAlchemy is more complex than SQLObject, requiring 
    17 separate table description, object declaration, and object mapping statements. 
    18 SQLAlchemy object properties retain their database name (for better or worse) 
    19 while SQLObject provides the styles package to allow name reformatting to suit 
    20 your taste. You can override the naming on a per-attribute basis but not 
    21 algorithmically. 
    22  
    23 If you use SQLAlchemy, you will lose access to fastdata. This could be fixed 
    24 rather simply by adding sqlalchemy-knowledgable conditions to the RuleDispatch 
    25 checks in ``formmaker.py``. You will also lose the use of CatWalk/ModelDesigner 
    26 ~~and will have to explicitly call ``objectstore.commit()`` in your controller 
    27 (i.e. transactions are not implicit).~~ (I believe this isn't true since [1600], transactions are automatically committed by run_with_transaction)  
    28  
    29 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. 
    30  
    31 == Getting SQLAlchemy == 
    32  
    33 You can SQLAlchemy get it from the cheeseshop: 
    34  
    351{{{ 
    36   easy_install SQLAlchemy 
     2#!rst 
     3.. note:: This page has been migrated to http://docs.turbogears.org/1.0/SQLALchemy. 
    374}}} 
    38  
    39 Or grab it some other way via the [http://www.sqlalchemy.org/download.myt SQLAlchemy Download Page]. Michael provides thorough [http://www.sqlalchemy.org/docs/ documentation] and the pages on python [http://www.sqlalchemy.org/docs/sqlconstruction.myt sql construction], 
    40 [http://www.sqlalchemy.org/docs/datamapping.myt basic data mapping], and [http://www.sqlalchemy.org/docs/adv_datamapping.myt advanced data mapping] are well worth the read. 
    41  
    42 The documentation is slightly confused in some places, so be sure you notice 
    43 the 'monkeypatch' version of ``mapper``, ``assign_mapper``. Also don't miss 
    44 the arguments to ``relation``, which are hidden towards the top of the 
    45 advanced mapping page. 
    46  
    47 For the basics of how SQLAlchemy works and how to perform mapping, refer to 
    48 the main SQLAlchemy docs. This document only covers points relevant to TG. 
    49  
    50 == Getting started with SQLAlchemy == 
    51  
    52 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. 
    53  
    54 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). 
    55  
    56 == SQLAlchemy in TG == 
    57  
    58 After reading SQLAlchemy docs you need to know how it maps to TurboGears. 
    59  
    60 Here it goes, taken from [http://groups.google.com/group/turbogears/msg/1c818ff704463f84 this thread]:  
    61  
    62  * use the predefined "metadata" and "session" (from turbogears.database import metadata, session) 
    63  * The context is "session.context" 
    64  * Use the activemapper layer if requirements are simple 
    65  * 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  
    66  
    67 == Tips and Gotchas == 
    68  
    69 You may want to keep echo off by default and turn it on when you're debugging 
    70 in ``tg-admin shell`` by simply ``engine.echo = 1`` in the interpreter. 
    71  
    72 ~~Be aware that SQLAlchemy has transactions on all the time via the 
    73 Unit of Work abstraction and they are **NOT** implicit. This means that you 
    74 need to call ``objectstore.commit()`` to push your changes to the db, even in 
    75 the controller methods. If you're running an ``assign_mapper`` object, you can 
    76 call the object's ``commit()`` directly to commit the object. A UoW rollback is 
    77 ``objectstore.clear()``.~~ (I believe this isn't true since [1600], transactions are automatically committed by run_with_transaction) 
    78  
    79 You can change a column's name using the ``alias`` parameter of the ``Column`` 
    80 constructor. 
    81  
    82  
    83 == Custom Column Types == 
    84  
    85 SQLObject uses formencode to perform python to database conversions. This 
    86 shows how to use the SQLAlchemy column datatypes. Below are two examples, one 
    87 which converts a number representing the system timestamp to a python datetime 
    88 while the other converts IPv4 addresses between integer and octet notation. Keep 
    89 in mind that `convert_bind_param` is  ''to'' the database while  
    90 `convert_result_value` is ''from'' the database. 
    91 {{{ 
    92 #!python 
    93  
    94   import time 
    95   from datetime import datetime 
    96  
    97   class TIMESTAMP(Numeric): 
    98       def convert_bind_param(self,value,engine): 
    99           return super(TIMESTAMP,self).convert_bind_param(time.mktime(value.timetuple()),engine) 
    100       def convert_result_value(self,value,engine): 
    101           return datetime.fromtimestamp(super(TIMESTAMP,self).convert_result_value(value,engine)) 
    102 }}}         
    103 {{{ 
    104 #!python 
    105  
    106   import struct 
    107   from socket import inet_aton, inet_ntoa, error as socket_error 
    108  
    109   class IPv4AddrTypeError(TypeError): 
    110       def __init__(self, addr): 
    111           self.addr = addr 
    112       def __str__(self): 
    113           return "Illegal IPv4 address '%s'" % self.addr 
    114  
    115   class IPV4(Numeric): 
    116       def convert_bind_param(self,value,engine): 
    117           try: 
    118               return super(IPV4,self).convert_bind_param(struct.unpack('!L',inet_aton(value))[0],engine) 
    119           except socket_error: 
    120               raise IPv4AddrTypeError(value) 
    121       def convert_result_value(self,value,engine): 
    122           return inet_ntoa(struct.pack('!L',super(IPV4,self).convert_result_value(value,engine)) 
    123 }}} 
    124  
    125 == Future Developments == 
    126  
    127  
    128 Jonathan LaCour is working on a declarative way of describing forms through 
    129 his [http://groups.google.com/group/turbogears/browse_frm/thread/cd5e46d9359029b8 ActiveMapper] project. 
    130  
    131