SQLAlchemy, Generating python declarative_base classes using reflection AND/OR Persisting reflected metadata and automap_base

Question

I am using SQlAlchemy automap extension for my preference to avoid manually writing up the declarative base classes. Mostly because I am not very familiar with the db schema and need to interact only with a small subset of tables and columns

I am using the following code to set up a SQlAlchemy session and then interact with my database. Things seem to work well using this.

        engine = create_engine('mssql+pymssql://xx:xxx@xxx/xxx', implicit_returning=False)
        metadata = MetaData()
        tables = ['xxx', 'xxx', 'xxx']
        metadata.reflect(engine, only=tables)
        base = automap_base(metadata=metadata)
        base.prepare()
        session_maker = sessionmaker(bind=engine)
        session = session_maker()

Since the database has a large number of tables and columns, it takes a fair amount of time for the session to load, since each time reflection has to be done.

Also, the IDE I use (PyCharm) gives me very useful auto completion when it can infer the object type correctly. It does not work when I use automap, for obvious reasons. It is often a challenge to get the column name strings right, and requires me to repeatedly verify them from db management studio.

Is there a way to generate declarative base classes using reflection? Can I at least persist the reflected metadata in some other way? It is safe to assume that db schema won't change.

Simeon Visser · Accepted Answer · 2015-05-12 22:33:01Z

1

You can define models that represent the tables in the database. Given that the database is not expected to change much this only needs to be done once, possibly with some column changes later on.

Example:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

Documentation

edited May 12, 2015 at 22:33

answered May 12, 2015 at 22:30

Simeon Visser

123k19 gold badges192 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jinal Kothari Over a year ago

Yes, of course. I should have mentioned I was hopping to avoid doing that and therefore went the reflection route.

Simeon Visser Over a year ago

I'm not aware of code that will generate the above Python code from reflected metadata within SQLAlchemy. Also, even when it's possible to persist the metadata in some text/binary file format, it's probably more maintainable to write it in Python code.

jkmacc Over a year ago

I've used SQLACodeGen to get models initially, then clean them up.

Collectives™ on Stack Overflow

SQLAlchemy, Generating python declarative_base classes using reflection AND/OR Persisting reflected metadata and automap_base

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related