Python script can't execute SQL Select statement

Question

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)

Gord Thompson · Accepted Answer · 2022-08-09 23:25:54Z

2

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()

edited Aug 9, 2022 at 23:25

answered Aug 9, 2022 at 18:02

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python script can't execute SQL Select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related