Sqlalchemy is great. It can do lots of magical things, like creating classes for table at run time automatically. It's just too deep buried in the documentation. Let's unshovel this mistery.

How to Load Table Definitions Using Sqlalchemy ORM

The problem

Sqlalchemy already describes us how to autoload table information for sql table expression.

However, I never found how to do it for the ORM part of it. Not in the docs, at least. By the time of writing this, they've already included it in the official docs but it's not very clear. Having a deep search using google and duckduckgo, I was finally able to do it.

The solution

I'm not here to tell you how to use the ORM, Go learn yourself a little Sqlalchemy you lazy programmer bastard. I'm here to tell you how to map your tables to classes. I'd say almost like magic, maybe some kind of alchemy.

Instead of defining classes like this:

class State(Base):
    __tablename__ = "State"

    StateId = Column(Integer, primary_key=True)
    Code = Column(Integer, nullable=False)
    Name = Column(String, nullable=False)
    NormalizedName = Column(String, nullable=True)

    Cities = relationship("City", lazy='joined')

You just need to do:

dbEngine = create_engine(...)
metadata = MetaData()
class State(Base):
    __table__ = Table("State", metadata, autoload=True, autoload_with=dbEngine)

    Cities = relationship("City", lazy='joined')

(metadata and dbEngine must be shared across your table definitions).

You're good to go. Don't thank me.

Posted by: fabzter
Last revised: 27 Feb, 2014 01:24 PM History


Your Comments

Used for your gravatar. Not required. Will not be public.
Posting code? Indent it by four spaces to make it look nice. Learn more about Markdown.