将Python DataFrame数据导入SQL Server表的可行方案求助

问题背景 #

已通过Alpha Vantage API获取微软（MSFT）的日度行情数据（存储在msft_data DataFrame中），需要将该数据写入SQL Server数据库，但尝试多种方法后报错，寻求可行代码方案。

msft_data数据结构示例：

1. open  2. high    3. low  4. close    5. volume
date                                                        
2024-01-19   395.76  398.6700  393.5000    398.67  29331136.0
2024-01-18   391.72  393.9900  390.1200    393.87  23392068.0
2024-01-17   387.98  390.1100  384.8100    389.47  22234108.0
...             ...       ...       ...       ...         ...

可行代码方案 #

以下是经过验证的完整代码，针对新手优化，简化操作流程：

import pandas as pd
from alpha_vantage.timeseries import TimeSeries
from sqlalchemy import create_engine

# 1. 获取Alpha Vantage数据
my_api_key = "#####"  # 替换为你的API密钥
ts = TimeSeries(my_api_key, output_format='pandas')
msft_data, meta_data = ts.get_daily(symbol="MSFT", outputsize="compact")

# 2. 预处理DataFrame，适配SQL要求
# 将日期索引转为普通列
msft_data = msft_data.reset_index()
# 重命名列名，移除特殊字符（SQL列名不建议包含空格、点号）
msft_data.rename(columns={
    'date': 'trade_date',
    '1. open': 'open_price',
    '2. high': 'high_price',
    '3. low': 'low_price',
    '4. close': 'close_price',
    '5. volume': 'trade_volume'
}, inplace=True)

# 3. 创建SQL Server连接引擎（SQLAlchemy方式）
# 连接字符串格式：mssql+pyodbc://<服务器地址>/<数据库名>?driver=<驱动>&trusted_connection=yes
engine = create_engine(
    "mssql+pyodbc://DESKTOP-MLSEKPL\\SQLEXPRESS/tracker?driver=SQL+Server&trusted_connection=yes"
)

# 4. 将数据写入SQL Server
# if_exists参数：'replace'=覆盖现有表，'append'=追加数据，'fail'=表存在则报错
msft_data.to_sql(
    name='msft_daily_prices',  # 要创建的表名
    con=engine,
    if_exists='replace',
    index=False,  # 不将DataFrame索引写入SQL
    chunksize=1000  # 大数据时分批写入，避免内存问题
)

print("数据已成功写入SQL Server！")

关键步骤说明 #

• DataFrame预处理：
  • 原数据的日期是索引，通过reset_index()转为普通列，避免写入SQL时丢失日期信息
  • 重命名列名，移除原列名中的数字、点号和空格，符合SQL列名命名规范，减少报错可能
• 使用SQLAlchemy引擎：
  相比直接用pyodbc操作游标，SQLAlchemy配合pandas的to_sql()方法会自动处理表结构创建、数据类型映射，无需手动编写CREATE TABLE语句，更适合新手
• 写入参数说明：
  • name：指定要创建的SQL表名
  • if_exists：根据需求选择覆盖、追加或报错模式
  • index=False：禁止将DataFrame的索引写入SQL，避免生成多余列

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