Python & SQL via pyodbc - executing queries via loop with dynamic where clause

Question

I'm trying to generate & execute SQL statements via pyodbc. I expect multiple SQL statements, all of which start with the same SELECT & FROM but have a different value in the WHERE. The value in my WHERE clause is derived from looping through a table - each distinct value the SQL script finds in the table, I need Python to generate another SQL statement with this value as the WHERE clause.

I'm almost there with this, I'm just struggling to get pyodbc to put my query strings in formats that SQL likes. My code so far:

import pyodbc

cn = pyodbc.connect(connection info)

cursor = cn.cursor()

result = cursor.execute('SELECT distinct searchterm_name FROM table1')

for row in result:
    sql = str("SELECT * from table2 WHERE table1.searchterm_name = {c}".format(c=row)),

#print sql

This code generates an output like this, where "name here" is based on the value found in table1.

('SELECT * from ifb_person WHERE searchterm_name = (u\'name here\', )',)

I just need to remove all the crap surrounding the query & where clause so it looks like this. Then I can pass it into another cursor.execute()

SELECT * from ifb_person WHERE searchterm_name = 'name here'

EDIT

for row in result:
    cursor.execute("insert into test (searchterm_name) SELECT searchterm_name FROM ifb_person WHERE searchterm_name = ?",
               (row[0],))

This query fails with the error pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Basically what I am trying to do is get Python to generate a fresh SQL statement for every result it finds in table1. The second query is running searches against the table ifb_person and inserting the results to a table "test". I want to run separate SQL statements for every result found in table1

How about doing this properly with parameterization? cursor.execute("SELECT * FROM ifb_person WHERE searchterm_name = ?", (row[0],)). Don't build strings with format to query a database. That, as probably every guide you can read about this topic will say in bold, leaves you open to sql injection. — roganjosh
Your results are also being returned as tuples, which is why you need to use the [0] index on row. Currently you're converting the tuple to its string representation. My comment should work; the production/testing debate here is a bit irrelevant since it's less characters to type do it properly from the start. — roganjosh
I can't properly see your edited comment but it looks like you're now trying to combine queries, which wasn't the approach in your question. I have no idea what that query is supposed to do, or why you now just have [0] on it's own, not row[0] — roganjosh
@roganjosh sorry hadn't realised it wasn't clear - I want the results of the first query to dynamically create (and execute) sql queries based on it's output — Shaun Parker
That's fine, it should be in your question anyway. Now the query looks like my old history essays; an exercise in tautology. cursor.execute("INSERT INTO test VALUES (?)", (row[0],)) or something very similar (I can't test atm). The rest of the query just goes round in circles. — roganjosh

Gord Thompson Gord Thompson · Accepted Answer · 2017-10-18T14:59:52

pyodbc allows us to iterate over a Cursor object to return the rows, during which time the Cursor object is still "in use", so we cannot use the same Cursor object to perform other operations. For example, this code will fail:

crsr = cnxn.cursor()
result = crsr.execute("SELECT ...")  # result is just a reference to the crsr object
for row in result:
    # we are actually iterating over the crsr object
    crsr.execute("INSERT ...")  # this clobbers the previous crsr object ...
    # ... so the next iteration of the for loop fails with " Previous SQL was not a query."

We can work around that by using fetchall() to retrieve all the rows into result ...

result = crsr.execute("SELECT ...").fetchall()  
# result is now a list of pyodbc.Row objects and the crsr object is no longer "in use"

... or use a different Cursor object in the loop

crsr_select = cnxn.cursor()
crsr_insert = cnxn.cursor()
crsr_select.execute("SELECT ...")
for row in crsr_select:
    crsr_insert.execute("INSERT ...")

Python & SQL via pyodbc - executing queries via loop with dynamic where clause

1 Answers