Python script can't execute SQL Select statement-CodePudding

I'm trying to execute a SQL SELECT statment from Microsoft SQL Server and write that data to an excel spreadsheet.

However, whenever I execute the Python script, I am getting this error:

Traceback (most recent call last):
  File "C:\Users\rrw\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\cursor.py", line 955, in fetchone
    row = dbapi_cursor.fetchone()
pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC Driver 17 for SQL Server]Function sequence error (0) (SQLFetch)')     

Traceback (most recent call last):
  File "F:\Astro\Python\AstroPy\WriteSQLData.py", line 91, in <module>
    for row in rs:

I can see from the error, that it doesn't like the line: for row in rs at the very end of the script. But I can't figure out why.

Is there anything I am missing?

Here is my script:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL
import pyodbc
import pandas as pd
import csv
import configparser

# Get data from configuration ini file
config = configparser.ConfigParser()
config.read('databaseConfig.ini')
destinationFile = config['destination']['fileName']

# Database Connection Code
connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=ASTROLAB;DATABASE=AstronomyMaps;UID=xyz;PWD=xyz"
connection_url = URL.create(
    "mssql pyodbc", query={"odbc_connect": connection_string})

engine = create_engine(connection_url)

# Simple test query
qry = "SELECT TOP (1000) * FROM [AstronomyMaps].[dbo].[starMapA]"
with engine.connect() as con:
    rs = con.execute(qry)
    
# Write query data to Excel spreadsheet 
with open(destinationFile, 'w', newline='') as f:
    a = csv.writer(f, delimiter=',')
    a.writerow([x[0] for x in cursor.description])
    a.writerows(row)

    for row in rs:
        print(row)

CodePudding user response：

When you exit the context manager (with block) the statement has been executed but the rows haven't been retrieved yet. However, exiting the context manager also "terminates" the connection so for row in rs: throws an error when the ODBC driver tries to call SQLFetch.

You can avoid the error by using .all() to retrieve the rows while still in the with block:

qry = "SELECT TOP (1000) * FROM [AstronomyMaps].[dbo].[starMapA]"
with engine.connect() as con:
    rs = con.exec_driver_sql(qry).all()