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 |