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 built two tables in my postgres CLI (test2_table: id, name, age, profession, city, country). The second is referenced to my 1st table's id and has a column called wage. When I run the syntax everything is fine.

SELECT name, age, profession, city, country, wage FROM test2_table JOIN salary ON salary.test2_id = test2_table.id; 
This works - a table with wages is printed for me to see.
My only problem is when I try the same thing to my SQLAlchemy database class.  When I try to join the classes together I get an error:
sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'test2_table.id = salary.id' on relationship Salary.wages.
Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.
my database classes:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Test_db_02(Base):
    __tablename__ = 'test2_table'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(40))
    age = Column('age', Integer)
    profession = Column('profession', String(60))
    city = Column('city', String(60))
    country = Column('country', String(40))
class Salary(Base):
    __tablename__ = 'salary'
    id = Column('id', Integer, primary_key=True)
    wage = Column('wage', String(20))
    test2_id = Column('test2_id', Integer, ForeignKey('test2_table.id'))
    wages = relationship("Test_db_02", backref="salary", primaryjoin="Test_db_02.id == Salary.id")
I have a simple report page that prompts the user to chose from a drop-down option selector.  Before I tried to join the two tables togteher, my queries were working well, now after my attempt to join I get the error.  The reports @app has been scaled down for this example.
Reports @app.route
Session = sessionmaker(bind=engine)
session = Session()
@app.route('/reports', methods=['GET', 'POST'])
if request.method == 'GET':
    return render_template('reports.html')
else:
    if request.form.get("report_options") == "all_db":
        db_entry = session.query(Test_db_02).order_by(Test_db_02.id)
        db_entry = db_entry.all()
        return render_template('reports.html', db_entry=db_entry)
    elif request.form.get("report_options") == "name":
        db_entry = session.query(Test_db_02).order_by(Test_db_02.id)
        data_name = db_entry.all()
        return render_template('reports.html', data_name=data_name)
    elif request.form.get("report_options") == "media_prof":
        db_entry = session.query(Test_db_02).join(test2_table.salary)
        media_prof = db_entry.all()
        return render_template('reports.html', media_prof=media_prof)
If I am honest I have read through the sqlalchemy documentation on relationships, joins & foreign keys (and watched some YouTube tutorials) but it still seems a bit confusing..
The main challenge for me is to be able to join two tables together. Once I have accomplished that, I will attempt to iterate through them with the flask/jinja set up.
                If your SQL works in the psql CLI but not in Python, then you have a Python problem and not a database or dba related one! Please delete the question here and move it to StackOverflow or a Python forum where it belongs. I don't wish to be rude, but your question as it stands is off-topic. Come back to us when you have database related questions - like a JOIN not working or similar. My profile has a number of articles on how to ask questions here. p.s. welcome to the forum! :-)
– Vérace
                Aug 11, 2019 at 18:55
Your primaryjoin condition in Salary.wages is wrong. Presumably, Salary.idis an autoincrement primary key column and as you have no ForeignKey constraining values of  Salary.id to values of Test_db_02.id, its unlikely that you want to include that column in the join condition as you have done:
primaryjoin="Test_db_02.id == Salary.id"
Its more likely that you want to relate the two models through the Salary.test2_id attribute, as you have the ForeignKey constraint on that column:
primaryjoin="Test_db_02.id == Salary.test2_id"
                thank you so much this works! but i am getting the class object returned instead of the values when I execute the same code with the changes.
– Sinclair Akoto
                Aug 12, 2019 at 15:42
        
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.