We will collect records from our sample student table in MySQL database and create the pickle file by using to_pickle(). Collect SQL dump of sample student table below.
Read more on MySQL with SQLAlchemy connection. you can add path if you want the file to be created using to_pickle() ( sample is given above )
import pandas as pd
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student LIMIT 0,10 "
df = pd.read_sql(sql,my_conn)
df.to_pickle("D:\my_data\my_data.pkl")
From Excel file to Pickle output
In above code we have created one DataFrame by taking data from a MySQL database table. We can create DataFrame by using any excel data or by using any csv file or from any other sources. ( check here to create a DataFrame from 8 different sources )
Once a DataFrame is created, then using that we can create pickle output by using to_pickle(). Here is one example to read one Excel file to a DataFrame and generate the string, you can explore other sources to create a DataFrame and finally generate pickle / file.
We used read_excel() to read our sample student.xlsx file.
df=pd.read_excel("D:\\my_data\\student.xlsx") # Path of the file.
df.to_pickle("D:\my_data\my_data.pkl")
df=pd.read_csv("D:\\my_data\\student.csv") # change the path
df.to_pickle("D:\my_data\my_data.pkl")
While creating the pickle we have to note the compression used. When we de-serialize or un-pickle the same file, we must use the same type of compression.