代码之家 › 专栏 › 技术社区 › Outcast

在SQL语句中解包元组

mysql python

Outcast · 技术社区 · 6 年前

我在用 Python 和 PyMySQL . 我想根据它们的id从mysql数据库中获取一些项:

items_ids = tuple([3, 2])
sql = f"SELECT * FROM items WHERE item_id IN {items_ids};"

我正在使用格式化的字符串文本( f" " , https://docs.python.org/3/whatsnew/3.6.html#whatsnew36-pep498 )计算SQL语句中的元组。

但是,我想按照元组指定的顺序取回项,因此首先 item_id = 3 然后这个项目 item_id = 2 . 为了达到这个目的,我必须使用 ORDER BY FIELD 条款(另见: Ordering by the order of values in a SQL IN() clause )

但如果我写这样的话:

items_ids = tuple([3, 2])
sql = f"SELECT * FROM items WHERE item_id IN {items_ids} ORDER BY FIELD{(item_id,) + items_ids};"

然后 item_id 在 按字段排序 子句被视为未声明的python变量

如果我写这样的东西:

items_ids = tuple([3, 2])
sql = f"SELECT * FROM items WHERE item_id IN {items_ids} ORDER BY FIELD{('item_id',) + items_ids};"

然后 项目ID 在 按字段排序 子句被视为字符串而不是sql变量,在本例中 按字段排序 什么都不做。

如何计算元组 (item_id,) + items_ids 通过维护 项目ID 作为 按字段排序 条款?

显然,我可以在从数据库返回后根据 items_ids 我不想再为mysql操心,但我只是想知道如何做到这一点。

2 回复 | 直到 6 年前

Ilja Everilä 6 年前

请不要使用f字符串或任何字符串格式将值传递给sql查询。这是通往 SQL injection . 现在你可能会想:“这是一个整数元组,会发生什么不好的事情?”首先,单元素python元组的字符串表示形式不是有效的sql。其次,有些人可能会使用用户可控制的数据(而不是整数元组)来遵循这个示例(因此,让这些坏示例在线会使这个习惯长期存在)。另外,你不得不求助于你的“狡猾”解决方案的原因是,在工作中使用了错误的工具。

将值传递给sql查询的正确方法是 use placeholders . 对于pymysql,占位符有点混乱 %s . 不要把它和手动格式混合在一起。如果必须向查询传递可变数量的值,则必须使用某种字符串生成,但生成的是占位符,而不是值:

item_ids = (3, 2)
item_placeholders = ', '.join(['%s'] * len(item_ids))

sql = f"""SELECT * FROM items
          WHERE item_id IN ({item_placeholders})
          ORDER BY FIELD(item_id, {item_placeholders})"""

# Produces:
#
#     SELECT * FROM items
#     WHERE item_id IN (%s, %s)
#     ORDER BY FIELD(item_id, %s, %s)

with conn.cursor() as cur:
    # Build the argument tuple
    cur.execute(sql, (*item_ids, *item_ids))
    res = cur.fetchall()

Outcast 6 年前

解决方案 .format() 具体如下:

items_ids = tuple([3, 2])
items_placeholders = ', '.join(['{}'] * len(items_ids))

sql = "SELECT * FROM items WHERE item_id IN {} ORDER BY FIELD(item_id, {});".format(items_ids, items_placeholders).format(*items_ids)

# with `.format(items_ids, items_placeholders)` you get this: SELECT * FROM items WHERE item_id IN (3, 2) ORDER BY FIELD(item_id, {}, {});
# and then with `.format(*items_ids)` you get this: SELECT * FROM items WHERE item_id IN (3, 2) ORDER BY FIELD(item_id, 3, 2);

一个相当棘手的解决方案 f-strings 具体如下:

sql1 = f"SELECT * FROM items WHERE item_id IN {item_ids} ORDER BY FIELD(item_id, "
sql2 = f"{items_ids};"
sql = sql1 + sql2[1:]

# SELECT * FROM items WHERE item_id IN (3, 2) ORDER BY FIELD(item_id, 3, 2);

但作为 @IIija 提到,我可能会得到 SQL injection 因为 IN {item_ids} 不能像这样容纳一个元素元组。

另外,使用 F字符串 在字符串中解包元组可能比使用如前所述( Formatted string literals in Python 3.6 with tuples )因为你不能使用 * 在 f-string . 但是,也许您可以为此想出一个解决方案(使用迭代器?)制作这个

sql = f"SELECT * FROM items WHERE item_id IN ({t[0]}, {t[1]}) ORDER BY FIELD(item_id, {t[0]}, {t[1]});"

即使我现在还没有解决这个问题的办法。如果你有这种解决方案,欢迎你发帖。