How to return data from a database in JSON format using Python

Data management and manipulation in relational databases are accomplished using SQL (Structured Query Language). DataData refers to any set of facts, figures, or pieces of information that can be processed or analyzed. from databases can be stored, retrieved, updated, and deleted in a standard manner.

Understand JSON data

Before digging deep into what we’re about to learn, it is better to have a feel and an understanding of what JSON and JSON data look like.

JSON (JavaScript Object Notation) is an easy-to-read, write, parse, and generate data exchange format that is suitable for both humans and machines. The data is represented by key-value pairs, with the key being a string and the value being one of a number, a boolean, a string, an array, or another JSON object. It is frequently used for data exchange between servers and web applications.

The following example represents the data representation in JSON:

import json
import sqlite3
# connect to the database
con = sqlite3.connect('mydemo.db')
# create a cursor object
cur = con.cursor()
# execute the query to create a table called Biodata
# with fields id, name, age, sex
cur.execute("CREATE TABLE Biodata(id, name, age, sex)")
# we will add some values into the table
cur.execute("""
    INSERT INTO Biodata VALUES
        (1, 'John', 22, 'M'),
        (2, 'Samantha', 18, 'F'),
        (3, 'Emmanuel ', 28, 'M')
""")
# we need to commit the changes so it can be saved in the database
con.commit()
# fetch all the data and store it in a variable
data = cur.execute('''SELECT 
json_group_array(
	json_object(
        'id', id, 
        'name', name, 
        'age', age,
        'sex', sex
        )
    )
    FROM Biodata ''')
# print the JSON data
print(data.fetchall())
# close the database connection
con.close()

Explanation

Line 1: We first import the json module.
Line 2: We also import the sqlite3 module which provides a powerful and easy-to-use interface for working with SQLite databases in Python.
Line 5: We connect to the database with the name mydemo.db using the connect() method.
Line 8: We create a cursor object used to traverse and manipulate the result of a database query.
Line 12: We execute the query to create a table called Biodata with fields id, name, age, and sex .
Line 15: We add some values to the table with the INSERT keyword.
Line 23: We must commit the changes to save them in the database using the commit() method.
Line 26: Now we'll fetch all the data we have stored in the database and return it in a JSON format using the json_group_array() and the json_object() methods. We must pass in the keys as well as the values so that the data will be displayed properly.
Line 38: Print the JSON data.
Line 41: We need to close the database connection, so we use the close() method.

Free AI Mock Interviews

Coding Interview

Coding PatternsFree Interview

Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.

System Design

You TubeFree Interview

Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources