Rob <[email protected]> writes:
> However, importing [or trying to instantiate this class] will fail
> until the connection is in place ie the class as it stands cannot be
> called until a valid engine is bound. I'm guessing that something
> will need to be passed to an __init__ in the class above.
>
> Is there a strategy for dealing with this?
Not sure it's the best strategy, but I'm doing something similar with
an application where the SA layer is entirely reflected, and basically
postpone all the database operations until runtime. The schema is
always reflected at startup, and the only definitions in the SA layer
is for ORM items like relations or proxies.
While I suspect I will eventually move to having a bit more of the
schema explicitly declared in the module over time (or at least saving
a cached pickle of the reflected metadata), this mechanism has let me
evolve the schema largely on the database side (using schema design
tools such as Power*Architect) while reducing the need to update the
SA module, other than for relation changes or brand new tables. I had
originally reflected all tables, but found that a little filtering on
that front was a good buffer between the DB changes and the SA code.
I'm not sure if it's useful to try to do this with declarative since
by definition you're reflecting the information so specifying the
SA-specific elements such as relations isn't really any simpler with
declarative than not. To be fair, I do replicate one feature of the
declarative base class in terms of permitting column names to be used
as keyword arguments during mapped object instantiation.
In my case, my db module contains:
* Mapped class definitions. Mostly placeholder objects ("pass")
with an occasional helper property or association_proxy mapping.
* Mapper definitions (class to table, and mapper options). Defined as
a simple dictionary at import time. The list of mappings in this
dictionary also constrains what tables are reflected.
* "setup" module method that is called at runtime to:
- Connect to the database, create engine and metadata
- Reflect tables
- Establish mappings and relations for mapped classes
- Execute mapper configuration requiring runtime information
The last point is a little ugly. It covers a handful of cases where
mapper configuration depends on data that will only be present after
reflection. In my case, this is one table that has two FK fields to
the same foreign table, so I need access to the reflected column
objects to specify the join for the relation (or at least haven't
found a way yet that doesn't require it).
Attached is an excerpt from my SA schema module, with just a few
classes relevant to a subset of family registration (families have
adults and children, one adult may be a primary contact per family,
and any number of adults may be in an emergency contact list).
It also includes some getter/setter utility functions I use with
association_proxy to provide read-only access across some proxies, and
to assure a proxy can be used in all cases (returning None rather than
an error if missing). Also, the code is a bit more generic than
necessary, as it supports multiple engines/schemas which aren't
reflected in the excerpt.
In use, the module is imported (safe for "from db import *" to get
mapped classes in the local namespace if desired) and after other
startup processing, the setup() method is called to make the db
connection and mappings. No database operations occur prior to the
call to setup(). After that, the module's sessionmaker() object is
used for sessions (in my case wrapped within a CherryPy session tool).
This is running with SA 0.5 (0.5.4p2 or 0.5.8 depending on server).
-- David
- - - - - - - - - - - - - - - - - - - - - - - - -
#
# Base class for mapped tables (also helps identify such classes for export)
#
class Mapped(object):
def __init__(self, **kwargs):
# Support arbitrary construction-time field assignment
for key, val in kwargs.items():
if not hasattr(self, key):
raise ValueError('Invalid column name: '+key)
setattr(self, key, val)
#
# Utility factory functions used with some association_proxy instances
#
def _primary_contact_from_adult(adult):
pc = PrimaryContact()
pc.adult = adult
return pc
def _null_getset(collection_class, proxy):
getter, setter = proxy._default_getset(collection_class)
def skipnull(obj, getter=getter):
if obj is None:
return None
else:
return getter(obj)
return skipnull, setter
def _ro_creator(value):
raise Exception("Attempt to set R/O proxy value")
def _ro_getset(collection_class, proxy):
def nowrite(o, v):
raise Exception("Attempt to set R/O proxy value")
getter, setter = proxy._default_getset(collection_class)
return getter, nowrite
#
# Core registration table classes
#
class Address(Mapped):
description = association_proxy('type', 'description',
getset_factory=_ro_getset,
creator=_ro_creator)
class Adult(Mapped):
description = association_proxy('type', 'description',
getset_factory=_ro_getset,
creator=_ro_creator)
@property
def full_name(self):
if self.firstname:
name = self.firstname
else:
name = '(Adult-%s)' % self.adult_id
if self.lastname:
name = name + ' ' + self.lastname
return name
class Child(Mapped):
@property
def lastname(self):
return self.family.lastname
class EmergencyContact(Mapped):
pass
class Family(Mapped):
primary_contact = association_proxy('primary_contact_', 'adult',
creator=_primary_contact_from_adult,
getset_factory=_null_getset)
class PrimaryContact(Mapped):
pass
#
# Definition/lookup registration classes
#
class AddressType(Mapped):
pass
class AdultType(Mapped):
pass
#
# Mapper definitions and foreign key relationships
#
mapper_config = {}
# Per database, list of:
# (Class, Table, Properties)
relation = orm.relation
backref = orm.backref
mapper_config['greatplay'] = [
#
# Core mappings
#
(Address, 'addresses',
{ 'adult': relation(Adult,
backref=backref('addresses',
cascade='all, delete-orphan')),
'type': relation(AddressType, lazy=False),
}),
(Adult, 'adults',
{ 'family': relation(Family,
backref=backref('adults',
cascade='all, delete-orphan')),
'type': relation(AdultType, lazy=False),
}),
(Child, 'children',
{ 'family': relation(Family,
backref=backref('children',
cascade='all, delete-orphan')),
}),
(EmergencyContact, 'emergency_contacts',
{ 'family': relation(Family,
backref=backref('emergency_contacts',
cascade='all, delete-orphan')),
'adult': relation(Adult,
backref=backref('emergency_contact_for',
uselist=False)),
}),
(Family, 'families', None),
(PrimaryContact, 'primary_contacts',
{ 'family': relation(Family, backref=backref('primary_contact_',
uselist=False)),
'adult': relation(Adult, backref=backref('primary_contact_for_',
uselist=False)),
}),
#
# Definition mappings
#
(AddressType, 'address_types', None),
(AdultType, 'adult_types', None),
]
#
# ----------------------------------------------------------------------
#
sessionmaker = orm.scoped_session(orm.sessionmaker(expire_on_commit=False))
engines = {}
meta = {}
def make_engines(host, user, password, port):
engine_url = 'postgres://%s:%...@%s:%s/%%s' % (user, password, host, port)
for database in mapper_config.keys():
logger.log(logging.DEBUG,
'Creating engine: %s', engine_url % database)
engines[database] = sa.create_engine(engine_url % database,
convert_unicode=True)
def setup(host=None, user='XXX', password='YYY', port=5432):
if host is None:
host = 'localhost'
# Only want SQL logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm').setLevel(logging.ERROR)
logging.getLogger('sqlalchemy.pool').setLevel(logging.ERROR)
make_engines(host, user, password, port)
session_binds = {}
# Load in database table definitions
for database, engine in engines.items():
meta[database] = sa.MetaData(bind=engine)
# Reflect tables for mapped classes, separating those that require
# a schema and those that don't.
tables = []
schema_tables = {}
for row in mapper_config[database]:
tblname = row[1]
if '.' in tblname:
schema, tblname = tblname.split('.')
schema_tables.setdefault(schema,[]).append(tblname)
else:
tables.append(tblname)
meta[database].reflect(only=tables)
for schema in schema_tables:
meta[database].reflect(only=schema_tables[schema], schema=schema)
# XXX: This is where I dynamically apply updates to the mapper
# configuration only possible after tables are reflected
# Then actually implement mappings
for cls, table, properties in mapper_config[database]:
table_cls = meta[database].tables[table]
orm.mapper(cls, table_cls, properties=properties)
session_binds[cls] = engine
# Update binding for sessionmaker to map to correct database engine
sessionmaker.configure(binds=session_binds)
def terminate():
for e in engines.values():
e.dispose()
orm.clear_mappers()
#
# ----------------------------------------------------------------------
#
# Export any mapped classes (but not base) and sessionmaker
from inspect import isclass
__all__ = ['sessionmaker'] + [k for k, v in locals().items()
if k != 'Mapped' and
isclass(v) and issubclass(v, Mapped)]
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
