How can I handle 100 tables with same table structure in SQLAlchemy?

[DoDo]

Hello all,

I encounter a situation that I have 100 tables in the same database but with same table structure. With SQLAlchemy, I need to copy 100 different class in order to adopt these tables. Is there any better way to do that? For example, I can use a general class to describe a table structure but use the index to generate this 100 tables class definition?

[Michael Bayer]

sure, you'd write a loop.  I'm not sure if you're looking for core Table objects or declarative classes, I'm guessing declarative classes:

from sqlalchemy.engine.reflection import Inspector

insp = Inspector.from_engine(engine)

class MyClass(Base):

    __abstract__ = True

    id = Column(Integer, primary_key=True)

    foobar = Column(String)

cls_by_table = {}

for table_name in insp.get_table_names():

    cls_by_table[table_name] = type(table_name, (MyClass, ), {'__tablename__':table_name})

or if you want them in the module space (like from mymodule import tablename):

from mypackage import mymod as mod

for table_name in insp.get_table_names():

    cls = type(table_name, (MyClass, ), {'__tablename__':table_name})

    setattr(mod, table_name, cls)

another option if you're just looking for quick and dirty mappings is to use SQLSoup:

https://sqlsoup.readthedocs.org/en/latest/

with this one, you just pull out an attribute name, and you have a mapped class, but it will reflect the whole thing:

for table_name in insp.get_table_names():

    setattr(mod, sqlsoup.entity(table_name))

On Oct 21, 2012, at 8:50 AM, DoDo wrote:

Hello all,

I encounter a situation that I have 100 tables in the same database but with same table structure. With SQLAlchemy, I need to copy 100 different class in order to adopt these tables. Is there any better way to do that? For example, I can use a general class to describe a table structure but use the index to generate this 100 tables class definition?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Z7QL25pEzwEJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[DoDo]

Thanks. Michael! This works for me.

DoDo

Michael Bayer於 2012年10月21日星期日UTC+8下午11時41分06秒寫道：