CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Ian Fiddes
Ian Fiddes

Reputation: 3011

Could not locate foreign key columns for primary join condition

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.

Upvotes: 0

Views: 4497

Answers (1)

univerio
univerio

Reputation: 20538

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)
}

Upvotes: 4

Related Questions