将 Python dict 用于 SQL INSERT 语句

2021-11-20 00:00:00 python sql mysql

我正在尝试使用 dict 来执行 SQL INSERT.逻辑基本上是:

I am trying to use a dict to do a SQL INSERT. The logic would basically be:

INSERT INTO table (dict.keys()) VALUES dict.values()

但是，我很难找出正确的语法/流程来执行此操作.这是我目前拥有的:

However, I am having a tough time figuring out the correct syntax / flow to do this. This is what I currently have:

# data = {...}
sorted_column_headers_list = []
sorted_column_values_list = []
for k, v in data.items():
    sorted_column_headers_list.append(k)
    sorted_column_values_list.append(v)
sorted_column_headers_string = ', '.join(sorted_column_headers_list)
sorted_column_values_string = ', '.join(sorted_column_values_list)

cursor.execute("""INSERT INTO title (%s) 
            VALUES (%s)""", 
            (sorted_column_headers_string, sorted_column_values_string))

由此我得到一个 SQL 异常(我认为与逗号也包含在我拥有的某些值中的事实有关).执行上述操作的正确方法是什么?

From this I get a SQL exception (I think related to the fact that commas are also included in some of the values that I have). What would be the correct way to do the above?

推荐答案

您想向查询添加参数占位符.这可能会为您提供所需的内容:

You want to add parameter placeholders to the query. This might get you what you need:

qmarks = ', '.join('?' * len(myDict))
qry = "Insert Into Table (%s) Values (%s)" % (qmarks, qmarks)
cursor.execute(qry, myDict.keys() + myDict.values())

相关文章