return SQL table as JSON in python

nobody seem to have offered the option to get the JSON directly from the Postgresql server, using the postgres JSON capability https://www.postgresql.org/docs/9.4/static/functions-json.html

No parsing, looping or any memory consumption on the python side, which you may really want to consider if you're dealing with 100,000's or millions of rows.

from django.db import connection

sql = 'SELECT to_json(result) FROM (SELECT * FROM TABLE table) result)'
with connection.cursor() as cursor:
  cursor.execute(sql)
  output = cursor.fetchall()

a table like:

id, value
----------
1     3
2     7

will return a Python JSON Object

[{"id": 1, "value": 3},{"id":2, "value": 7}]

Then use json.dumps to dump as a JSON string

If you are using an MSSQL Server 2008 and above, you can perform your SELECT query to return json by using the FOR JSON AUTO clause E.G

SELECT name, surname FROM users FOR JSON AUTO

Will return Json as

[{"name": "Jane","surname": "Doe" }, {"name": "Foo","surname": "Samantha" }, ..., {"name": "John", "surname": "boo" }]

After 10 years :) . Without list comprehension

Return a single row of values from a select query like below.

"select name,userid, address from table1 where userid = 1"

json output

{ name : "name1", userid : 1, address : "adress1, street1" }

Code

cur.execute(f"select name,userid, address from table1 where userid = 1 ")
row = cur.fetchone()
desc = list(zip(*cur.description))[0]  #To get column names
rowdict = dict(zip(desc,row))
jsondict = jsonify(rowdict)  #Flask jsonify

cur.description is a tuple of tuples as below. unzip and zip to combine column name with values

(('name', None, None, None, None, None, None), ('userid', None, None, None, None, None, None), ('address', None, None, None, None, None, None))

For sqlite, it is possible to set a callable to connection.row_factory and change the format of query results to python dictionary object. See the documentation. Here is an example:

import sqlite3, json

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        # col[0] is the column name
        d[col[0]] = row[idx]
    return d

def get_data_to_json():
    conn = sqlite3.connect("database.db")
    conn.row_factory = dict_factory
    c = conn.cursor()
    c.execute("SELECT * FROM table")
    rst = c.fetchall() # rst is a list of dict
    return jsonify(rst)

I'm Sorry to inform that the above code will fail to export date time fields in json. It will appear like this:

....
, 'TimeCreated': datetime.datetime(2019, 6, 17, 9, 2, 17), 'Created': 1,
....

Which make the output an invalid json.

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String

Base = declarative_base()
metadata = Base.metadata


class UserTable(Base):
    __tablename__ = 'UserTable'

    Id = Column("ID", Integer, primary_key=True)
    Name = Column("Name", String(100))

        
class UserTableDTO:
    def __init__(self, ob):
        self.Id = ob.Id
        self.Name = ob.Name
        
rows = dbsession.query(Table).all()

json_string = [json.loads(json.dumps(UserTableDTO(ob).__dict__, default=lambda x: str(x)))for ob in rows]
print(json_string)

One simple example for return SQL table as formatted JSON and fix error as he had @Whitecat

I get the error datetime.datetime(1941, 10, 31, 0, 0) is not JSON serializable

In that example you should use JSONEncoder.

import json
import pymssql

# subclass JSONEncoder
class DateTimeEncoder(JSONEncoder):
        #Override the default method
        def default(self, obj):
            if isinstance(obj, (datetime.date, datetime.datetime)):
                return obj.isoformat()

def mssql_connection():
    try:
        return pymssql.connect(server="IP.COM", user="USERNAME", password="PASSWORD", database="DATABASE")
    except Exception:
        print("\nERROR: Unable to connect to the server.")
        exit(-1)

def query_db(query):
    cur = mssql_connection().cursor()
    cur.execute(query)
    r = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return r

def write_json(query_path):
    # read sql from file
    with open("../sql/my_sql.txt", 'r') as f:
        sql = f.read().replace('\n', ' ')
    # creating and writing to a json file and Encode DateTime Object into JSON using custom JSONEncoder
    with open("../output/my_json.json", 'w', encoding='utf-8') as f:
        json.dump(query_db(sql), f, ensure_ascii=False, indent=4, cls=DateTimeEncoder) 

if __name__ == "__main__":
    write_json()

# You get formatted my_json.json, for example:
[
   {
      "divroad":"N",
      "featcat":null,
      "countyfp":"001",
      "date":"2020-08-28"
   }
]