To return SQL data in JSON format in Python, you can use the built-in json module and the pymysql package (assuming you are using MySQL as your database). Here’s an example of how to do it:
import mysql.connector
import json
# Connect to the database
conn = mysql.connector.connect(
host=”localhost”,
user=”username”,
password=”password”,
database=”database_name”
)
# Create a cursor object
cursor = conn.cursor()
# Execute the SQL query
query = “SELECT * FROM table_name”
cursor.execute(query)
# Fetch all rows and convert to a list of dictionaries
rows = cursor.fetchall()
result = []
for row in rows:
d = {}
for i, col in enumerate(cursor.description):
d[col[0]] = row[i]
result.append(d)
# Convert the list of dictionaries to JSON and print it
json_result = json.dumps(result)
print(json_result)
In the above code, we first connect to the database using pymysql, then create a cursor object to execute the SQL query. We then fetch all rows from the query result and convert them to a list of dictionaries, where each dictionary represents a row in the table with column names as keys and their values as values. Finally, we convert the list of dictionaries to JSON format using the json.dumps() method and print it.