Creating sqlquery in python with IN clause

Question

i have file and i am reading the file in list using python. then these needs to be used in sqlquery in the IN clause. File is read using a functionreadCsv & contents are stored in list(file may have more than 10k records at times) and now i have to pass this list to sqlquery in value1 & value2 & so on. any suggestions will be appreciated.

 def readCsv():
        with open(filename, 'r', encoding='latin1') as file:
               csvreader = csv.reader(file)
                for row in csvreader:
                     nlist.append(row)
                       
 if __name__ == '__main__':
       nlist = [] 
       readCsv()

       sqlquery = "select col1,col2 from tablename where col3 in ('value1',,'value2')

Why did you use two commas in `where col3 in ('value1',,'value2')` ? — Luuk, Oct 04 '22 at 17:22

score 0 · Answer 1 · answered Oct 04 '22 at 17:17

You may join the elements in the list to form a string with comma separated values and use that string as a parameter for the execute() command. Note that the parameter has to be a tuple. Something like this may work for you:

values = ','.join(['%s'] * len(nlist))
cursor.execute("SELECT col1,col2 FROM tablename WHERE col3 IN (%s)" % values, tuple(nlist))

Please look at the accepted answers in these questions below as they offer similar working solutions: imploding a list for use in a python MySQLDB IN clause

python list in sql query as parameter

Creating sqlquery in python with IN clause

1 Answers1