Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have created the following database definitions and test data:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
Base = declarative_base()
# build dynamic table
cols = {'__tablename__': 'mytable', 'A': Column(String, primary_key=True), 'B': Column(Integer)}
table = type('mytable', (Base,), cols)
cols2 = {'__tablename__': 'mychild', 'A': Column(String, primary_key=True), 'Z': Column(Integer)}
cols2['parent_id'] = Column(String, ForeignKey('mytable.A'))
child_table = type('mychild', (Base,), cols2)
child_table.parent = relationship('mytable', backref='mytable', primaryjoin='mytable.A==mychild.A')
# initialize engine
from sqlalchemy import create_engine
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
# generate a toy dataframe and dump to sql
import pandas as pd
df = pd.DataFrame.from_dict({'B': {'row1': 1}})
df.to_sql('mytable', engine, if_exists='append', index_label='A')
Session = sessionmaker(bind=engine)
session = Session()
session.query(child_table)
And this leads to the error
ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'mytable."A" = mychild."A"' on relationship mychild.parent.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.
In general, I am confused on how I can write queries against my tables. In this case, how would I do something like select * from mytable or even more useful SELECT B,Z from mytable JOIN mychild using A, which is what I am really trying to achieve through this.
You need to have a foreign key constraint declared in SQLAlchemy between the two primary join columns, even if you don't actually have one in the database:
cols2 = {
    '__tablename__': 'mychild', 
    'A': Column(String, ForeignKey("mytable.A"), primary_key=True), 
    'Z': Column(Integer)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.