Create a pandas Dataflame by searching the DB table using sqlalchemy

Search RDB table and create pandas dataframe

pandas provides a method to execute sql queries against RDB. Use this to execute a stored procedure and create a pandas dataframe from the execution result. It can be expanded to data aggregation using pandas and subsequent graph drawing using matplotlib.

make_pandas_df.py



    import sqlalchemy
    import pandas as pd

    q = 1
    ym = 201604

    CONNECT_INFO = 'mssql+pyodbc://hogehoge'
    engine = sqlalchemy.create_engine(CONNECT_INFO, encoding='utf-8')

    #Data frame creation
    query = 'EXEC dbo.sp_rtrv4pandas @q = {0},@ym = {1}'.format(qq,ym)
    df = pd.read_sql_query(query, engine,index_col =['prd'])

    print(df)
  1. The connection DB in the above is assumed to be SQL Server. In the case of SQL Server, the stored execution is performed by the EXEC statement.
  2. Execute SQL with pandas read_sql_query method
  3. Stored procedure arguments are specified in python's variable embedded description (format) as described above.
  4. Set the result of the stored procedure to df, which will be the pandas dataframe object

Recommended Posts

Create a pandas Dataflame by searching the DB table using sqlalchemy
Create a dictionary by searching the table using sqlalchemy
DB table insertion process using sqlalchemy
Create a graph using the Sympy module
Create a dataframe from excel using pandas
[Python] Sort the table by sort_values (pandas DataFrame)
Create a GUI on the terminal using curses
[CRUD] [Django] Create a CRUD site using the Python framework Django ~ 1 ~
[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 ~
Create a new csv with pandas based on the local csv
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
SQLAlchemy + DB migration by Alembic
Create a summary table by product and time by processing the data extracted from a certain POS system
Do a search by image from the camera roll using Pythonista3
Create and deploy a Django (PTVS) app using Azure Table storage
How to format a table using Pandas apply, pivot and swaplevel
[Ev3dev] Create a program that captures the LCD (screen) using python
Create a python GUI using tkinter
Create a pandas Dataframe from a string.
Creating a simple table using prettytable
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
[Development environment] How to create a data set close to the production DB
Create a record with attachments in KINTONE using the Python requests module
Create an application that inputs, displays, and deletes forms by using an array as a DB with Python / Flask.