Pandas DataFrames - Writing To And Reading From MySQL Table

Overview:

  • Data from pandas dataframes can be read from and written to several external repositories and formats.
  • Pandas support writing dataframes into MySQL database tables as well as loading from them.

Writing data from MySQL database table into pandas dataframe:

  • Import the required Python modules including pandas, pymysql and sqlalchemy.
  • Obtain an SQLAlchemy engine object to connect to the MySQL database server by providing required credentials.
  • Using the engine object, connect to the MySQL server by calling the connect() method.
  • Create a dictionary of python lists. The python lists correspond to column data and the keys of the python dict correspond to column names.
  • Create a dataframe by calling the pandas dataframe constructor and passing the python dict object as data.
  • Invoke to_sql() method on the pandas dataframe instance and specify the table name and database connection. This creates a table in MySQL database server and populates it with the data from the pandas dataframe.

Example – Write Pandas DataFrame into a MySQL Database Table:

from sqlalchemy import create_engine

import pymysql

import pandas as pd

 

userVitals = {"UserId":["xxxxx", "yyyyy", "zzzzz", "aaaaa", "bbbbb", "ccccc", "ddddd"],

            "UserFavourite":["Greek Salad", "Philly Cheese Steak", "Turkey Burger", "Crispy Orange Chicken", "Atlantic Salmon", "Pot roast", "Banana split"],

            "MonthlyOrderFrequency":[5, 1, 2, 2, 7, 6, 1],

            "HighestOrderAmount":[30, 20, 16, 23, 20, 26, 9],

            "LastOrderAmount":[21,20,4,11,7,7,7],

            "LastOrderRating":[3,3,3,2,3,2,4],

            "AverageOrderRating":[3,4,2,1,3,4,3],

            "OrderMode":["Web", "App", "App", "App", "Web", "Web", "App"],

            "InMedicalCare":["No", "No", "No", "No", "Yes", "No", "No"]};

 

tableName   = "UserVitals"

dataFrame   = pd.DataFrame(data=userVitals)           

 

sqlEngine       = create_engine('mysql+pymysql://root:@127.0.0.1/test', pool_recycle=3600)

dbConnection    = sqlEngine.connect()

 

try:

    frame           = dataFrame.to_sql(tableName, dbConnection, if_exists='fail');

except ValueError as vx:

    print(vx)

except Exception as ex:   

    print(ex)

else:

    print("Table %s created successfully."%tableName);   

finally:

    dbConnection.close()

Output:

Table UserVitals created successfully.

Reading data from MySQL database table into pandas dataframe:

  • Call read_sql() method of the pandas module by providing the SQL Query and the SQL Connection object to get data from the MySQL database table.
  • The database connection to MySQL database server is created using sqlalchemy.
  • read_sql() method returns a pandas dataframe object. The frame will have the default-naming scheme where the rows start from zero and get incremented for each row. The columns will be named after the column names of the MySQL database table.

Example – Read a MySQL Database Table into a Pandas DataFrame:

from sqlalchemy import create_engine

import pymysql

import pandas as pd

 

sqlEngine       = create_engine('mysql+pymysql://root:@127.0.0.1', pool_recycle=3600)

dbConnection    = sqlEngine.connect()

frame           = pd.read_sql("select * from test.uservitals", dbConnection);

 

pd.set_option('display.expand_frame_repr', False)

print(frame)

 

dbConnection.close()

 

Output:

   index UserId          UserFavourite  MonthlyOrderFrequency  HighestOrderAmount  LastOrderAmount  LastOrderRating  AverageOrderRating OrderMode InMedicalCare

0      0  xxxxx            Greek Salad                      5                  30               21                3                   3       Web            No

1      1  yyyyy    Philly Cheese Steak                      1                  20               20                3                   4       App            No

2      2  zzzzz          Turkey Burger                      2                  16                4                3                   2       App            No

3      3  aaaaa  Crispy Orange Chicken                      2                  23               11                2                   1       App            No

4      4  bbbbb        Atlantic Salmon                      7                  20                7                3                   3       Web           Yes

5      5  ccccc              Pot roast                      6                  26                7                2                   4       Web            No

6      6  ddddd           Banana split                      1                   9                7                4                   3       App            No


Copyright 2020 © pythontic.com