2

I have a table with a composite primary key.

ReportsTable: id1,id2,data,...

I want to delete multiple rows based on the composite key list. Eg. [(1,0),(1,1),(2,0),(3,0),(3,1),(3,2),(3,3)]

How to delete the records which match the ids list using sqlalchemy? I tried to do it by constructing the query by chaining or_s in a loop.

tuple_list = [(1,0),(1,1),(2,0),(3,0),(3,1),(3,2),(3,3)]
with runtime.terminating_sn('adb', 'user', cid) as session:
    base_or = or_(
        and_(Reports.id1 == tuple_list[0][0], Reports.id2 == tuple_list[0][1]),
        and_(Reports.id1 == tuple_list[1][0], Reports.id2 == tuple_list[1][1]))
    for i in range(2, len(tuple_list)):
        base_or = or_(base_or, and_(Reports.id1 == tuple_list[i+1][0],
                                    Reports.id2 == tuple_list[i+1][1]))
    session.query(Reports).filter(base_or).delete()
    session.commit()

I would like to know if there is a better way to do it in python.

brainless
  • 5,698
  • 16
  • 59
  • 82

0 Answers0