Practice serializing MySQL tables into Pandas DataFrames, pickling binary files, using to_pickle() and read_pickle() directly inside Google Colab.
Data stored in MySQL tables can be serialized (pickled) and deserialized (un-pickled) efficiently by loading query results into a Pandas DataFrame.
Here are the steps required for completing the pickling and un-pickling processes :
to_pickle() method.Here is the complete code using standard Python pickle and mysql.connector. Update your connection credentials accordingly.
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()
We used an SQL query with LIMIT to get only the first 5 records from our student table .
Now we will read the pickled binary file back into memory using Python's pickle.load() :
fob = open('my_student', 'rb')
my_dict1 = pickle.load(fob) # reading the Pickle
fob.close()
print(my_dict1)
Output :
id name class mark gender
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
In place of using pickle.dump() with a file object, we can use Pandas' built-in to_pickle() method directly on the DataFrame to serialize the file without manually managing binary file objects :
my_data.to_pickle("my_student")
Similarly, we can restore a Pandas DataFrame directly using read_pickle():
import pandas as pd
my_dict2 = pd.read_pickle('my_student')
print(my_dict2)
Output :
id name class mark gender
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
pickle module is not secure against erroneous or maliciously crafted data. Never unpickle data received from untrusted or unauthenticated sources, as it can execute arbitrary code on your system.
pd.read_sql() seamlessly (e.g., pd.read_sql(text("SELECT * FROM student"), engine)).
to_pickle() and read_pickle() methods provide a cleaner, concise syntax specifically optimized for DataFrame serialization, automatically handling compression options and file paths.
Learn how to load MySQL table results directly into Pandas DataFrames.
Explore standard cursor fetch methods like fetchall() and fetchmany().
Master SQLAlchemy 2.0+ engines and connection context managers.
Download the complete student.sql database dump file featuring sample records with the gender column schema.
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.