我在python中有一个列表:
usernames = ["username 1", "Username 2", ... "Username 3"]
我必须执行sql查询来从表中选择所有行,其中非username等于列表中的任何元素。
results = cursor.execute("SELECT * FROM table WHERE NOT username = ?", usernames)
我怎样才能做到这一点?
您可以使用NOT IN
条件。不幸的是,您无法用值列表替换占位符,因此,您必须为usernames
列表中的每个元素创建一个占位符:
In [24]: c.execute('SELECT * FROM test').fetchall()
Out[24]: [('name1',), ('name2',), ('name3',)]
In [25]: usernames = ['name2', 'name4', 'name5']
In [26]: sql = 'SELECT * FROM test WHERE name NOT IN ({})'.format(','.join('?' * len(usernames)))
In [27]: c.execute(sql, usernames).fetchall()
Out[27]: [('name1',), ('name3',)]
In [28]: usernames += ['name1']
In [29]: sql = 'SELECT * FROM test WHERE name NOT IN ({})'.format(','.join('?' * len(usernames)))
In [30]: c.execute(sql, usernames).fetchall()
Out[30]: [('name3',)]