Day 12 - Reading JSON and SQL Data in Python

In Python, working with JSON and SQL data is common in data processing, web scraping, or API integration. Whether you're dealing with data in a file, from an API, or from a database, Python offers powerful libraries to manage these tasks efficiently.

1. Reading JSON from a File

Read a local JSON file into a Python dictionary.

import json
with open('data.json', 'r') as file:
    data = json.load(file)
print(data['name'])

2. Fetching JSON from an API

Fetch JSON data from an API and process it.

import requests
response = requests.get('https://jsonplaceholder.typicode.com/users')
if response.status_code == 200:
    data = response.json()
    for user in data:
        print(user['name'], user['email'])

3. Connecting to MySQL with mysql.connector

Connect to a MySQL database and fetch records from a table.

import mysql.connector
conn = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
cursor = conn.cursor()
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

4. Using pandas.read_sql_query

Run a SQL query and load the result into a pandas DataFrame.

import pandas as pd
import mysql.connector
conn = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
df = pd.read_sql_query("SELECT * FROM employees", conn)
print(df)
conn.close()