CODEWITHSUNDEEP
pythonsqlpyodbc
Shaun Parker
Shaun Parker

Reputation: 44

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

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

Upvotes: 1

Views: 3581

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

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 ...")

Upvotes: 2

Related Questions