如何用最简单的方式将字典插入数据库表格？

Question

如何用最简单的方式将字典插入数据库表格？

5

我有一个包含键和值的字典，如下所示：

my_dict = {'a':33, 'b': 'something', 'c': GETDATE(), 'd': 55}

假设SQL表中的列名也像字典的键一样命名为"a,b,c,d"。

实际的字典有20多个键值对。

代码

我使用了pyodbc.connect创建了一个游标(cursor)，我可以使用它来执行一个SQL INSERT语句：

for k in my_dict.keys():
    cursor.execute(
    '''
        INSERT INTO TABLEabc (%s)
        VALUES (%s)
    '''
    % (k, my_dict[k])
    )

然而，这种方法似乎效率较低，因为每次都是新的 SQL 操作。

使用循环插入值的最简便方法是什么？
如何编写代码以便将所有值一次性插入？

- Mitch

那么你的表格像 TABLEabc 这样有20多列（就像字典中的键值对）？你期望为这个单独的字典创建1个表行，需要1个单独的INSERT语句吗？ - hc_dev

3个回答

1

使用SQLAlchemy，这个字典与我在SQL Server中构建的表相匹配。

mydict = {
  "Name": "Laura", "Form": "4B","Year": "7", "DOB" : "", "Col_5" : "",
  "Col_6" : "","Col_7" : "","Col_8" : ""  
}

print(mydict)

我使用这个方法将键和值传递到一个字符串中（经过一些操作），然后将其写入我的表格pypupil中。同时，我也想要写入所有记录的数据，无论它们是否存在于字典中。

fields = (str(list(mydict.keys()))[1:-1])
values = (str(list(mydict.values()))[1:-1])

columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in mydict.keys())
values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in mydict.values())
sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % ('pypupil', columns, values)

sql = sql.replace ( "`","") 
print(sql)

with engine.connect() as con:
    rs = con.execute(sql)

这种方法需要更多的信息和许多辅助工具，可以在此处查看。 https://discuss.dizzycoding.com/using-a-python-dict-for-a-sql-insert-statement/

- JonTout

请在上面链接中查找答案5/6。 - JonTout

1

注意：使用SQLAlchemy 1.4+，退出with engine.begin() as conn:块将自动提交，如果没有发生错误。退出with engine.connect() as conn:块将自动回滚，如果没有发生错误。 - Gord Thompson

这很有用，谢谢@GordThompson。 - JonTout

@JonTout 你知道如何将自动递增的ID添加到它吗？我得到一个错误ArgumentError：列类型NUMERIC（18,0）在列“tableABC.p_ID”上与autoincrement = True不兼容。 - Mitch

1

作为对JonTout回答的补充，如果你使用SQLAlchemy，那么你的字典已经可以被SQLAlchemy Core中的Table对象直接使用：

import datetime

import sqlalchemy as sa

connection_url = sa.engine.URL.create("mssql+pyodbc", … )
engine = sa.create_engine(connection_url)

table_abc = sa.Table("TABLEabc", sa.MetaData(), autoload_with=engine)

my_dict = {'a':33, 'b': 'something', 'c': datetime.date.today(), 'd': 55}

with engine.begin() as conn:
    conn.execute(table_abc.insert(), my_dict)

- Gord Thompson

抱歉，我不理解这个连接。您是在反射后修改Table对象吗？您可能需要提出一个新的问题。 - Gord Thompson

不，我正在尝试完成插入到具有自动增量 PK 的表中，但是我遇到了这个错误。您需要在 SQL 插入语句中指定自动增量吗？ - Mitch

1

@Mitch - 你可能发现了一个 bug。https://github.com/sqlalchemy/sqlalchemy/issues/8111 - Gord Thompson

@GordThompson 你也遇到了同样的错误吗？ - Mitch

1

@Mitch - 是的，它在GitHub问题中。 - Gord Thompson

显示剩余4条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Anentropic · Accepted Answer

如果您正在使用pyodbc，则可以尝试以下方法：

代码示例：

columns = {row.column_name for row in cursor.columns(table='TABLEabc')}

safe_dict = {key: val for key, val in my_dict.items() if key in columns}

# generate a parameterised query for the keys in our dict
query = "INSERT INTO TABLEabc ({columns}) VALUES ({value_placeholders})".format(
    columns=", ".join(safe_dict.keys()),
    value_placeholders=", ".join(["?"] * len(safe_dict)),
)

cursor.execute(query, list(safe_dict.values()))

这段代码旨在防止SQL注入攻击，因为：

我们只过滤实际数据库中的列名
我们使用pyodbc游标执行参数，因此值将被正确转义

但是，在以下情况下可能不起作用：

如果任何列名需要加引号和转义，则不会自动发生，从而导致失败

引号/转义是与数据库特定的，因此我们必须检查我们实际数据库的规则，并将其应用于我们格式化为查询的字典键。（或找到某种方法让pyodbc为我们完成，不确定是否可能）

如果您信任您的my_dict不包含恶意代码，则可以简化为：

query = "INSERT INTO TABLEabc ({columns}) VALUES ({value_placeholders})".format(
    columns=", ".join(my_dict.keys()),
    value_placeholders=", ".join(["?"] * len(my_dict)),
)

cursor.execute(query, list(my_dict.values()))