Pickling MySQL table Data & Un-Pickling

Pickling MySQL table data
Data stored in MySQL tables can be pickled and un-pickled by using Pandas DataFrame.
What is Pickle .
Here are the steps required for completing the pickling and un- pickling processes.
  1. Connecting to Database. You can use MySQL connector or SQLAlchemy
  2. Create a DataFrame by using read_sql()
  3. Create a file in Binary mode and Pickle the table to this file
The full code is here . Update your userid, Password and database name with your MySQL login details.
import pickle
import mysql.connector
import pandas as pd 
my_connect = mysql.connector.connect(
      host="localhost",
      user="userid",
      passwd="****",
      database="my_tutorial"
    )
####### end of connection ####
my_data = pd.read_sql("SELECT * FROM student LIMIT 0,5",my_connect)
print(my_data)  # print output to check
###### end of collecting data and creation of DataFrame #######

fob = open('my_student','wb') # file handling object is created
pickle.dump(my_data,fob) # generated the Pickle
fob.close()
Creating DataFrame by using data from MySQL table
Here we have used one SQL Query with LIMIT to get only first 5 records. You can change the query and get different result from MySQL table. We used our Student table in above code.

There is a print command in above code, so 5 records of our student table will be shown ( same as output shown below )

Now we have created the my_student the pickled binary file. Now we will try to read the data by using our un-pickle code.
fob=open('my_student','rb')
my_dict1=pickle.load(fob) # reading the Pickle
fob.close()
print(my_dict1)
Output is here
   id        name  class  mark     sex
0   1    John Deo   Four    75  female
1   2    Max Ruin  Three    85    male
2   3      Arnold  Three    55    male
3   4  Krish Star   Four    60  female
4   5   John Mike   Four    60  female

Using to_pickle()

In place of using pickle_dump() we can use to_pickle() as we already have the data in Pandas DataFrame and by using this line we can create the file my_student. ( no need to create fob the file object )
my_data.to_pickle("my_student")

reading pickled data

We can create Pandas DatraFrame by using read_pickle() function. We will collect the same ( above ) records by using read_pickle().
import pandas as pd
my_dict2=pd.read_pickle('my_student')
print(my_dict2)
Output is here
   id        name  class  mark     sex
0   1    John Deo   Four    75  female
1   2    Max Ruin  Three    85    male
2   3      Arnold  Three    55    male
3   4  Krish Star   Four    60  female
4   5   John Mike   Four    60  female


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer