python - Return records for 100 specific surnames

Question

I have a tuple, l, with 100 surnames. How can I do something like this in sqlite3:

l = ("Smith", "Murphy", "Owens", ...)
with sqlite3.connect("census.sqlite") as conn:
    c = conn.cursor()
    c.execute('select firstname, surname from census_data where surname in ?',(l,))

so that I can return all the records for the surnames contained in l.

Answer 1

Question: return all the records for the surnames contained in a tuple

The core is, create a Query with as much bindings - ? - as in the sequence.
The [:-1] is needed to exclude the last comma ...?,.

• SQL As Understood By SQLite - whereclause

  surnames = ("Smith", "Murphy", "Owens")
  bindings = '?,'*len(surnames)
  QUERY = "select firstname, surname from census_data where surname in ({});"
            .format(bindings[:-1])
  print(QUERY)
  # >>> select firstname, surname from census_data where surname in (?,?,?);
  cur.execute (QUERY, surnames)
  

Tested with Python:3.5.3 - sqlite3:2.6.0