Most of the time we create DataFrame by using data from Dictionary, but in some situations we may create DataFrame from csv, excel or from MySQL database tables and from this DataFrame we can export the data to a dictionary.
Here is one example where the student table from MySQL database is used to create DataFRame and finally the dictionary is created by using to_dict()
DataFrame from MySQL database
We will collect records from our sample student table in MySQL database and create the DataFrame. From the DataFrame by using to_dict() we will generate the dictionary. Collect SQL dump of sample student table below.
Read more on MySQL with SQLAlchemy connection
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, 3"
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 (Comma Separated Value) 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 dictionary output by using to_dict(). 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 dictionary.
We used read_excel() to read our sample student.xlsx file.
df=pd.read_excel("D:\\my_data\\student.xlsx") # Path of the file.