sqlalchemyを使ってテーブル検索して辞書を作成する

DBのテーブルをストアド実行で検索して、辞書を作成する

　例えばSQLServerに任意のテーブルの複数レコードを横に展開した1レコードを返すストアドプロシージャを用意しておいて、それをSQLAlchemyでストアドを実行し、その実行結果(vle)をfetchone()で取得するとともに、そのデータの列名(clm)をkeys()で取得。それら２つをzip関数でlist化して、それをdict辞書にするなどしておけば、その後の工程でelementTreeを使ってXMLファイル作成などの処理が展開可能となる。

make_dict.py

    import sqlalchemy

    ym = 201603

    CONNECT_INFO = 'mssql+pyodbc://hogehoge' #hogehogeは任意のdns(odbc接続)
    engine = sqlalchemy.create_engine(CONNECT_INFO, encoding='utf-8')

    # セッション生成
    Session = sqlalchemy.orm.sessionmaker(bind=engine)
    session = Session()
    
    #ストアドプロシージャ実行 
    query = 'EXEC dbo.sp_rtrv4XML @prd = {0}'.format(ym)
    #vle = session.execute(query).fetchall() #実行結果取得:複数結果が返ってくる場合 
    #print( vle[0][0] )

    vle = session.execute(query).fetchone() #実行結果取得:１件返ってくる
    clm = session.execute(query).keys()  #列名取得

    dc = dict(zip(clm , vle)) #辞書作成
    print(dc)

    # セッション後始末
    #session.commit()
    session.close()

注）接続するDBはSQLServerを想定。SQLServerの場合、ストアドの実行はEXEC文で行う

補足

apdx.sql

--テーブルの複数レコードを横に展開して1レコードにするSELECT文
;WITH apdx
AS(
    SELECT 1 AS n,'vle1-1' AS fld1 , 'vle1-2' AS fld2 UNION ALL
    SELECT 2 AS n,'vle2-1' AS fld1 , 'vle2-2' AS fld2 UNION ALL
    SELECT 3 AS n,'vle3-1' AS fld1 , 'vle3-2' AS fld2 UNION ALL
    SELECT 4 AS n,'vle4-1' AS fld1 , 'vle4-2' AS fld2 
)
--SELECT *FROM hoge
SELECT 
    MAX(CASE WHEN n= 1 THEN fld1 END )AS fl11
,   MAX(CASE WHEN n= 1 THEN fld2 END )AS fl12
,   MAX(CASE WHEN n= 2 THEN fld1 END )AS fl21
,   MAX(CASE WHEN n= 2 THEN fld2 END )AS fl22
,   MAX(CASE WHEN n= 3 THEN fld1 END )AS fl31
,   MAX(CASE WHEN n= 3 THEN fld2 END )AS fl32
,   MAX(CASE WHEN n= 4 THEN fld1 END )AS fl41
,   MAX(CASE WHEN n= 4 THEN fld2 END )AS fl42
FROM apdx

/* 
実行結果↓
fl11	fl12	fl21	fl22	fl31	fl32	fl41	fl42
vle1-1	vle1-2	vle2-1	vle2-2	vle3-1	vle3-2	vle4-1	vle4-2
*/

apdx.py

# ZIP関数を使って辞書を作成
   clm = ['fld1','fld2','fld3','fld4']
   vle = ['vle1','vle2','vle3','vle4']

   y = zip(clm,vle)
   print(dict(y))
   # {'fld3':'vle3','fld2':'vle2','fld1':'vle1','fld4':'vle4',}