Example of using SQLAlchemy.

sqlalchemy_example.py

from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData
from sqlalchemy import sql


def connectComm():
    shopping_engine = create_engine('mysql://dbuser:******@comm/shopping', echo=False, strategy="threadlocal")

    def _makeTable(name):
        meta = MetaData()
        meta.bind = shopping_engine
        return Table(name, meta, autoload=True, autoload_with=shopping_engine)
    
    # Objects
    carts = _makeTable('carts')
    cart_lines = _makeTable('cart_lines')
    customers = _makeTable('customers')
    addresses = _makeTable('addresses')
    customer_addresses = _makeTable('customer_addresses')
    products = _makeTable('products')
    
    # Attributes
    obj_boolean_attributes = _makeTable('obj_boolean_attributes')
    obj_date_attributes = _makeTable('obj_date_attributes')
    obj_float_attributes = _makeTable('obj_float_attributes')
    obj_int_attributes = _makeTable('obj_int_attributes')
    obj_string_attributes = _makeTable('obj_string_attributes')
    obj_text_attributes = _makeTable('obj_text_attributes')
    line_boolean_attributes = _makeTable('line_boolean_attributes')
    line_date_attributes = _makeTable('line_date_attributes')
    line_float_attributes = _makeTable('line_float_attributes')
    line_int_attributes = _makeTable('line_int_attributes')
    line_string_attributes = _makeTable('line_string_attributes')
    line_text_attributes = _makeTable('line_text_attributes')
    pc_boolean_attributes = _makeTable('pc_boolean_attributes')
    pc_date_attributes = _makeTable('pc_date_attributes')
    pc_float_attributes = _makeTable('pc_float_attributes')
    pc_int_attributes = _makeTable('pc_int_attributes')
    pc_string_attributes = _makeTable('pc_string_attributes')
    pc_text_attributes = _makeTable('pc_text_attributes')

    def queryAttr(table, attr, on_id, for_type):
        columns = getattr(table, 'c')
        return sql.select([table.c.value]).where(
            (columns.type == for_type) \
            & (columns.attribute == attr) \
            & (columns.id == on_id)
            ).execute().fetchone()['value']

    def queryAttrs(tables, in_ids, for_type):
        d = defaultdict(dict)
        for table in tables:
            columns = getattr(table, 'c')
            if in_ids:
                q = sql.select([table]).where(
                    (columns.type == for_type) \
                    & (table.c.id.in_(in_ids))
                    )
            else:
                q = sql.select([table]).where(
                    (columns.type == for_type)
                    )
            attrs = q.execute().fetchall()
            for attr in attrs:
                d[attr['id']][attr['attribute']] = attr['value']
        return d

    class Namespace(object):
        def __init__(self, **kwargs):
            for key, value in kwargs.iteritems():
                setattr(self, key, value)
    
    return Namespace(**locals())