Return SQL Data in JSON Format in Python : Stepwise Solution

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 –

pandas read_sql() method implementation with Examples

Additional Notes –

The Reverse is also possible. For more information please refer to the below article.

pandas to_sql method: Insert datasets values to the Database


Data Science Learner Team

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

Thank you for signup. A Confirmation Email has been sent to your Email Address.

Something went wrong.

Meet Abhishek ( Chief Editor) , a data scientist with major expertise in NLP and Text Analytics. He has worked on various projects involving text data and have been able to achieve great results. He is currently manages, where he and his team share knowledge and help others learn more about data science.
Thank you For sharing.We appreciate your support. Don't Forget to LIKE and FOLLOW our SITE to keep UPDATED with Data Science Learner