sqlalchemy dynamic use of and_ in where clause

user_10816754 0
Viewed 357

I am attempting to use sqlalchemy to build a delete query. The where portion of the delete clause should be constructed dynamically to satisfy multiple conditions. For example:

DELETE FROM table
WHERE table.col1 = x1
  AND table.col2 = x2
  AND ...

The following is a simplified portion of code I have.

def upsert(key_col):
    
...
            
    # Build the WHERE clause of your DELETE statement from rows in the dataframe.
    cond = df.apply(lambda row: sa.and_(detail_table.c[key_col] == row[key_col]), axis=1)
    cond = sa.or_(*cond)
    
    # Define and execute the DELETE
    delete = detail_table.delete().where(cond)
    with engine.connect() as conn:
        conn.execute(delete)

...

This runs fine for deleting rows from a table with a single primary key column. I would like to be able to pass key_col as a list for tables having a composite primary key.

I can do something like

cond = df.apply(lambda row: sa.and_(detail_table.c[key_col[0]] == row[key_col[0]], 
                                    detail_table.c[key_col[1]] == row[key_col[1]],
                                    detail_table.c[key_col[2]] == row[key_col[2]]), 
                                    axis=1)

but I want to be able to create the above dynamically at runtime based on the size of key_col. I'm guessing there is a better way to execute such a delete query using sqlalchemy.

pandas sqlalchemy sql python
1 Answers

SQLAlchemy will accept multiple .where constructs and AND them together, e.g.,

import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://@mssqlLocal64", echo=True)

detail_table = sa.Table(
    "#detail_table",
    sa.MetaData(),
    sa.Column("col1", sa.Integer),
    sa.Column("col2", sa.Integer),
)
detail_table.create(bind=engine)

# test data
criteria = [(detail_table.c.col1, 3), (detail_table.c.col2, 5)]

del_stmt = detail_table.delete()
for crit in criteria:
    col, val = crit
    del_stmt = del_stmt.where(col == val)

with engine.begin() as conn:
    conn.execute(del_stmt)
"""console output:
sqlalchemy.engine.base.Engine DELETE FROM [#detail_table] WHERE [#detail_table].col1 = ? AND [#detail_table].col2 = ?
sqlalchemy.engine.base.Engine (3, 5)
"""
user_21443900
Related