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)

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.
Execute SQL with pandas read_sql_query method
Stored procedure arguments are specified in python's variable embedded description (format) as described above.
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 ~