SQL query to json list in python

Python -- Posted on March 2, 2024

convert sql query to json list in python with pyodbc

                import pyodbc
import json

MS_SQL_Host = "host"
MS_SQL_Db = "db_name"
MS_SQL_User = "user"
MSSQL_Paswd = "pass"

class DecimalEncoder(json.JSONEncoder):
    def default(self, obj):
        # 👇️ if passed in object is instance of Decimal
        # convert it to a string
        if isinstance(obj, Decimal):
            return str(obj)
        # 👇️ otherwise use the default behavior
        return json.JSONEncoder.default(self, obj)

def write_json_data(data, filename):
    with open("{}.json".format(filename), "w", encoding="utf-8") as f:
        # json.dump(data, f, ensure_ascii=False, indent=4, cls=DecimalEncoder)
        json.dump(data, f, ensure_ascii=False, cls=DecimalEncoder)

def get_data(query, name):
    mssql_connection = pyodbc.connect(
        "DRIVER={SQL Server};SERVER="
        + MS_SQL_Host
        + ";DATABASE="
        + MS_SQL_Db
        + ";UID="
        + MS_SQL_User
        + ";PWD="
        + MSSQL_Paswd
    mssql_cursor = mssql_connection.cursor()
    results = []
    columns = [column[0] for column in mssql_cursor.description]
    for row in mssql_cursor.fetchall():
        data = dict(zip(columns, row))
    return write_json_data(results, name)

data = get_data("sql_query", 'data')

Related Posts