sql - 使用雪花连接器在雪花中通过 python 执行 SQL 查询时，列名称为 0、1、2、3

Question

我正在从 python 脚本执行 sql 查询以从 Windows 10 中的雪花检索数据，但生成的查询缺少列名，并且它被 0、1、2、3 等替换。在雪花界面中执行查询并下载 csv 时，会给出文件中的列。我在查询中将列名作为别名传递

代码如下

def _CONSUMPTION(con):

data2 = con.cursor().execute("""select sd.sales_force_lvl_1_code "Plan-To Code",sd.sales_force_lvl_1_desc "Plan-To Description",pd.matl_code "Product Code",pd.matl_desc "Product Description",pd.ean_upc_code "UPC",dd.fiscal_week_desc "Fiscal Week Description",f.unit_sales_qty "Sales Units",f.incr_units_qty "Incremental Units"
                                    from DW.consumption_fact1 f, DW.market_dim md, DW.matl_dim pd, DW.fiscal_week_dim dd, (select sales_force_lvl_1_code,max(sales_force_lvl_1_desc) sales_force_lvl_1_desc from DW.mv_us_sales_force_dim group by sales_force_lvl_1_code) sd 
                                    where dd.fiscal_week_key = f.fiscal_week_key 
                                    and pd.matl_key = f.matl_key 
                                    and md.market_key = f.market_key 
                                    and sd.sales_force_lvl_1_code = md.curr_sales_force_lvl_1_code 
                                    and dd.fiscal_week_key between (select curr_fy_week_key-6 from DW.curr_date_lkp) and (select curr_fy_week_key-1 from DW.curr_date_lkp)
                                    and f.company_key = 6006 
                                    and (f.unit_sales_qty <> 0 and f.sales_amt <> 0) 
                                    and md.curr_sales_force_lvl_1_code is not null
                                    UNION
                                    select '5000016240' "Plan-To Code", 'AWG TOTAL' "Plan-To Description",pd.matl_code "Product Code",pd.matl_desc "Product Description",pd.ean_upc_code "UPC",dd.fiscal_week_desc "Fiscal Week Description",f.unit_sales_qty "Sales Units",f.incr_units_qty "Incremental Units"
                                    from DW.consumption_fact1 f, DW.market_dim md, DW.matl_dim pd, DW.fiscal_week_dim dd 
                                    where dd.fiscal_week_key = f.fiscal_week_key 
                                    and pd.matl_key = f.matl_key 
                                    and md.market_key = f.market_key 
                                    and dd.fiscal_week_key between (select curr_fy_week_key-6 from DW.curr_date_lkp) and (select curr_fy_week_key-1 from DW.curr_date_lkp)
                                    and f.company_key = 6006 
                                    and (f.unit_sales_qty <> 0 and f.sales_amt <> 0) 
                                    and md.market_code = '20267'""").fetchall()
                            
df = pd.DataFrame(data2)
df.head(5)
df.to_csv('CONSUMPTION.csv',index = False)

Answer 1

[查看文档]，似乎最简单的方法是使用游标方法 .fetch_pandas_all():

query = "SELECT 1 a, 2 b, 'a' c UNION ALL SELECT 7,4,'snow'"
cur = connection.cursor()
cur.execute(query).fetch_pandas_all()

或者，如果您想将结果转储到 CSV 中，只需按照问题中的方式进行:

query = "SELECT 1 a, 2 b, 'a' c UNION ALL SELECT 7,4,'snow'"
cur = connection.cursor()
df = cur.execute(query).fetch_pandas_all()
df.to_csv('x.csv', index = False)

可视化: