Can SQLAlchemy automatically create relationships from a database schema?

Question

Starting from an existing (SQLite) database with foreign keys, can SQLAlchemy automatically build relationships?

SQLAlchemy classes are automatically created via __table_args__ = {'autoload': True}.

The goal would be to easily access data from related tables without having to add all the relationships one by one by hand (i.e. without using sqlalchemy.orm.relationship() and sqlalchemy.orm.backref).

Ricotta

Answer 1

[Update] As of SQLAlchemy 0.9.1 there is Automap extension for doing that.

For SQLAlchemy < 0.9.0 it is possible to use sqlalchemy reflection.

SQLAlchemy reflection loads foreign/primary keys relations between tables. But doesn't create relations between mapped classes. Actually reflection doesn't create mapped classes for you - you have to specify mapped class name.

Actually I think that reflection support for loading foreign keys is a great helper and time saving tool. Using it you can build a query using joins without need to specify which columns to use for a join.

from sqlalchemy import *
from sqlalchemy import create_engine, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship



metadata = MetaData()
Base = declarative_base()
Base.metadata = metadata

db = create_engine('<db connection URL>',echo=False)
metadata.reflect(bind=db)

cause_code_table = metadata.tables['cause_code']
ndticket_table = metadata.tables['ndticket']

sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

q = session.query(ndticket_table,cause_code_table).join(cause_code_table)
for r in q.limit(10):
    print r

Also when I was using reflection to run queries to existing database - I had to define only mapped classes names, table bindings, relations, BUT there were no need to define table columns for these relations.

class CauseCode(Base):
    __tablename__ = "cause_code"

class NDTicket(Base):
    __tablename__ = "ndticket"
    cause_code = relationship("CauseCode", backref = "ndticket")


q = session.query(NDTicket)
for r in q.limit(10):
    print r.ticket_id, r.cause_code.cause_code

Overall SQLAlchemy reflection is already powerful tool and save me time, so adding relations manually is a small overhead for me.

If I would have to develop functionality that will add relations between mapped objects using existing foreign keys, I would start from using reflection with inspector. Using get_foreign_keys() method gives all information required to build relations - referred table name, referred column name and column name in target table. And would use this information for adding property with relationship into mapped class.

insp = reflection.Inspector.from_engine(db)
print insp.get_table_names()
print insp.get_foreign_keys(NDTicket.__tablename__)
>>>[{'referred_table': u'cause_code', 'referred_columns': [u'cause_code'], 'referred_schema': None, 'name': u'SYS_C00135367', 'constrained_columns': [u'cause_code_id']}]

Shilohshim

Answer 2

As of SQLAlchemy 0.9.1 the (for now experimental) Automap extension would seem to do just that: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html

Willson