How to fetch JSON data using Python API, then convert it into DataFrame and loading into MySQL Database?
In the below code, I have implemented
1-Python API to fetch JSON data from URL
2-Convert JSON to pandas DataFrame
3-Load Dataframe into a table in MySQL DB
import pymysql
import requests
import json
import pandas as pd
from sqlalchemy import create_engine
response = requests.get(“https://lnkd.in/dcCchEhk")
# Convert Json to Dataframe
df = pd.DataFrame(response.json())
print(df)
# Replace the following values with your MySQL credentials and database name
db_username = ‘root’
db_password = ‘anish’
db_name = ‘LearningSQL’
db_host = ‘127.0.0.1’
db_port = ‘3306’
# Create a MySQL engine using sqlalchemy
engine = create_engine(f’mysql+pymysql://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}’)
# Write the DataFrame to a MySQL database
df.to_sql(‘todolist’, con=engine, if_exists=’replace’, index=False)
