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.
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.
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 :
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.
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.
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.
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)
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 –
The Reverse is also possible. For more information please refer to the below article.
Thanks
Data Science Learner Team