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',}

Recommended Posts

Create a dictionary by searching the table using sqlalchemy
Create a pandas Dataflame by searching the DB table using sqlalchemy
Create a nested dictionary using defaultdict
Create a GUI on the terminal using curses
[CRUD] [Django] Create a CRUD site using the Python framework Django ~ 2 ~
[CRUD] [Django] Create a CRUD site using the Python framework Django ~ 3 ~
[CRUD] [Django] Create a CRUD site using the Python framework Django ~ 4 ~
[CRUD] [Django] Create a CRUD site using the Python framework Django ~ 5 ~
Reuse the behavior of the @property method by using a descriptor [16/100]
Create a real-time auto-reply bot using the Twitter Streaming API
Create a new list by combining duplicate elements in the list
Do a search by image from the camera roll using Pythonista3
Precautions when using a list or dictionary as the default argument
Creating a simple table using prettytable
Create a summary table by product and time by processing the data extracted from a certain POS system
DB table insertion process using sqlalchemy
[Ev3dev] Create a program that captures the LCD (screen) using python
Create a CRUD API using FastAPI
Create a C wrapper using Boost.Python
Estimate the probability that a coin will appear on the table using MCMC
Create a record with attachments in KINTONE using the Python requests module
What is the fastest way to create a reverse dictionary in python?
Python> dictionary> values ()> Get All Values by Using values ()
[Python] Create a Batch environment using AWS-CDK
Create an application using the Spotify API
Various ways to create a dictionary (memories)
[Python] Scraping a table using Beautiful Soup
Create a Hatena dictionary for SKK (additional)
Script to create a Mac dictionary file
Add a layer using the Keras backend
Create a REST API using the model learned in Lobe and TensorFlow Serving.
[AWS Lambda] Create a deployment package using the Docker image of Amazon Linux
Create a GIF file using Pillow in Python
Create a beauty pageant support app using PyLearn2
Let's create a REST API using SpringBoot + MongoDB
Create a record table from JFL match results
Create a phylogenetic tree from Biopyton using ClustalW2
Write a TCP server using the SocketServer module
Create a bulletin board with Heroku, Flask, SQLAlchemy
[Python] Sort the table by sort_values (pandas DataFrame)
Create a binary data parser using Kaitai Struct
Create a web map using Python and GDAL
I tried using the Datetime module by Python
Create a visitor notification system using Raspberry Pi
Create a Mac app using py2app and Python3! !!
Create a MIDI file in Python using pretty_midi
Create a command to get the work log
Isn't there a default value in the dictionary?
Create a translation tool with the Translate Toolkit
Create a table of contents with IPython notebook
Try to create a battle record table with matplotlib from the data of "Schedule-kun"
[Python] You can save an object to a file by using the pickle module.
Use Twitter API to reduce the time taken by Twitter (create a highlighting (like) timeline)