如何通过pyodbc从Snowflake ODBC获取带表头的表格格式数据

方法1：转换为Pandas DataFrame（推荐） #

pyodbc的游标没有fetch_pandas_all()方法，但可以通过cursor.description提取列名，结合fetchall()获取全量数据，手动构造带表头的DataFrame：

import pyodbc
import pandas as pd

con = pyodbc.connect('DSN=Snowflake Conn')
con.setencoding(encoding='utf-8')
con.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cs = con.cursor()

# 注意：后续建议改用参数化查询避免SQL注入，先保留原查询结构
query = "select mr.entity_code as FUNDCODE, mr.AS_OF_DATE, mr.vol_contr_pc_abs * 3.549 EWMA_Vol_Annualised from risksd.tbl_market_risk mr join risksd.tbl_control_funds cf on cf.fundcode = mr.entity_code where model_code = 'GBL' and record_type = 'Portfolio' and mr.as_of_date ='"+last_business_day_string+"' and cf.fundcode in "+str(fundslist)
cs.execute(query)

# 提取列名：cursor.description里每个元素的第一个值就是列名
column_names = [col[0] for col in cs.description]
# 获取所有数据行
all_data = cs.fetchall()
# 构造DataFrame
result_df = pd.DataFrame(all_data, columns=column_names)

# 查看结果
print(result_df)

方法2：打印带表头的纯文本表格 #

如果不需要DataFrame，只是要直观的表格打印效果，可以用tabulate库（先执行pip install tabulate安装）：

import pyodbc
from tabulate import tabulate

con = pyodbc.connect('DSN=Snowflake Conn')
con.setencoding(encoding='utf-8')
con.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cs = con.cursor()

query = "select mr.entity_code as FUNDCODE, mr.AS_OF_DATE, mr.vol_contr_pc_abs * 3.549 EWMA_Vol_Annualised from risksd.tbl_market_risk mr join risksd.tbl_control_funds cf on cf.fundcode = mr.entity_code where model_code = 'GBL' and record_type = 'Portfolio' and mr.as_of_date ='"+last_business_day_string+"' and cf.fundcode in "+str(fundslist)
cs.execute(query)

column_names = [col[0] for col in cs.description]
all_data = cs.fetchall()

# 打印带表头的网格状表格
print(tabulate(all_data, headers=column_names, tablefmt="grid"))

关键优化：避免SQL注入 #

原代码直接拼接字符串到SQL查询，存在安全风险。改用参数化查询更稳妥：

# 假设fundslist是字符串列表，比如['FUND001', 'FUND002']
# 生成对应数量的占位符
placeholders = ','.join(['?'] * len(fundslist))
# 用占位符替换拼接的部分
query = """
select mr.entity_code as FUNDCODE, mr.AS_OF_DATE, mr.vol_contr_pc_abs * 3.549 EWMA_Vol_Annualised 
from risksd.tbl_market_risk mr 
join risksd.tbl_control_funds cf on cf.fundcode = mr.entity_code 
where model_code = 'GBL' 
and record_type = 'Portfolio' 
and mr.as_of_date = ? 
and cf.fundcode in ({})
""".format(placeholders)

# 执行时传入参数：日期 + 基金列表转成的元组
cs.execute(query, (last_business_day_string,) + tuple(fundslist))

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