Return SQL Table as JSON in Python

Convert SQL query results to JSON in python using libraries like sqlite3, pymysql or psycopg2 for structured output in APIs or data processing workflows.

Summarize with AI

Not enough time? get the key points instantly.

Returning SQL table results as JSON in python development 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

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. 

Also Read: How to get POSTed JSON in Flask?

Tip

Use flask.jsonify() when returning JSON in a Flask API. For large datasets consider paginating results before JSON conversion to avoid memory overload.


Resources

Insights from our engineering leaders.

Our team shares practical insights on the technologies and engineering decisions shaping enterprise software today, grounded in real client work.

Python

How to Copy Files in Python

You can copy files in python using the shutil module which provides high level file and collection of files operations. You can also combine it…