Return SQL Table as JSON in Python

Conversion of one form of data into another form of data is always a critical task.
Follow below steps to convert SQL kind of database data into JSON format with Python.

1. Install Library and establish the connection between database and Python

Open Database Connectivity (ODBC) is essential to understand the solution.ODBC (Open Database Connectivity) is a powerful technology that serves as a bridge between Python and various databases. It enables developers to connect, query, and manipulate data seamlessly. Whether you’re a coding wizard or just starting on your journey, ODBC can work its magic and simplify your interactions with databases.

To begin your adventure with ODBC, you’ll need to install the pyodbc library—a vital component that grants you access to the wonders of the ODBC realm. With this library in your arsenal, you gain the ability to communicate with any ODBC-compliant database, regardless of the underlying database technology.

The connection string is your key to unlocking the gates of databases. It contains essential information, such as the database’s name, your username, and the secret password to grant you access. With this information, ODBC acts as the translator, enabling Python to understand and communicate with different database languages.

Once connected, you can send queries to the database and receive precious data in response. No longer do you need to learn a new language for each database. ODBC provides a universal interface, simplifying your coding life and empowering you to explore diverse databases with ease.import pyodbc
import json

Establish a Connection
connection_string = 'Driver={SQL Server};Server=my_server;Database=my_database;UID=my_username;PWD=my_password;'
conn = pyodbc.connect(connection_string)

2. Execute SQL queries and fetch data which you want to convert into JSON

Execute SQL Query
cursor = conn.cursor()
sql_query = "SELECT * FROM your_table;"
cursor.execute(sql_query)

Fetch Data
columns = [column[0] for column in cursor.description]
data = [dict(zip(columns, row)) for row in cursor.fetchall()]

3. Convert these data into JSON by using the inbuilt JSON library

#Convert to JSON
json_data = json.dumps(data, indent=4)

Save or Use JSON Data
# save JSON data to a file:
with open('data.json', 'w') as json_file:
    json_file.write(json_data)

# Alternatively, you can use the JSON data directly in your program:
print(json_data)