Fetching data from the table based on the Id of another table fastAPI and sqlalchemy-CodePudding

I want to do a database operation where I have 2 tables (Table1 and Table2). There is an "id" column in Table1 and in table2 I have 4 column (id, server, port, endpoint). So I want to compare id of both table and if match I want server, port and endpoint details. I am using fastAPI with sqlalchemy.

Model file is something like this

#Table1
class RD(Base):
    __tablename__ = "table1"
    id = Column(String, unique=True, index=True, nullable=False)

#Table2
class AD(Base):
    __tablename__ = "table2"
    id = Column(String, unique=True, index=True, nullable=False)
    server = Column(String(100), index=True, nullable=False)
    port = Column(String, index=True, nullable=False)
    endpoint = Column(String, index=True, nullable=False)

Now i want comapare the id's of both the table and if matched i want to print server, port and endpoint from table 2. Also i dont want to write a raw sql queries. i want to write a ORM based query.

my database connection file

Thanks.

CodePudding user response：

I Tried your code with some modifications because a few lines gave me an error at the id column definition and for table1 name("response")

class Table1(Base):
    __tablename__ = "table1"
    id = Column(String, primary_key=True, index=True)
    
class Table2(Base):
    __tablename__ = "table2"
    id = Column(String, primary_key=True, index=True)
    server = Column(String(100), index=True, nullable=False)
    port = Column(String, index=True, nullable=False)
    endpoint = Column(String, index=True, nullable=False)

# Insert few rows in tables
db.add(Table1(id=1))
db.add(Table1(id=2))
db.add(Table1(id=3))

db.add(Table2(id=1, server='localhost', port=8000, endpoint='/home'))
db.add(Table2(id=2, server='localhost', port=8000, endpoint='/cart'))
db.add(Table2(id=5, server='localhost', port=8000, endpoint='/item'))

db.commit()

now when I execute this query:

result = db.query(Table2).filter(Table1.id == Table2.id).all()
for row in result:
    print(row.server, row.port, row.endpoint)

I got the rows from table2 having ids equal to id in table 1

localhost 8000 /home
localhost 8000 /cart