Is it possible to declare relationships after creating automap class in SqlAlchemy

Question

I am new to sqlalchemy. I followed the tutorial to create an automap of an existing db with a relation from 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 mapped to one of the columns of table1 . But when I tried to use the query and joined table1 and table2 , it reports an error saying that "Cannot find the relationship with the foreign key." Since I know the relationship of these two tables, is there any way for me to declare this relationship after creating the class instance? Or is there a way to explicitly talk about this relationship in the query function? Thanks!

Answer 1

Although this can be done in the request, as @mpolednik mentioned, if I read your question correctly, the ideal solution would be to have a relation declared in your classes for reuse.

This can be achieved simply by pre-declaring the class you want to use, for example:

 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

Answer 2

You can do this explicitly using the onclause paremeter join method of SQLAlchemy join . The onclause onclause used similarly to SQL JOIN, for example.

 sqlalchemy.orm.join(table1, table2, onclause=table2.ID == table1.your_attribute) 

You can also specify a foreign key bypass in the database by simply specifying the foreign key in the class:

 Column('ID', Integer, ForeighKey('table1.your_attribute'))