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.
print(json_data)That’s all you have to do. Using the above steps We have now successfully received SQL data in JSON style using Python.
Conclusion
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.
