"SQL query" equivalent in SQLAlchemy

Question

I have two tables that names are cafe and cafe_address:

describe cafe;

Field	Type	Null	Key	Default	Extra
cafe_id	int unsigned	NO	PRI	NULL	auto_increment
cafe_name	varchar(64)	YES		NULL
city	varchar(64)	YES		NULL
province	varchar(128)	YES		NULL
phone_number	varchar(64)	YES		NULL
cost	tinyint	YES		NULL
work_start	time	YES		NULL
work_end	time	YES		NULL

describe cafe_address;

Field	Type	Null	Key	Default	Extra
cafe_id	int unsigned	YES	MUL	NULL
cafe_address	varchar(256)	YES		NULL

i want this query:

SELECT c.cafe_name,
c.cafe_id,
c.city,
ca.*
FROM cafe AS c
INNER JOIN cafe_address AS ca
ON c.cafe_id = ca.cafe_id;

what is the expression equal to this query in sqlalchemy ?!
Thanks !
i write this code:

connection.session.query(CafeAddress).join(Cafe, Cafe.cafe_id == CafeAddress.cafe_id).all()

but this result only have columns of cafe_address table

class CafeAddress(Base):
    __tablename__ = 'cafe_address'
    __table_args__ = (
        PrimaryKeyConstraint('cafe_id'), {},
    )
    cafe_id = Column(Integer, ForeignKey('cafe.cafe_id'))
    cafe_address = Column(String(256))

class Cafe(Base):
    __tablename__ = 'cafe'
    cafe_id = Column(Integer, primary_key=True, autoincrement=True)
    cafe_name = Column('cafe_name', String(64))
    city = Column('city', String(64))
    province = Column('province', String(64))
    phone_number = Column('phone_number', String(64))
    cost = Column(Integer)
    work_start = Column(Time, nullable=True)
    work_end = Column(Time, nullable=True)

"SQL query" equivalent in SQLAlchemy

0 Answers