使用Pandas读取Snowflake表时遇AttributeError错误求助

问题场景 #

尝试通过Python（Pandas）读取Snowflake表数据，执行代码如下：

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
import pandas as pd
url = URL(
    user="MY_SERVICE_ACCOUNT",
    password="MY_SECRET",
    account="my_account",
    warehouse="MY_WAREHOUSE",
    database="MY_DATABASE",
    schema="MY_SCHEMA",
    )

engine = create_engine(url)
connection = engine.connect()

df = pd.read_sql_query('select * from my_table', connection)

报错信息 #

运行后触发错误：

AttributeError: 'Engine' object has no attribute 'cursor'

环境配置 #

虚拟环境通过以下命令搭建：

$ pip install --upgrade snowflake-sqlalchemy
$ pip install pandas

解决方法 #

方法1：直接传入SQLAlchemy Engine对象 #

Pandas的read_sql_query可直接接受SQLAlchemy Engine作为数据源，无需手动创建连接对象。修改代码如下：

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
import pandas as pd
url = URL(
    user="MY_SERVICE_ACCOUNT",
    password="MY_SECRET",
    account="my_account",
    warehouse="MY_WAREHOUSE",
    database="MY_DATABASE",
    schema="MY_SCHEMA",
    )

engine = create_engine(url)
# 直接传入engine，无需手动调用engine.connect()
df = pd.read_sql_query('select * from my_table', engine)

方法2：检查版本兼容性 #

该错误可能源于snowflake-sqlalchemy与pandas的版本不兼容，可指定安装经过验证的兼容版本：

pip install snowflake-sqlalchemy==1.4.5 pandas==2.1.4

方法3：改用snowflake-connector-python直接连接 #

绕过SQLAlchemy，直接使用Snowflake官方连接器：

import snowflake.connector
import pandas as pd

# 直接创建Snowflake连接
conn = snowflake.connector.connect(
    user="MY_SERVICE_ACCOUNT",
    password="MY_SECRET",
    account="my_account",
    warehouse="MY_WAREHOUSE",
    database="MY_DATABASE",
    schema="MY_SCHEMA"
)

df = pd.read_sql_query('select * from my_table', conn)
# 用完记得关闭连接
conn.close()

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