sqlalchemy在where子句中动态使用和

pdtvr36n 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(444)

我正在尝试使用sqlalchemy构建一个delete查询。delete子句的where部分应该动态构造以满足多个条件。例如：

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

下面是我的代码的简化部分。

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)

...

对于从具有单个主键列的表中删除行来说，这种方法运行得很好。我想通过考试 key_col 作为具有复合主键的表的列表。
我可以做一些像

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)

但我希望能够在运行时根据 key_col . 我猜有更好的方法可以使用sqlalchemy执行这样的删除查询。

sql python pandas sqlalchemy

来源：https://stackoverflow.com/questions/63138442/sqlalchemy-dynamic-use-of-and-in-where-clause

1条答案

按热度按时间

s8vozzvw1#

sqlalchemy将接受多个 .where 构造并将它们结合在一起，例如。，

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)
"""

赞(0）回复(0）举报 2021-07-29

我来回答

sqlalchemy在where子句中动态使用和

1条答案

相关问题

热门标签

最新问答