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