We will collect records from our sample student table in MySQL database and display the JSON formatted string. Collect SQL dump of sample studtent table below.
Read more on MySQL with SQLAlchemy connection. Below code will create student.json file in the same directory, you can add path if you want the file to be created at different location.
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_json('student1.json',orient='records')
From Excel file to JSON 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 JSON output by using to_json(). Here is one example to read one Excel file to a DataFrame and generate JSON string, you can explore other sources to create a DataFrame and finally generate JSON string / 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_json()