sql查询中的python列表作为参数

问题:sql查询中的python列表作为参数

我有一个python列表,说我

l = [1,5,8]

我想编写一个SQL查询来获取列表中所有元素的数据,例如

select name from students where id = |IN THE LIST l|

我该如何完成?

I have a python list, say l

l = [1,5,8]

I want to write a sql query to get the data for all the elements of the list, say

select name from students where id = |IN THE LIST l|

How do I accomplish this?


回答 0

到目前为止,答案一直是将这些值模板化为纯SQL字符串。这对于整数绝对没问题,但是如果我们想对字符串进行处理,则会遇到转义问题。

这是一个使用参数化查询的变体,它对两个都适用:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

Answers so far have been templating the values into a plain SQL string. That’s absolutely fine for integers, but if we wanted to do it for strings we get the escaping issue.

Here’s a variant using a parameterised query that would work for both:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

回答 1

最简单的方法是将列表转到tuple第一

t = tuple(l)
query = "select name from studens where id IN {}".format(t)

Easiest way is to turn the list to tuple first

t = tuple(l)
query = "select name from studens where id IN {}".format(t)

回答 2

不要使其复杂化,解决方案很简单。

l = [1,5,8]

l = tuple(l)

params = {'l': l}

cursor.execute('SELECT * FROM table where id in %(l)s',params)

我希望这可以帮助!!!

Dont complicate it, Solution for this is simple.

l = [1,5,8]

l = tuple(l)

params = {'l': l}

cursor.execute('SELECT * FROM table where id in %(l)s',params)

I hope this helped !!!


回答 3

您想要的SQL是

select name from studens where id in (1, 5, 8)

如果您想从python构造它,可以使用

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'

地图功能将改变列表转换成可以通过使用逗号胶合在一起的字符串列表str.join方法。

或者:

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join((str(n) for n in l)) + ')'

如果您更喜欢生成器表达式而不是map函数。

更新:S. Lott在评论中提到Python SQLite绑定不支持序列。在这种情况下,您可能想要

select name from studens where id = 1 or id = 5 or id = 8

产生者

sql_query = 'select name from studens where ' + ' or '.join(('id = ' + str(n) for n in l))

The SQL you want is

select name from studens where id in (1, 5, 8)

If you want to construct this from the python you could use

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'

The map function will transform the list into a list of strings that can be glued together by commas using the str.join method.

Alternatively:

l = [1, 5, 8]
sql_query = 'select name from studens where id in (' + ','.join((str(n) for n in l)) + ')'

if you prefer generator expressions to the map function.

UPDATE: S. Lott mentions in the comments that the Python SQLite bindings don’t support sequences. In that case, you might want

select name from studens where id = 1 or id = 5 or id = 8

Generated by

sql_query = 'select name from studens where ' + ' or '.join(('id = ' + str(n) for n in l))

回答 4

string.join用逗号分隔的列表值,并使用format运算符形成查询字符串。

myquery = "select name from studens where id in (%s)" % ",".join(map(str,mylist))

(谢谢,布莱尔康拉德

string.join the list values separated by commas, and use the format operator to form a query string.

myquery = "select name from studens where id in (%s)" % ",".join(map(str,mylist))

(Thanks, blair-conrad)


回答 5

我喜欢bobince的回答:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

但是我注意到了这一点:

placeholders= ', '.join(placeholder for unused in l)

可以替换为:

placeholders= ', '.join(placeholder*len(l))

如果不太聪明和不太笼统,我会觉得这更直接。这里l需要有一个长度(即,引用一个定义__len__方法的对象),这应该不是问题。但是占位符也必须是单个字符。要支持多字符占位符使用:

placeholders= ', '.join([placeholder]*len(l))

I like bobince’s answer:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

But I noticed this:

placeholders= ', '.join(placeholder for unused in l)

Can be replaced with:

placeholders= ', '.join(placeholder*len(l))

I find this more direct if less clever and less general. Here l is required to have a length (i.e. refer to an object that defines a __len__ method), which shouldn’t be a problem. But placeholder must also be a single character. To support a multi-character placeholder use:

placeholders= ', '.join([placeholder]*len(l))

回答 6

@umount答案的解决方案,因为它用一个元素的元组中断,因为(1,)不是有效的SQL。

>>> random_ids = [1234,123,54,56,57,58,78,91]
>>> cursor.execute("create table test (id)")
>>> for item in random_ids:
    cursor.execute("insert into test values (%d)" % item)
>>> sublist = [56,57,58]
>>> cursor.execute("select id from test where id in %s" % str(tuple(sublist)).replace(',)',')'))
>>> a = cursor.fetchall()
>>> a
[(56,), (57,), (58,)]

sql字符串的其他解决方案:

cursor.execute("select id from test where id in (%s)" % ('"'+'", "'.join(l)+'"'))

Solution for @umounted answer, because that broke with a one-element tuple, since (1,) is not valid SQL.:

>>> random_ids = [1234,123,54,56,57,58,78,91]
>>> cursor.execute("create table test (id)")
>>> for item in random_ids:
    cursor.execute("insert into test values (%d)" % item)
>>> sublist = [56,57,58]
>>> cursor.execute("select id from test where id in %s" % str(tuple(sublist)).replace(',)',')'))
>>> a = cursor.fetchall()
>>> a
[(56,), (57,), (58,)]

Other solution for sql string:

cursor.execute("select id from test where id in (%s)" % ('"'+'", "'.join(l)+'"'))

回答 7

placeholders= ', '.join("'{"+str(i)+"}'" for i in range(len(l)))
query="select name from students where id (%s)"%placeholders
query=query.format(*l)
cursor.execute(query)

这应该可以解决您的问题。

placeholders= ', '.join("'{"+str(i)+"}'" for i in range(len(l)))
query="select name from students where id (%s)"%placeholders
query=query.format(*l)
cursor.execute(query)

This should solve your problem.


回答 8

如果您将PostgreSQL与Psycopg2库一起使用,则可以让其元组适应为您完成所有转义和字符串插值,例如:

ids = [1,2,3]
cur.execute(
  "SELECT * FROM foo WHERE id IN %s",
  [tuple(ids)])

即只需确保您将IN参数作为传递tuple。如果是,则list可以使用= ANY数组语法

cur.execute(
  "SELECT * FROM foo WHERE id = ANY (%s)",
  [list(ids)])

请注意,这两个都将变成相同的查询计划,因此您应该只使用较容易的那个。例如,如果您的列表位于一个元组中,则使用前者;如果它们存储在列表中,则使用后者。

If you’re using PostgreSQL with the Psycopg2 library you can let its tuple adaption do all the escaping and string interpolation for you, e.g:

ids = [1,2,3]
cur.execute(
  "SELECT * FROM foo WHERE id IN %s",
  [tuple(ids)])

i.e. just make sure that you’re passing the IN parameter as a tuple. if it’s a list you can use the = ANY array syntax:

cur.execute(
  "SELECT * FROM foo WHERE id = ANY (%s)",
  [list(ids)])

note that these both will get turned into the same query plan so you should just use whichever is easier. e.g. if your list comes in a tuple use the former, if they’re stored in a list use the latter.


回答 9

例如,如果要使用sql查询:

select name from studens where id in (1, 5, 8)

关于什么:

my_list = [1, 5, 8]
cur.execute("select name from studens where id in %s" % repr(my_list).replace('[','(').replace(']',')') )

For example, if you want the sql query:

select name from studens where id in (1, 5, 8)

What about:

my_list = [1, 5, 8]
cur.execute("select name from studens where id in %s" % repr(my_list).replace('[','(').replace(']',')') )

回答 10

一个更简单的解决方案:

lst = [1,2,3,a,b,c]

query = f"""SELECT * FROM table WHERE IN {str(lst)[1:-1}"""

a simpler solution:

lst = [1,2,3,a,b,c]

query = f"""SELECT * FROM table WHERE IN {str(lst)[1:-1}"""

回答 11

l = [1] # or [1,2,3]

query = "SELECT * FROM table WHERE id IN :l"
params = {'l' : tuple(l)}
cursor.execute(query, params)

:var符号似乎简单。(Python 3.7)

l = [1] # or [1,2,3]

query = "SELECT * FROM table WHERE id IN :l"
params = {'l' : tuple(l)}
cursor.execute(query, params)

The :var notation seems simpler. (Python 3.7)


回答 12

这使用参数替换并处理单个值列表的情况:

l = [1,5,8]

get_operator = lambda x: '=' if len(x) == 1 else 'IN'
get_value = lambda x: int(x[0]) if len(x) == 1 else x

query = 'SELECT * FROM table where id ' + get_operator(l) + ' %s'

cursor.execute(query, (get_value(l),))

This uses parameter substitution and takes care of the single value list case:

l = [1,5,8]

get_operator = lambda x: '=' if len(x) == 1 else 'IN'
get_value = lambda x: int(x[0]) if len(x) == 1 else x

query = 'SELECT * FROM table where id ' + get_operator(l) + ' %s'

cursor.execute(query, (get_value(l),))