CODEWITHSUNDEEP
pythonsql
Yousef Bahar
Yousef Bahar

Reputation: 13

Python PYODBC - Previous SQL was not a query

I have the following python code, it reads through a text file line by line and takes characters x to y of each line as the variable "Contract".

import os
import pyodbc

cnxn = pyodbc.connect(r'DRIVER={SQL Server};CENSORED;Trusted_Connection=yes;')
cursor = cnxn.cursor()


claimsfile = open('claims.txt','r')

for line in claimsfile:
    #ldata = claimsfile.readline()
    contract = line[18:26]
    print(contract)
    cursor.execute("USE calms SELECT XREF_PLAN_CODE FROM     calms_schema.APP_QUOTE WHERE APPLICATION_ID = "+str(contract))
print(cursor.fetchall())

When including the line cursor.fetchall(), the following error is returned:

Programming Error: Previous SQL was not a query.

The query runs in SSMS and replace str(contract) with the actual value of the variable results will be returned as expected.

Based on the data, the query will return one value as a result formatted as NVARCHAR(4).

Most other examples have variables declared prior to the loop and the proposed solution is to set NO COUNT on, this does not apply to my problem so I am slightly lost.

P.S. I have also put the query in its own standalone file without the loop to iterate through the file in case this was causing the problem without success.

Upvotes: 1

Views: 5245

Answers (1)

Parfait
Parfait

Reputation: 107587

In your SQL query, you are actually making two commands: USE and SELECT and the cursor is not set up with multiple statements. Plus, with database connections, you should be selecting the database schema in the connection string (i.e., DATABASE argument), so TSQL's USE is not needed.

Consider the following adjustment with parameterization where APPLICATION_ID is assumed to be integer type. Add credentials as needed:

constr = 'DRIVER={SQL Server};SERVER=CENSORED;Trusted_Connection=yes;' \
         'DATABASE=calms;UID=username;PWD=password'
cnxn = pyodbc.connect(constr)
cur = cnxn.cursor()

with open('claims.txt','r') as f:
    for line in f:
        contract = line[18:26]
        print(contract)

        # EXECUTE QUERY
        cur.execute("SELECT XREF_PLAN_CODE FROM APP_QUOTE WHERE APPLICATION_ID = ?", 
                    [int(contract)])
        # FETCH ROWS ITERATIVELY
        for row in cur.fetchall():
            print(row)

cur.close()
cnxn.close()

Upvotes: 2

Related Questions