Reading JSON, SQL Databases & Parquet Files0%
Indexing with loc & iloc

Reading JSON, SQL Databases & Parquet Files

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Reading JSON, SQL Databases & Parquet Files

Reading Time: 8 Mins | Level: Beginner to Advanced

1. Modern Data Storage Formats

In enterprise data analytics, data rarely lives solely in spreadsheets. Data Analysts frequently ingest data from:

  1. 1
    REST APIs & Web Services (JSON)
  2. 2
    Relational Databases (PostgreSQL, MySQL, SQL Server, SQLite)
  3. 3
    Big Data Lakes & Cloud Warehouses (Apache Parquet)

2. Ingesting JSON Files (read_json)

JSON (JavaScript Object Notation) is hierarchical and nested.

Python
import pandas as pd
 
# Load flat JSON records
df_users = pd.read_json('users_api.json')
 
# Normalize semi-structured nested JSON
from pandas import json_normalize
 
raw_json = [
{"id": 1, "name": "Amit", "address": {"city": "Delhi", "pincode": 110001}},
{"id": 2, "name": "Priya", "address": {"city": "Mumbai", "pincode": 400001}}
]
 
df_flat = pd.json_normalize(raw_json)
print(df_flat)

Output:

Output
id name address.city address.pincode
0 1 Amit Delhi 110001
1 2 Priya Mumbai 400001

3. Querying Relational SQL Databases (read_sql)

Pandas integrates with SQLAlchemy to query any SQL database and load the results directly into a DataFrame:

Python
from sqlalchemy import create_engine
 
# 1. Create a database connection engine (e.g., MySQL or PostgreSQL)
engine = create_engine('sqlite:///msk_institute.db')
 
# 2. Execute SQL query directly into a DataFrame
sql_query = """
SELECT student_id, name, course, fees_paid
FROM admissions
WHERE fees_paid > 10000
ORDER BY fees_paid DESC;
"""
 
df_students = pd.read_sql(sql_query, con=engine)
print(df_students.head())

4. Apache Parquet: The Fast Columnar Big Data Format

Parquet is a columnar file format that offers:

  • Up to 10x smaller file sizes via dictionary and run-length compression (snappy).
  • Up to 50x faster read speeds compared to CSV.
  • Preserved data types (no need to parse dates or specify column datatypes upon loading).
Bash / Terminal
pip install pyarrow fastparquet
Python
# Export to Parquet
df_students.to_parquet('students.parquet', engine='pyarrow', compression='snappy')
 
# Read Parquet
df_fast = pd.read_parquet('students.parquet', engine='pyarrow')
Interactive Quiz

Multiple Choice Questions

Question 1
Which function flattens semi-structured, nested JSON dictionaries into a flat 2D DataFrame?
Question 2
What library is typically used alongside pd.read_sql() to manage database connections?
Question 3
Why is Apache Parquet preferred over CSV for storing large analytics datasets?
Question 4
Which parameter in df.to_parquet() defines the underlying compression algorithm?
Question 5
What does pd.read_sql_table('employees', con=engine) do?
Next Lesson

Indexing with loc & iloc

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Reading Excel Spreadsheets & Multiple SheetsIndexing with loc & iloc

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext