将 Python dict 用于 SQL INSERT 语句

编程基础网数据库问题

2021-01-01

Using a Python dict for a SQL INSERT statement(将 Python dict 用于 SQL INSERT 语句)

本文介绍了将 Python dict 用于 SQL INSERT 语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用 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())

这篇关于将 Python dict 用于 SQL INSERT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

Python