Loop through SQL queries in a text file and execute - pyodbc

Question

I have a pipe delimited text file with SQL queries. I am trying to loop through and execute each query. When I print the queries in the file in the loop, everything seems fine. If I copy the text and run in SSMS, the SQL runs fine, but when I try to execute with pyodbc, I get the error:

ProgrammingError: No results.  Previous SQL was not a query.

Here is my code:

with open('C:/users/a/tria_file_by_ASLOB12.csv', 'r') as queryFile:
    for line in queryFile:
        fields = line.split('|')
        print fields[0]
        #cursor.execute(fields[0])
        #cursor.fetchall()

Returns:

SELECT COUNT(DISTINCT(pol_num)) In_Force_Count, sum(lmt_pol_s) Exposure

          FROM bapu.dbo.fact_prem 

          WHERE aslob = 90

          and CONCAT(2016,1231) between CAST(d_pol_eff AS DATE) and CAST(d_pol_exp AS DATE)

          and cvg_state = 'WA'

          and rpt_co_name in (SELECT Ent_Name FROM NAIC_Legal_Ent_Lookup WHERE ID = 40045)


SELECT COUNT(DISTINCT(pol_num)) In_Force_Count, sum(lmt_pol_s) Exposure

          FROM bapu.dbo.fact_prem 

          WHERE aslob = 90

          and CONCAT(2016,1231) between CAST(d_pol_eff AS DATE) and CAST(d_pol_exp AS DATE)

          and cvg_state = 'WI'

          and rpt_co_name in (SELECT Ent_Name FROM NAIC_Legal_Ent_Lookup WHERE ID = 40045)

etc

What am I missing?

Thanks

I just performed a search using the criteria `pyodbc execute multiple statements`. The first result returned was http://stackoverflow.com/questions/38856534/execute-sql-file-with-multiple-statements-separated-by-using-pyodbc. I hope it or one of the others helps. — toonice, May 08 '17 at 01:28

score 0 · Accepted Answer · answered May 11 '17 at 13:55

I figured it out. I needed to execute the query, then loop through the results of cursor.fetchall(). Here is my code:

row_iterator = df.iterrows()
_, last = row_iterator.next()  # take first item from row_iterator
resultFile = open('C:\\Users\\pathtofile\\file.csv', 'w')

for i, row in row_iterator:
    row1 = (int(row['LEGAL_ENTITY']), int(row['CY_YE']), 
int(row['TRIP_ASLOB']), row['CVG_ST'], int(row['Row']))
    row2 = (last['LEGAL_ENTITY'], last['CY_YE'], last['TRIP_ASLOB'], 
last['CVG_ST'], int(row['Row']))
    last = row
    data = row1
    query= """SELECT COUNT(DISTINCT(pol_num)) In_Force_Count, 
sum(distinct(lmt_pol_s)) Exposure
              FROM bapu.dbo.xxx
              WHERE aslob = {}
              and CONCAT({},1231) between CAST(d_pol_eff AS DATE) and 
CAST(d_pol_exp AS DATE)
              and cvg_state = '{}'
              and rpt_co_name in (SELECT Ent_Name FROM NAIC_Legal_Ent_Lookup 
WHERE ID = {})
              and (cvg_desc = 'tria' or cvg_desc like '%terrorism%')
              """.format(data[2],data[1],data[3], data[0])
    cursor.execute(query)
    for row in cursor.fetchall():
        resultFile.write("{} {} {} {} {} {} {} \n".format(row[0], row[1], 
data[2], data[1], data[3], data[0], data[4]))
        print row[0], row[1], data[2], data[1], data[3], data[0], data[4]

Loop through SQL queries in a text file and execute - pyodbc

1 Answers1