Returning SQL table results as JSON in python is common when building APIs or exporting data.Works across SQLite, MySQL, PostgreSQL and more.
Below are two practical methods:
Method 1: Using sqlite3 with json (Basic Example)
Converts rows from an SQLite database into JSON format using standard libraries.
python
import sqlite3import json
# Connect to the database
conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()
# Execute query
cursor.execute("SELECT * FROM users")
columns = [description[0] for description in cursor.description]
rows = cursor.fetchall()
# Convert to list of dictionaries
results = [dict(zip(columns, row)) for row in rows]
# Convert to JSON
json_output = json.dumps(results, indent=2)
print(json_output)
This works well for local SQLite files or small data exports.
Also Read: Using Python For Data Science in 2025
Method 2: MySQL / PostgreSQL with pymysql / psycopg2
Fetches SQL results and returns JSON for external databases using DB-specific drivers.
python
import pymysqlimport json
conn = pymysql.connect(
host='localhost',
user='root',
password='password',
db='mydb'
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
columns = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
results = [dict(zip(columns, row)) for row in rows]
json_output = json.dumps(results, indent=2)
print(json_output)
Swap pymysql with psycopg2 if you are using PostgreSQL, the logic remains similar.
Tip
Use flask.jsonify() when returning JSON in a Flask API. For large datasets consider paginating results before JSON conversion to avoid memory overload.
