#!/usr/bin/env python
# encoding: utf-8
import pyodbc
import pandas as pd
import numpy as np
sql="""
SELECT 产品线,终端,品规,销售模式
,cast(year([日期1]) as varchar(50))+right('00'+cast(month([日期1]) as nvarchar(50)),2)'年月'
,sum([医院入库数量])'数量'
,sum(iif([销售模式]='自营',[商业发货单价]*[医院入库数量]/10000,[底价]*[医院入库数量]/10000))'价格'
FROM [商务部流向明细表] where [销售模式]!='自营' or [销售模式]!='小包' or [销售模式]!='中包'
group by 产品线,终端,品规,year(日期1),month(日期1),销售模式
"""
conn = pyodbc.connect \
('DRIVER={SQL Server Native Client 11.0};SERVER=APSOOZRK6V8FK4X\MSSQLSERVER12;DATABASE=流向;UID=sa;PWD=abcd2012')
df = pd.read_sql(sql,con=conn)
conn.close()