Is it possible to declare relationship after class is created by automap in SqlAlchemy

Question

I'm new to sqlalchemy. I have followed the tutorial to create the automap of a existing db with relationship from a mysql db

from sqlalchemy import create_engine, MetaData, Column, Table, ForeignKey
from sqlalchemy.ext.automap import automap_base, generate_relationship
from sqlalchemy.orm import relationship, backref
from config import constr, mytables

def _gen_relationship(base, direction, return_fn,
                  attrname, local_cls, refferred_cls, **kw):
    return generate_relationship(base, direction, return_fn, attrname, local_cls, refferred_cls, **kw)

engine = create_engine(constr)
metadata = MetaData()
metadata.reflect(engine, only=mytables)
Base = automap_base(metadata=metadata)
Base.prepare(engine, reflect=True, generate_relationship=_gen_relationship)
Tableclass1 = Base.classes.table1
Tableclass2 = Base.classes.table2

Table2.ID maps to one of table1's columns. But when I was trying to use query and join table1 and table2, it reports error saying that "Can't find any foreign key relationships". Since I know the relationship of these 2 tables, is there way for me the declare this relationship after the class instance has been created? Or is there way to explicitly tell this relationship in query function? Thanks!

Answer 1

While it is possible to do this in the query as @mpolednik mentioned, if I read your question correctly, the ideal solution would be to have the relationship declared in your classes for repeated use.

It can be achieved simply by pre-declaring the class that you wish to use, like so:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session, relationship
from sqlalchemy import create_engine, Column, String

Base = automap_base()

engine = create_engine("postgresql://user:pass@localhost:5432/mydb")

# pre-declare User for the 'user' table
class User(Base):
    __tablename__ = 'user'

    # override schema elements like Columns
    oname = Column('originalname', String)

    # and a relationship. I name it 'weird' because in my database schema
    # this relationship makes absolutely no sense, but it does demonstrate
    # the point
    weird = relationship("usergroup",
                         foreign_keys='usergroup.id',
                         primaryjoin='and_(usergroup.id==User.id)')

Base.prepare(engine, reflect=True)
session = Session(engine)

# Test this by querying the User table and then following the relationship
u = session.query(User).filter(User.oname == 'testuser').one()
print (u.oname)
for g in u1.weird:
    print g.name

See here for the docs (including another example): http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html?highlight=automap#specifying-classes-explicitly