使用Python将Excel数据写入Snowflake表时遇pandas依赖缺失错误求助

问题描述 #

已成功将Excel数据读取为pandas DataFrame，Snowflake连接也正常，但调用df.to_sql并使用pd_writer方法写入Snowflake表时，报错：
snowflake.connector.errors.MissingDependencyError: Missing optional dependency: pandas

代码示例 #

import pandas as pd

from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL
from snowflake.connector.pandas_tools import pd_writer
url = URL(
    account = '',
    user = '',
    schema = 'TMP',
    database = 'TMP',
    warehouse= 'DATABRICKS',
    role = '',
    authenticator='externalbrowser',
)

engine = create_engine(url)
con = engine.connect()
df = pd.read_excel("C:\\Final.xlsx")
df.columns = df.columns.astype(str)
table_name = 'test_connect'
if_exists = 'replace'

df.to_sql(name=table_name.lower(), con=con,index= False, if_exists=if_exists, method=pd_writer)

完整报错堆栈 #

Traceback (most recent call last):
  File "C:\Users\XYZ\AppData\Roaming\JetBrains\DataSpell2022.2\scratches\scratch.py", line 32, in <module>
    df.to_sql(name=table_name.lower(), con=con,index= False, if_exists=if_exists, method=pd_writer)
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\pandas\core\generic.py", line 2963, in to_sql
    return sql.to_sql(
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\pandas\io\sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\pandas\io\sql.py", line 1739, in to_sql
    total_inserted = sql_engine.insert_records(
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\pandas\io\sql.py", line 1322, in insert_records
    return table.insert(chunksize=chunksize, method=method)
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\pandas\io\sql.py", line 950, in insert
    num_inserted = exec_insert(conn, keys, chunk_iter)
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\snowflake\connector\pandas_tools.py", line 320, in pd_writer
    df = pandas.DataFrame(data_iter, columns=keys)
  File "C:\Users\XYZ\AppData\Roaming\Python\Python310\site-packages\snowflake\connector\options.py", line 36, in __getattr__
    raise MissingDependencyError(self._dep_name)
snowflake.connector.errors.MissingDependencyError: Missing optional dependency: pandas
Process finished with exit code 1

解决方案 #

1. 安装Snowflake Connector的pandas扩展依赖 #

Snowflake的pandas_tools模块需要单独安装对应的依赖组件，即使本地已安装pandas，也要执行以下命令：

pip install snowflake-connector-python[pandas]

该命令会安装snowflake-connector-python及其与pandas交互所需的全部依赖。

2. 环境一致性检查 #

• 确认执行代码的Python环境和安装依赖的环境为同一个（避免虚拟环境与全局环境混淆）
• 若使用conda环境，改用以下命令确保依赖兼容：

conda install snowflake-connector-python pandas

3. 验证安装（可选） #

执行以下命令查看snowflake-connector-python的安装信息，确认pandas已被列为依赖：

pip show snowflake-connector-python

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