使用Dask read_sql_query读取SQL数据报错：'str'对象无limit属性

问题重现 #

尝试用Dask读取SQL Server数据时编写了如下代码：

from dask.dataframe import read_sql_query, read_sql, read_sql_table

username='uid'
password='pid'
database='myDB'
host='sql_server'
port='1433'
driver='ODBC+Driver+17+for+SQL+Server'

conn_str = f"mssql+pyodbc://{username}:{password}@{host}:{port}/{database}?driver={driver}"

ddf = read_sql_query(sql="select * from data", con=conn_str, index_col='id')

运行后触发错误：

File "C:\Program Files\Python38\lib\site-packages\dask\dataframe\io\sql.py", line 118, in read_sql_query
    q = sql.limit(head_rows)
AttributeError: 'str' object has no attribute 'limit'

问题原因 #

旧版本的Dask read_sql_query要求sql参数必须是SQLAlchemy Query/TextClause对象，而非原生字符串SQL。代码中直接传入字符串，导致Dask尝试调用字符串的limit方法，触发属性错误。

解决方案 #

有两种可行的解决方式：

方式1：用SQLAlchemy包装SQL字符串 #

通过SQLAlchemy的text()函数将字符串SQL转换为可识别的对象，同时传入SQLAlchemy引擎：

from dask.dataframe import read_sql_query
from sqlalchemy import create_engine, text

username='uid'
password='pid'
database='myDB'
host='sql_server'
port='1433'
driver='ODBC+Driver+17+for+SQL+Server'

conn_str = f"mssql+pyodbc://{username}:{password}@{host}:{port}/{database}?driver={driver}"
# 创建SQLAlchemy引擎
engine = create_engine(conn_str)
# 用text包装SQL字符串
sql_query = text("select * from data")

ddf = read_sql_query(sql=sql_query, con=engine, index_col='id')

方式2：改用Dask的read_sql函数 #

read_sql是Dask提供的更通用的SQL读取接口，支持直接传入字符串SQL，无需额外包装：

from dask.dataframe import read_sql

username='uid'
password='pid'
database='myDB'
host='sql_server'
port='1433'
driver='ODBC+Driver+17+for+SQL+Server'

conn_str = f"mssql+pyodbc://{username}:{password}@{host}:{port}/{database}?driver={driver}"

ddf = read_sql(sql="select * from data", con=conn_str, index_col='id')

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