How to Return SQL Data in JSON Format in Python : Steps

Are you a Python coder searching for the method to return SQL data in JSON format? If your response is yes, then you have arrived at the ideal website. In this tutorial, you will learn how to return SQL data in JSON format using Python.

What is JSON Object?

JSON (JavaScript Object Notation) is a popular way of transferring data between applications, which makes it very simple to serialize and deserialize data. It is an ideal and the best selection for web services and Application Programming Interfaces (APIs) since it is lightweight and easy to understand.

We can use Python’s built-in JSON library to get the SQL data in JSON format. This library offers a range of functions and classes that make it effortless to handle JSON data.

Additionally, we will use the MySQL connector for Python, an authorized MySQL library that enables us to link to our database.

Step 1: Establish the connection

Establishing a connection to the MySQL database is done with the mysql.connector.connect() function. We need to pass the credentials in the form of a dictionary in order to make the connection.

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

Step 2: Return the cursor

Once you are linked to the database you will use the cursor object to perform a query in order to acquire the data you need.

cursor = conn.cursor() cursor.execute('SELECT * FROM your_table')

Step 3:  Fetch the data

Now you will take the output of your query and make a dictionary out of it by using the cursor’s fetchall() method. It will acquire all the rows returned by the query.

rows = cursor.fetchall()

Step 4: Return SQL Data in JSON Format

Now that you have the data, let’s turn it into a dictionary so you can output it in JSON form. To do so you will use the json.dumps() function to make the conversion to JSON.

import json 
data = { 'data': rows }
json_data = json.dumps(data)

Step 5: Print the data

The only thing that remains is to output our JSON information. To do so you will use the print() command.


That’s all you have to do. Using the above steps We have now successfully received SQL data in JSON style using Python.


In this tutorial, you have used the JSON library to transform the data into JSON format. Also used the MySQL connector for Python to link to our database. Now that you understood to return SQL data in JSON structure in Python, you can start creating web services and APIs using the above simple steps.

Hi, I am CodeTheBest. Here you will learn the best coding tutorials on the latest technologies like a flutter, react js, python, Julia, and many more in a single place.


This Offer is Limited! Grab your Discount!
15000 ChatGPT Prompts
Offer Expires In: