基于DataFrame更新SQL Server中表的某些列的最佳方法？

Question

我在SQLServer上有一个类似这样的表，其中每一行都有一个唯一的Event A和Event B组合

`Global Rules Table

ID   Event 1  |  Event 2  |  Validated as |  Generated as  |   Generated with score 
1      EA1       EB1           Rule            Anti-Rule            0.01
2      EA1       EB2           Rule            Rule                 0.95  
3      ...       ...           ...             ...                  ...

我有另一个表，它对Global Rules Table具有外键约束，称为Local Rules Table

我有一个熊猫数据框，看起来像这样

      Event 1  |  Event 2  |  Validated as |  Generated as  |   Generated with score 
        EA1       EB1           Rule            Rule                 0.85
        EA1       EB2           Rule            Rule                 0.95  
        ...       ...           ...             ...                  ...

因为我在Local Rules和Global Rules表之间有这个外键约束，所以我不能使用df.to_sql('Global Rules',con,if_exists='replace')

我想根据dataframe中的值在数据库中更新的列是Generated as和Generated with score，那么，根据我拥有的dataframe只更新数据库表中的这些列的最佳方法是什么呢？是否有一些现成的函数或库我不知道

Answer 1

我还没有找到一个图书馆来完成这项工作。我开始自己写一个，在PyPi上主持，但还没有完成

在这种情况下，针对SQL临时表的内部联接可以很好地工作。它将只更新SQL中的一个子集列，并且对于更新许多记录来说非常有效

我假设您正在使用pyodbc连接到SQL server

SQL游标

# quickly stream records into the temp table
cursor.fast_executemany = True

创建临时表

# assuming your DataFrame also has the ID column to perform the SQL join
statement = "CREATE TABLE [#Update_Global Rules Table] (ID BIGINT PRIMARY KEY, [Generated as] VARCHAR(200), [Generated with score] FLOAT)"
cursor.execute(statement)

将DataFrame插入到临时表中

# insert only the key and the updated values
subset = df[['ID','Generated as','Generated with score']]

# form SQL insert statement
columns = ", ".join(subset.columns)
values = '('+', '.join(['?']*len(subset.columns))+')'

# insert
statement = "INSERT INTO [#Update_Global Rules Table] ("+columns+") VALUES "+values
insert = [tuple(x) for x in subset.values]

cursor.executemany(statement, insert)

从临时表更新主表中的值

statement = '''
UPDATE
     [Global Rules Table]
SET
     u.Name
FROM
     [Global Rules Table] AS t
INNER JOIN 
     [#Update_Global Rules Table] AS u 
ON
     u.ID=t.ID;
'''

cursor.execute(statement)

放下临时桌子

cursor.execute("DROP TABLE [#Update_Global Rules Table]")