Pandas DataFrames - Writing to and reading from MySQL Table

Data from python pandas dataframe instances can be written into MySQL database tables. Data can be loaded from MySQL tables into pandas dataframes as well. The pandas examples persist a dataframe into UserVitals table and load it back into pandas dataframe.

Serialization Updated 25 May 2019 3 min read

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

python
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

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

python
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

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