How to convert MySQL query result to JSON in Python
In this article, you will learn a simple process to convert MySQL query result to JSON using Python programming.
JSON (JavaScript Object Notation) is a lightweight, open standard file format. It is array data types and consisting of attribute–value pairs. It is easy to read and write for humans. It is used primarily to transmit data between web application and server. Today, it get more popular than XML.
Install MySQL Connector
A connector is a bridge when we have to connect a database with a programming language. It provides access to database driver to the required language. Python MySQL installer available for different operating systems. You can download the MSI version from here -
Python MySQL Installer
You can also install MySQL Connector using pip command.
pip install mysql-connector
Python connecting to MySQL
First, we need to import the MySQL connector in Python file to connect to the database. Then, use the connect() constructor to create a connection to the MySQL server. Make sure to replace the 'user', 'password', 'host' and 'database' with your database credentials.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='company')
if conn:
print ("Connected Successfully")
else:
print ("Connection Not Established")
Python MySQL Select Table
Here is the MySQL select statement to fetch data from the 'employee' table.
mydict = create_dict()
select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
for row in result:
mydict.add(row[0],({"name":row[1],"email":row[2],"phone":row[3]}))
In the above code, first we have instantiated the create_dict class and call the add() method to store the fetched data in key value pair. The class is defined as follows -
class create_dict(dict):
# __init__ function
def __init__(self):
self = dict()
# Function to add key:value
def add(self, key, value):
self[key] = value
Import JSON Module
import JSON
The JSON module provides dumps() method for writing data to files. This method takes data object to be serialized and the indent attribute specifies the indentation size for nested structures. The sort_keys attribute is a boolean value. If it is TRUE, then the output of dictionaries will be sorted by key.
stud_json = json.dumps(mydict, indent=2, sort_keys=True)
print(stud_json)
Complete Code
Here, we have merged the above code that we explained in chunks to convert MySQL query result to JSON using Python -
import mysql.connector
import json
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='company')
if conn:
print ("Connected Successfully")
else:
print ("Connection Not Established")
class create_dict(dict):
# __init__ function
def __init__(self):
self = dict()
# Function to add key:value
def add(self, key, value):
self[key] = value
mydict = create_dict()
select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
for row in result:
mydict.add(row[0],({"name":row[1],"email":row[2],"phone":row[3]}))
stud_json = json.dumps(mydict, indent=2, sort_keys=True)
print(stud_json)
The above code returns output in JSON format as follows -

Related Articles
CRUD operations in Python using MongoDB connectorWrite Python Pandas Dataframe to CSV
Quick Introduction to Python Pandas
Python Pandas DataFrame
Python3 Tkinter Messagebox
Python get visitor information by IP address
Python Webbrowser
Python Tkinter Overview and Examples
Python Turtle Graphics Overview
Factorial Program in Python
Python snake game code with Pygame
Python JSON Tutorial - Create, Read, Parse JSON file
Python convert xml to dict
Python convert dict to xml