Return SQL Data in JSON Format in Python : Stepwise Solution

To Return SQL Data in JSON Format in Python is easily possible in just two steps establishing the connection and fetching the data with SQL Database and then converting the data into JSON Format. Now the fetching part is not that tricky but converting the data into SQL format is possible in multiple contexts which depends on the business problems which opt for. Let’s start.

Return SQL Data in JSON Format in Python ( Solution ) –

As I explained earlier the first step is common in all the approaches to fetching data and then the second is converting the data into JSON format.

Step 1: Retrieving the data from the SQL Server –

To achieve this there are some substeps that will be common across all the SQL Server & Databases but the implementation syntax may vary. To elaborate and explain we are referencing the syntax related to MySQL Databases :

1.1 Establishing the connection –

Here we need to provide the connection string/config as follows :

db_config = { 'host': 'localhost', 'user': 'root', 'password': 'password', 'database': 'my_db' }
conn = mysql.connector.connect(**db_config)

These are default configs but the user / DBA can alter them as the application requires.

1.2 Query Execution and result fetching

In this step, we will create a cursor and execute the query on the SQL Server. Please refer to the below coding part.

cursor = conn.cursor() cursor.execute('SELECT * FROM  TABLE')
obj = cursor.fetchall()

Here the query will be changed as per the application requirement.

Step 2: Converting SQL Query Output into JSON Format

JSON is a key pair data structure that is equivalent to dict data structure format. Hence either we somehow convert the query output into key-value pair by assigning some random key and then convert the dict into JSON Format. OR there is a direct conversion possible where we will retrieve the output in tabular format and then direct convert the output into JSON object. Well, let’s walk through both ways.

Option 1: Converting the Query output to dict and then convert it into JSON –

Here we will take a reference key and then assign the output as a value for the key. This will create the query output into a python dict object. After it, we will use Python JSON Library to convert the same JSON Format. Here is the complete implementation.

import json 
dict_obj = { 'key': obj }
json_obj = json.dumps(dict_obj)

Option 2: Fetching the Query Result as Pandas object and then convert it into JSON-

Here we will first fetch the data into pandas dataframe and then convert the dataframe to JSON Object To make it easier, Here is the complete code for reference.

import mysql.connector as sql
import pandas as pd
db_connection = sql.connect(host='hostname', database='db_name', user='username', password='password')
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM TABLE')
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows)
result = df.to_json(orient="records")
parsed = json.loads(result)
json.dumps(parsed, indent=4)

This single code will Return SQL Data in JSON Format in Python. Another way to achieve the same is explicitly mentioned in the below article as well –