On May 5, 2010, at 4:43 PM, Chris Angove wrote:
> I am new to Sqlalchemy and ORM in general. I also have a slightly odd
> use case. My existing database is a set of very simple tables. Each
> table has the exact same layout/columns just different names, for
> different collections of information. So I looked through the
> Documentation and it seems to get this to work I would have to create
> a different class instance for each table, even though they have the
> same layout. Using reflection with autoload means I need to edit the
> code for every new table (could be dozens) with empty class
> definitions. The autoload per table to create objects where the
> columns were members of the class (ie mytable.mycolumn)
Since Python allows dynamic generation of classes, there are easy ways to deal
with this.
>
> This presents a maintenance issue I would like to avoid. So I tried
> using 'meta.reflect(bind=engine)' which does indeed read all my tables
> in without the maintenance issues. However it creates generic Table()
> classes with no interface additions for the columns. This makes my
> queries basic SQL with some added sqlalchemy calls to get the table
> and dereference the columns. Also not desirable.
>
> I believe this is due to the fact that Sqlalchemy chooses to modify
> the class definitions on reflection as opposed to class instances.
I am assuming there is a breakdown of terminology occuring here. The
reflection process produces instances of the Table object with contained
Columns. These are not classes, they are instances. The classes in use are
sqlalchemy.schema.Table and sqlalchemy.schema.Column, and these do not change
nor are they dynamically extended. There is also no "dereferencing" step I'm
aware of unless you are referring to the present of the Column objects on the
.c. collection. The table metadata produced by the MetaData object has no
knowledge of the ORM or the declarative extension, but the ORM builds upon
these constructs.
It sounds like you'd like a reflection process that produces declarative-bound
ORM classes, not Table instances. The recipe below achieves this, by
generating a new declarative class for each Table reflected by the metadata.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
def declared_classes_for_tables(Base, engine):
result = {}
Base.metadata.reflect(engine)
for table in Base.metadata.tables.values():
cls_ = type(str(table.name), (Base,), {'__table__':table})
result[cls_.__name__] = cls_
return result
# demo
if __name__ == '__main__':
engine = create_engine('sqlite://', echo=True)
metadata = MetaData()
for name in ('peas', 'carrots', 'celery', 'brocolli'):
Table(name, metadata,
Column('id', Integer, primary_key=True),
Column('data', String(50))
)
metadata.create_all(engine)
# now reflect the database into classes
Base = declarative_base()
classes = declared_classes_for_tables(Base, engine)
sess = sessionmaker(engine)()
sess.add_all([
classes['carrots'](data='carrot1'),
classes['peas'](data='pea1')
])
print sess.query(classes['carrots']).all()
print sess.query(classes['peas']).all()
--
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.
