Python脚本执行SQL Server SELECT语句报错求助

问题描述 #

执行Python脚本从Microsoft SQL Server查询数据并写入Excel时，触发以下错误：

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:

错误指向脚本末尾的for row in rs行，原脚本如下：

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)

问题原因及解决方法 #

核心问题1：连接上下文提前关闭 #

with engine.connect() as con:是上下文管理器，代码块执行完毕后会自动关闭数据库连接。而rs是基于该连接的结果集，连接关闭后再遍历rs就会触发HY010函数序列错误。

核心问题2：其他逻辑错误 #

• 使用了未定义的cursor变量，cursor.description会直接抛出NameError
• a.writerows(row)中的row未定义，且writerows需要传入可迭代的行集合，而非单个行
• 文件写入与结果集遍历的顺序逻辑混乱

修正后的脚本 #

from sqlalchemy import create_engine
from sqlalchemy.engine import URL
import csv
import configparser

# 读取配置文件
config = configparser.ConfigParser()
config.read('databaseConfig.ini')
destinationFile = config['destination']['fileName']

# 构建数据库连接
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)

qry = "SELECT TOP (1000) * FROM [AstronomyMaps].[dbo].[starMapA]"

# 在连接上下文内处理结果集
with engine.connect() as con:
    rs = con.execute(qry)
    # 获取查询列名
    column_names = [x[0] for x in rs.cursor.description]
    
    # 写入CSV文件
    with open(destinationFile, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f, delimiter=',')
        writer.writerow(column_names)
        # 遍历结果集，同时打印并写入
        for row in rs:
            print(row)
            writer.writerow(row)

额外优化建议 #

既然已导入pandas，可以用更简洁的方式完成查询与写入操作，无需手动处理CSV逻辑：

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

config = configparser.ConfigParser()
config.read('databaseConfig.ini')
destinationFile = config['destination']['fileName']

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)

qry = "SELECT TOP (1000) * FROM [AstronomyMaps].[dbo].[starMapA]"
# 直接用pandas读取查询结果并写入Excel
df = pd.read_sql(qry, engine)
df.to_excel(destinationFile, index=False)

内容的提问来源于stack exchange，提问作者SkyeBoniwell