使用元组的 Python 列表将多行插入数据库

Question

我有一个元组列表：

 list_ = [(1,7,3000),(1,8,3500), (1,9,3900)]

我想为给定 ID 更新包含多行/值的表（在本例中为 ID = 1）

所以：

 INSERT INTO table (ID, Speed, Power) VALUES (1,7,3000),(1,8,3500),(1,9,3900)

我在格式方面遇到问题 - 我已经将字符串简化为如下内容：

 INSERT INTO ... VALUES ((1,7,3000),(1,8,3500),(1,9,3900))

但是当然这不起作用，因为元组周围有额外的括号。有什么想法可以构建一种“Python 方式”执行此操作的方法吗？

原文由 mk8efz 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

那么，你需要构建这条线：

 INSERT INTO ... VALUES (1,7,3000), (1,8,3500), (1,9,3900)

试试那个：

 rows = [(1,7,3000), (1,8,3500), (1,9,3900)]
values = ', '.join(map(str, rows))
sql = "INSERT INTO ... VALUES {}".format(values)

原文由 Viach Kakovskyi 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

在 Python 中处理这个问题的惯用方法是使用正在使用的数据库驱动程序提供的 游标 的 executemany 方法。

例如对于sqlite使用标准库中的 sqlite3 模块

conn = sqlite3.connect('/path/to/file.db')
cursor = conn.cursor()
sql = """INSERT INTO mytable (ID, Speed, Power) VALUES (?, ?, ?)"""
values = [(1,7,3000),(1,8,3500),(1,9,3900)]
cursor.executemany(stmt, values)

VALUES 子句中使用的占位符因特定驱动程序而异。可以在驱动程序的文档中或通过查找驱动程序模块的 paramstyle 属性找到正确的值。

使用这种方法而不是字符串插值/格式化或 f-strings 可确保正确引用值，从而防止 SQL 注入和其他错误：

 >>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> date = '2020-11-23'

>>> # Correctly quoted input is returned as the selected value
>>> cur.execute("""SELECT ? AS today""", (date,)) # <- execute requires a tuple as values
<sqlite3.Cursor object at 0x7f1fa205e1f0>
>>> cur.fetchone()
('2020-11-23',)

>>> # Unquoted input is evaluated as an expression!
>>> cur.execute(f"""SELECT {date} AS today""")
<sqlite3.Cursor object at 0x7f1fa205e1f0>
>>> cur.fetchone()
(1986,)

下面是一个使用字符串格式的 SQL 注入示例。因为值“name”没有被转义，当程序员的意图只是返回一个时，查询返回了表中的所有用户名和密码。

 NAMES = [('Alice', 'apple'),  ('Bob', 'banana'),  ('Carol', 'cherry')]

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("""CREATE TABLE users (name text, password text)""")
cur.executemany("""INSERT INTO users (name, password) VALUES (?, ?)""", NAMES)
conn.commit()
cur.execute("""SELECT name, password FROM users WHERE name = {}""".format('name'))
for row in cur.fetchall():
    print(row)

如果该值被正确转义：

  cur.execute("""SELECT name, password FROM users WHERE name = ?""", ('name',))

不会返回任何行，从而击败了攻击。

原文由 snakecharmerb 发布，翻译遵循 CC BY-SA 4.0 许可协议