Python如何从字典更新sql server ?

Question

我有一个INSERT语句，看起来像:

query = "INSERT TABLE ({columns}) VALUES ({value_placeholders})".format(
columns=", ".join(my_dict.keys()),
value_placeholders=", ".join(["?"] * len(my_dict)),
)
cursor.execute(query, list(my_dict.values()))

如何将其转换为UPDATE语句?

我已经得到了这个…

query = "UPDATE TABLE SET ({columns}) ({value_placeholders}) WHERE ID = ({id})".format(
columns=", ".join(my_dict.keys()),
value_placeholders=", ".join(["?"] * len(my_dict)),
id=id
)
cursor.execute(query, list(my_dict.values()))

不确定如何连接字典中的键/值对以使其成为正确的格式。

编辑:

my_dict ={
'key1': '1',
'key2': 'whatever',
'key3': Timestamp('1970-01-01 00:27:34.883683354'),
'key4': 'blabla',
'key5': Timestamp('1970-01-01 00:27:34.883882086'),
'ID': 11
}

Answer 1

这是您可以使用的生成的查询字符串

# Imports
import pandas as pd
from pandas import Timestamp
from datetime import datetime
# Input dict
my_dict ={
'key1': '1',
'key2': 'whatever',
'key3': Timestamp('1970-01-01 00:27:34.883683354'),
'key4': 'blabla',
'key5': Timestamp('1970-01-01 00:27:34.883882086'),
'ID': 11
}
# generate key/pair array
arr = []
for key,value in my_dict.items():
if type(value) is Timestamp: # Handle TimeStamps
arr.append("{key} = {value}".format(key=key, value=value.timestamp()))
elif type(value) is int: # Handle Integers
arr.append("{key} = {value}".format(key=key, value=value))
else: # Default Handler
arr.append("{key} = '{value}'".format(key=key, value=value))
# generate string from key/pair array
data = ", ".join(arr)
# format SQL string
id = 1
table_name = 'users'
query = "UPDATE {table_name} SET {data} WHERE id = {id};".format(table_name=table_name, data=data, id=id)
# Call Query
cursor.execute(query)

阅读更多