python list in sql query as parameter

Question

I have a python list  say l  l    1 5 8    I want to write a sql query to get the data for all the elements of the list  say  select name from students where id    IN THE LIST l    How do I accomplish this

User · Accepted Answer

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.

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)

User · Answer

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

User · Answer

This Will Work If Number of  Values in List equals to 1 or greater than 1 t   str tuple l   if t -2             t  t replace t -2   quot  quot   query    quot select name from studens where id IN    quot  format t

User · Answer

The SQL you want is  select name from studens where id in  1  5  8    If you want to construct this from the python you could use  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   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  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

User · Answer

If you re using PostgreSQL with the Psycopg2 library you can let its tuple adaption do all the escaping and string interpolation for you  e g   ids    1 2 3  cur execute     SELECT   FROM foo WHERE id IN  s      tuple ids      i e  just make sure that you re passing the IN parameter as a tuple   if it s a list you can use the   ANY array syntax   cur execute     SELECT   FROM foo WHERE id   ANY   s       list ids      note that these both will get turned into the same query plan so you should just use whichever is easier  e g  if your list comes in a tuple use the former  if they re stored in a list use the latter

User · Answer

This Will Work If Number of  Values in List equals to 1 or greater than 1 t   str tuple l   if t -2             t  t replace t -2   quot  quot   query    quot select name from studens where id IN    quot  format t

User · Answer

This uses parameter substitution and takes care of the single value list case   l    1 5 8   get operator   lambda x      if len x     1 else  IN  get value   lambda x  int x 0   if len x     1 else x  query    SELECT   FROM table where id     get operator l       s   cursor execute query   get value l

User · Answer

l    1    or  1 2 3   query    SELECT   FROM table WHERE id IN  l  params     l    tuple l   cursor execute query  params    The  var notation seems simpler   Python 3 7

User · Answer

placeholders       join      str i       for i in range len l    query  select name from students where id   s   placeholders query query format  l  cursor execute query    This should solve your problem

User · Answer

l    1    or  1 2 3   query    SELECT   FROM table WHERE id IN  l  params     l    tuple l   cursor execute query  params    The  var notation seems simpler   Python 3 7

User · Answer

Easiest way is to turn the list to tuple first  t   tuple l  query    select name from studens where id IN     format t

User · Answer

I like bobince s answer   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   placeholders       join  placeholder  len l

User · Answer

I like bobince s answer   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   placeholders       join  placeholder  len l

User · Answer

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

User · Answer

Solution for  umounted answer  because that broke with a one-element tuple  since  1   is not valid SQL     gt  gt  gt  random ids    1234 123 54 56 57 58 78 91   gt  gt  gt  cursor execute  create table test  id     gt  gt  gt  for item in random ids      cursor execute  insert into test values   d     item   gt  gt  gt  sublist    56 57 58   gt  gt  gt  cursor execute  select id from test where id in  s    str tuple sublist   replace             gt  gt  gt  a   cursor fetchall    gt  gt  gt  a   56     57     58      Other solution for sql string   cursor execute  select id from test where id in   s                 join l

User · Answer

Solution for  umounted answer  because that broke with a one-element tuple  since  1   is not valid SQL     gt  gt  gt  random ids    1234 123 54 56 57 58 78 91   gt  gt  gt  cursor execute  create table test  id     gt  gt  gt  for item in random ids      cursor execute  insert into test values   d     item   gt  gt  gt  sublist    56 57 58   gt  gt  gt  cursor execute  select id from test where id in  s    str tuple sublist   replace             gt  gt  gt  a   cursor fetchall    gt  gt  gt  a   56     57     58      Other solution for sql string   cursor execute  select id from test where id in   s                 join l

User · Answer

string join the list values separated by commas  and use the format operator to form a query string   myquery    select name from studens where id in   s         join map str mylist      Thanks  blair-conrad

User · Answer

string join the list values separated by commas  and use the format operator to form a query string   myquery    select name from studens where id in   s         join map str mylist      Thanks  blair-conrad

User · Answer

Just use inline if operation with tuple function  query    quot Select   from hr employee WHERE id in  quot    tuple employee ids  if len employee ids     1 else  quot   quot   str employee ids 0      quot   quot

User · Answer

This uses parameter substitution and takes care of the single value list case   l    1 5 8   get operator   lambda x      if len x     1 else  IN  get value   lambda x  int x 0   if len x     1 else x  query    SELECT   FROM table where id     get operator l       s   cursor execute query   get value l

User · Answer

placeholders       join      str i       for i in range len l    query  select name from students where id   s   placeholders query query format  l  cursor execute query    This should solve your problem

User · Answer

Just use inline if operation with tuple function  query    quot Select   from hr employee WHERE id in  quot    tuple employee ids  if len employee ids     1 else  quot   quot   str employee ids 0      quot   quot

User · Answer

a simpler solution   lst    1 2 3 a b c   query   f   SELECT   FROM table WHERE IN  str lst  1 -1

User · Answer

string join the list values separated by commas  and use the format operator to form a query string   myquery    select name from studens where id in   s         join map str mylist      Thanks  blair-conrad

User · Answer

The SQL you want is  select name from studens where id in  1  5  8    If you want to construct this from the python you could use  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   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  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

User · Answer

For example  if you want the sql query   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

User · Answer

To run a select from where field is in list of strings  instead of int   as per this question use repr tuple map str  l     Full example  l     a   b   c   sql   f     select name  from students  where id in  repr tuple map str  l         print sql   Returns   select name  from students  where id in   a    b    c   For a list of dates in Oracle  this worked dates str       join  f DATE  repr s    for s in   2020-11-24    2020-12-28     dates str   f   dates str     sql cmd   f    select   from students  where  and date in  dates str       Returns  select   from students  where  and date in  DATE  2020-11-24  DATE  2020-12-28

User · Answer

The SQL you want is  select name from studens where id in  1  5  8    If you want to construct this from the python you could use  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   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  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

User · Answer

For example  if you want the sql query   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

User · Answer

a simpler solution   lst    1 2 3 a b c   query   f   SELECT   FROM table WHERE IN  str lst  1 -1

User · Answer

The SQL you want is  select name from studens where id in  1  5  8    If you want to construct this from the python you could use  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   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  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

User · Answer

string join the list values separated by commas  and use the format operator to form a query string   myquery    select name from studens where id in   s         join map str mylist      Thanks  blair-conrad

User · Answer

To run a select from where field is in list of strings  instead of int   as per this question use repr tuple map str  l     Full example  l     a   b   c   sql   f     select name  from students  where id in  repr tuple map str  l         print sql   Returns   select name  from students  where id in   a    b    c   For a list of dates in Oracle  this worked dates str       join  f DATE  repr s    for s in   2020-11-24    2020-12-28     dates str   f   dates str     sql cmd   f    select   from students  where  and date in  dates str       Returns  select   from students  where  and date in  DATE  2020-11-24  DATE  2020-12-28

User · Answer

Easiest way is to turn the list to tuple first  t   tuple l  query    select name from studens where id IN     format t

User · Answer

If you re using PostgreSQL with the Psycopg2 library you can let its tuple adaption do all the escaping and string interpolation for you  e g   ids    1 2 3  cur execute     SELECT   FROM foo WHERE id IN  s      tuple ids      i e  just make sure that you re passing the IN parameter as a tuple   if it s a list you can use the   ANY array syntax   cur execute     SELECT   FROM foo WHERE id   ANY   s       list ids      note that these both will get turned into the same query plan so you should just use whichever is easier  e g  if your list comes in a tuple use the former  if they re stored in a list use the latter

[python] python list in sql query as parameter

Examples related to python

Examples related to sql