Convert a list to comma separated strings for sql IN operator

This seems very obvious use case and I am surprised I was not able to find any single working solution for this.

I have a method which returns a list of usernames something similar to this:

username = ["Tom", "Bob", "Charley"]

Now I want to use this variable to build a sql for IN operator, something similar to this:

sql = "Select * from users Where username IN %s" % username;

above line obviously fails due to syntax error.

Then I somwehere found that, I can change this to tupple:

username_tuple = tuple(username)
sql = "Select * from users Where username IN {}".format(username_tuple)

Above line works well but if there are just one string in the list (username = [“tom”]), then it fails with syntax error because username_tuple evaluates to (“tom”,) (please note trailing comma after username.)

Answer

Rather then reinvent the wheel, use the built in list adaption from psycopg2. List adaption:

username = ["Tom", "Bob", "Charley"]
cur.execute("Select * from users Where username = ANY(%s)", [username])