CODEWITHSUNDEEP
pythonsqljoinsqlalchemy
sanyassh
sanyassh

Reputation: 8520

Join without foreign key constraint

I am trying to perform JOIN between two tables, for which I described the relation with primaryjoin attribute. I expected that it will be used to build a query, but it is not, and it is failing with sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships. Below is the whole code needed to reproduce the error. Please suggest how can I describe my tables and how do I construct a query in a situation when there must be no ForeignKey constraints between these tables:

from sqlalchemy import Column, Integer, String, MetaData
from sqlalchemy import create_engine
from sqlalchemy.sql import select, insert
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship


METADATA = MetaData()
SQLAlchemyTable = declarative_base(metadata=METADATA)


class Cities(SQLAlchemyTable):
    __tablename__ = 'cities'

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)


class Orders(SQLAlchemyTable):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, nullable=True)

    # relation creation code is taken from 
    # https://stackoverflow.com/questions/37806625/sqlalchemy-create-relations-but-without-foreign-key-constraint-in-db
    city = relationship(
        'Cities',
        foreign_keys=[city_id],
        primaryjoin='Orders.city_id==Cities.id'
    )


if __name__ == '__main__':
    connection_string = 'sqlite:///test_join.db'
    db = create_engine(connection_string)
    METADATA.drop_all(db)
    METADATA.create_all(db)
    db.execute(insert(Cities).values([{'id': 1, 'name': 'city_name_1'}]))
    db.execute(insert(Cities).values([{'id': 2, 'name': 'city_name_2'}]))
    db.execute(insert(Orders).values([{'id': 1, 'city_id': 1}]))
    db.execute(insert(Orders).values([{'id': 2, 'city_id': None}]))
    db.execute(insert(Orders).values([{'id': 3, 'city_id': 3}]))
    # i need to get this query:
    print(list(db.execute('SELECT orders.id, orders.city_id FROM orders JOIN cities ON orders.city_id = cities.id WHERE cities.id = 1')))
    # how?
    query = select([Orders]).join(Cities).where(Cities.id == 1)  # not working
    print(query)
    print(list(db.execute(query)))

Upvotes: 1

Views: 1070

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

A relationship is an ORM construct, but you're using the Core to build your query. You have to explicitly define how to join between the two:

query = select([Orders]).\
    select_from(join(Orders, Cities, Orders.city_id == Cities.id)).\
    where(Cities.id == 1)

Note the use of Select.select_from() and join() instead of select([...]).join(). If using the Query API you can define the join based on the relationship:

query = session.query(Orders).\
    join(Orders.city).\
    filter(Cities.id == 1)

Upvotes: 1

Related Questions