python-mysqlHow can I convert a MySQL query to JSON using Python?

To convert a MySQL query to JSON using Python, you can use the mysql.connector package. This package provides an interface for connecting to a MySQL database and executing queries. The following example code will demonstrate how to execute a MySQL query and convert the result to JSON:

import mysql.connector
import json

# Connect to the MySQL database
db = mysql.connector.connect(
    host="localhost",
    user="user",
    passwd="password",
    database="mydatabase"
)

# Create a cursor object
cursor = db.cursor()

# Execute the MySQL query
cursor.execute("SELECT * FROM mytable")

# Fetch the result and convert it to JSON
result = cursor.fetchall()
result_json = json.dumps(result)

print(result_json)

This code will output a JSON string containing the results of the query:

[["row1", "data1", "data2"], ["row2", "data3", "data4"]]

The code consists of the following parts:

1. Import packages - The mysql.connector and json packages are imported.
2. Connect to the database - The mysql.connector.connect() method is used to connect to the MySQL database.
3. Create a cursor object - A cursor object is created using the db.cursor() method.
4. Execute the query - The query is executed using the cursor.execute() method.
5. Fetch the result - The result is fetched using the cursor.fetchall() method.
6. Convert the result to JSON - The json.dumps() method is used to convert the result to a JSON string.

For more information, see the following links:

• MySQL Connector/Python
• MySQL Connector/Python Cursor Objects
• Python json Module

Edit this code on GitHub