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.
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