How to return SQL data in JSON format python

JSON is a popular lightweight text-based format used to store data and transmit it in the form of arrays or attribute-value pairs. With its simple and human-readable structure, JSON has become a staple in the world of data communication, data storage, and exchange, especially in web applications and APIs.

Json Format
JSON Format

In Python, there are several ways to handle JSON data including the widely used Flask-RESTful, and Django REST Framework. It also offers a built-in JSON package for handling JSON data. In this article, we will see how to convert data from an SQL table into JSON format. 

Return JSON from sqlite3 using Python

First, you need to be sure that SQLite module is installed on your system. You don’t need to install it explicitly as it is already included in the standard library of Python. However, if you are using a version of Python that does not include SQLite, you can download the latest version of SQLite from the SQLite website or you can install it using the below command:

pip install sqlite3

To convert SQLite query results in JSON format in python. Use json.dump method to return SQL data in JSON format in python. Let’s see the below example to understand it:

# import required libraries
import sqlite3
import JSON

# Connect to a database
conn = sqlite3.connect('new_database.db')

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

# Execute the INSERT statement
cursor.execute("INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com')")


# Commit the changes to the database
conn.commit()

# Execute the SELECT statement to retrieve the data
cursor.execute('SELECT * FROM users')

# Fetch all rows from the query result
rows = cursor.fetchall()

# Convert the data to a list of dictionaries
data = []
for row in rows:
  data.append({"id": row[0], "name": row[1], "email": row[2]})

# Convert the data to JSON format
json_data = json.dumps(data)

# Print the JSON data
print(json_data)

# Close the database connection
conn.close()

Note that in this example, the data is being converted to a JSON string using json.dumps(). Let’s break down the code to understand what is happening in it.

Import Required module

The code starts by importing the sqlite3 and JSON modules (libraries).

import sqlite3
import JSON

Connect python to sqlite3

The sqlite3.connect() function is used to connect to the sqlite3 database. This function takes one parameter that specifies the connection details such as the database name. After that, a cursor object is created from the connection using the conn.cursor() method. The cursor is used to execute SQL statements on the database.

# Connect to a database
conn = sqlite3.connect('new_database.db')

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

Insert data in the database

Then cursor.execute() method is used to execute an SQL statement that creates a new table named users. The data is then inserted into the users’ table. The table has three columns id, name, and email

Note: The id column is defined as the primary key, which means that it must be unique and not null for each row in the table. 

After that, changes are committed to the database using the conn.commit() method,

# Execute the INSERT statement
cursor.execute("INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com')")

# Commit the changes to the database
conn.commit()

Retrieve data from the table

Using SELECT statement all the data in users table is retrieved. And cursor.fetchall() method is used to retrieve all rows from the query result.

# Execute the SELECT statement to retrieve the data
cursor.execute('SELECT * FROM users')

# Fetch all rows from the query result
rows = cursor.fetchall()

Store the fetched data in a key-value pair

The data is then converted to a list of dictionaries, with each dictionary representing a row in the table

# Convert the data to a list of dictionaries
data = []
for row in rows:
  data.append({"id": row[0], "name": row[1], "email": row[2]})

Convert the data to JSON format

The data obtained from the query is then converted into a JSON formatted string using the json.dumps() method. This method takes a Python object and returns a JSON formatted string. In our case, the object was a list of dictionaries, where each dictionary represented a row of data from the query result. The keys of each dictionary were derived from the names of the columns in the query result and the values were the corresponding data for each row.

# Convert the data to JSON format
json_data = json.dumps(data)

# Print the JSON data
print(json_data)

Close database Connection

Finally, the database connection is closed using the conn.close() method.

# Close the database connection
conn.close()

Return JSON from MYSQL using Python

To convert MYSQL query results in JSON format in python,

  • First, install MYSQL module using the below command:
pip install mysql-connector
  • Use json.dump method to return SQL data in JSON format in python. 

Let’s consider the below example to understand it:

import mysql.connector
import json

# Connect to a MySQL database
conn = mysql.connector.connect(
  host="host_name",
  user="user_name",
  password="password",
  database="new_database"
)
cursor = conn.cursor()

# Execute the SELECT statement to retrieve the data
cursor.execute('SELECT * FROM users')

# Fetch all rows from the query result
rows = cursor.fetchall()

# Convert the data to a list of dictionaries
data = []
for row in rows:
  data.append({"id": row[0], "name": row[1], "email": row[2]})

# Convert the data to JSON format
json_data = json.dumps(data)

# Print the JSON data
print(json_data)

# Close the database connection
conn.close()

Check here to know more about Python dictionaries and to handle nested dictionaries

Example Output:

[

  {

    “id”: 1,

    “name”: “John Doe”,

    “email”: “johndoe@example.com”

  }

]

The above code demonstrated the basic steps for retrieving data from a MySQL database and converting the data into a JSON formatted string using json.dump method in Python.

The main difference between the above code and this one is the method used to connect to the database. In sqlite3, sqlite3.connect is used while in MySQL, the mysql.connector.connect method is used with a dictionary of parameters (host name, user name, password, and database name) as input to create database connection.

Conclusion:

In this article, we have explored how to return SQL data in JSON format in python using json.dump method. We covered the implementation of this method with both SQLite and MySQL databases. By utilizing this approach, we can efficiently retrieve data from a database and return it in a JSON format within the Python environment.

Good Luck with your Learning !!

Similar Posts