2

I'm trying to pass a an unknown number of params to SQL Server in python 3.6. Here is my code using pypyodbc:

cursor = cnxn.cursor()
theargs= ['1033286869','1053474957','1063654630','1104116235','1104910306','JASON']

thesql = """SELECT * 
            FROM BI_DUPLICATES_STAGE_0 
            WHERE DUP_ID IN (?, ?, ?, ?, ?)
            AND FRST_NM = ?
         """
cursor.execute(thesql, theargs)
resultset = cursor.fetchall()

This works. However, I'm never sure how many DUP_IDs I'm going to have. I've seen some examples of using a list, but then if I have another variable such as is this case, FRST_NM, then I can't just use the list.

So if somebody could provide a simple example of having a list (of varying length), and another variable and how to get SQL to run that I would greatly appreciate it! I'm not bound to using pypyodbc if that is the issue and there is another way to do this.

UPDATE: Thank you for the nicely formatted answer. Just one quick follow-up question. I'm still a little unclear how I would format 2 lists if I had to produce something like: SELECT * FROM BI_DUPLICATES_STAGE_0 WHERE DUP_ID IN (1033286869, 1053474957, 1063654630, 1104116235, 1104910306) AND MID_NM IN ('SMITH','JON') AND FRST_NM = 'JASON'

Without the nice format join, I have some mess like this: SELECT * FROM BI_DUPLICATES_STAGE_0 WHERE DUP_ID IN (?,?,?,?,?) AND MID_NM IN (?,?) AND FRST_NM = ?

How would I format the list twice as suggested? Keeping in mind I won't know how long each list (the ID and MID_NM) are actually going to be.

sniperd
  • 5,124
  • 6
  • 28
  • 44
  • *I have some mess like this*...why is that query a mess? That is a prepared statement for a parameterized query. – Parfait Feb 22 '17 at 16:27

1 Answers1

3

Consider formatting the string to dynamically build ? placeholders by length of lists, theargs and otherargs. Do note the last name should be at end of param list to correspond to last ? placedholder. Once done building the prepared statement, pass to cursor.execute() with param values:

theargs= ['1033286869','1053474957','1063654630','1104116235','1104910306']
otherargs = ['SMITH','JON']
lastarg = ['JASON']
allargs = theargs + otherargs + lastarg

thesql = """SELECT * 
            FROM BI_DUPLICATES_STAGE_0 
            WHERE DUP_ID IN ({})
            AND MID_NM IN ({})
            AND FRST_NM = ?
         """.format(",".join(['?' for i in range(len(theargs))]),
                    ",".join(['?' for i in range(len(otherargs))]))    
print(thesql)

# SELECT * 
#             FROM BI_DUPLICATES_STAGE_0 
#             WHERE DUP_ID IN (?,?,?,?,?)
#             AND MID_NM IN (?,?)
#             AND FRST_NM = ?

cursor.execute(thesql, allargs)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Interesting! If my WHERE clause was something like: WHERE id IN (1,2,3) AND othervalue IN ('a','b','c') AND name = 'Bob' would there be a clever way to handle multiple lists of unknown lengths that I'm not seeing? I'm just surprised there isn't a simple IN (@list) kind of parameter way to pass stuff :) – sniperd Feb 21 '17 at 21:58
  • You would need to format twice for both lists. SQL is declarative language. So as soon as a table, column, variable, even clauses like `WHERE` or `GROUP BY` or `JOIN` is declared it is immutable. Hence, it cannot dynamically change per conditions. Python can dynamically build the string but once built, SQL processes in one call. – Parfait Feb 22 '17 at 00:32
  • Thank you very much for the answer. I've updated the question with a small follow-up. I'm a little unclear how I would format twice for both lists :) – sniperd Feb 22 '17 at 13:54
  • 1
    See update. Simply extend [`str.format()`](https://docs.python.org/3/library/stdtypes.html#str.format) with another `.join` string. Also note how the `FRST_NM` param should be placed at the end. By the way, hopefully your lists do not extend to thousands as various databases have limits of values in `IN()` clause. Consider using temp tables for such large lists. – Parfait Feb 22 '17 at 16:39