I have a list in python:
usernames = ["username 1", "Username 2", ... "Username 3"]
I have to perform a sql query to select all rows from a table where not username equals any of the elements in the list.
results = cursor.execute("SELECT * FROM table WHERE NOT username = ?", usernames)
How can I achieve this?
You could use NOT IN condition. Unfortunately, you cannot replace a placeholder with a list of values, so, you have to create a placeholder for each element in the usernames list:
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',)]
