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

I have a python list, say l

我有一个python列表，比如l

l = [1,5,8]

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

我想编写一个sql查询来获取列表中所有元素的数据，比如

"select name from studens where id = |IN THE LIST l|"

“在表l|中id = |的studens中选择名字”

How do i accomlish this?

我如何适应这个?

9 个解决方案

#1

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.

到目前为止，答案都是将值模板化到一个纯SQL字符串中。这对于整数来说是完全没问题的，但是如果我们想对字符串做，我们会得到转义问题。

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)

#2

The SQL you want is

您需要的SQL是

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

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

如果您想从python中构造这个，可以使用它

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.

map函数将把列表转换成一个字符串列表，可以使用string .join方法通过逗号将这些字符串粘在一起。

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

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

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))

#3

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 !!!

我希望这对你有帮助!!!

#4

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

字符串。加入由逗号分隔的列表值，并使用format操作符形成一个查询字符串。

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

(Thanks, blair-conrad)

(谢谢,blair-conrad)

#5

I like bobince's answer:

我喜欢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)

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:

我发现这更直接，如果不那么聪明，也不那么普遍。这里要求l具有长度(即引用定义__len__方法的对象)，这应该不是问题。但是占位符也必须是单个字符。支持多字符占位符的使用:

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

#6

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

@ uminstalled answer的解决方案，因为它是用一个元素元组破坏的，因为(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,)]

#7

For example, if you want the sql query:

例如，如果您想要sql查询:

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(']',')') )

#8

Easiest way is to turn the list to tuple first

最简单的方法是首先将列表转换为tuple

t = tuple(l)
select name from studens where id IN" + str(t)

#9

As to me , i want to query info from db by id list(the below code is a sql example)

至于我，我想通过id列表从db中查询信息(下面的代码是一个sql示例)

lst = [1, 2, 3]
str_lst = ','.join([str(item) for item in lst])
sql = """select url ,uid from tb_name where id in (%s)""" % str_lst
print sql

output

输出

select url ,uid from tb_name where id in (1,2,3)

选择url，来自tb_name的uid(1、2、3)

practice class member function:

实践类成员函数:

def get_url_by_id_lst(self, lst_id):
    str_lst = ','.join([str(item) for item in lst_id])
    sql = """
      select url ,uid from tb_face_rec_sample_xy where id in (%s)
    """ % str_lst
    self.cursor.execute(sql)
    return [row for row in self.cursor]

#1