[PYTHON] Create a dictionary by searching the table using sqlalchemy

Search the DB table by stored execution and create a dictionary

For example, prepare a stored procedure in SQL Server that returns one record that expands multiple records of an arbitrary table horizontally, execute the stored procedure with SQLAlchemy, and fetch the execution result (vle) with fetchone (). , Get the column name (clm) of the data with keys (). If you make a list of these two with a zip function and make it a dict dictionary, you can expand the process such as XML file creation using elementTree in the subsequent process.

make_dict.py

    import sqlalchemy

    ym = 201603

    CONNECT_INFO = 'mssql+pyodbc://hogehoge' #hoge hoge is any dns(odbc connection)
    engine = sqlalchemy.create_engine(CONNECT_INFO, encoding='utf-8')

    #Session generation
    Session = sqlalchemy.orm.sessionmaker(bind=engine)
    session = Session()
    
    #Stored procedure execution
    query = 'EXEC dbo.sp_rtrv4XML @prd = {0}'.format(ym)
    #vle = session.execute(query).fetchall() #Get execution result:When multiple results are returned
    #print( vle[0][0] )

    vle = session.execute(query).fetchone() #Get execution result:1 case will be returned
    clm = session.execute(query).keys()  #Get column name

    dc = dict(zip(clm , vle)) #Dictionary creation
    print(dc)

    #Clean up after the session
    #session.commit()
    session.close()

Note) The DB to be connected is assumed to be SQL Server. In the case of SQL Server, the stored execution is performed by the EXEC statement.

Supplement

apdx.sql

--SELECT statement that expands multiple records in a table horizontally into one record
;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

/* 
Execution result ↓
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

#Create a dictionary using the ZIP function
   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',}