return SQL table as JSON in python

Question

I m playing around with a little web app in web py  and am setting up a url to return a JSON object  What s the best way to convert a SQL table to JSON using python

User · Answer

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

User · Answer

After 10 years      Without list comprehension Return a single row of values from a select query like below   quot select name userid  address from table1 where userid   1 quot   json output   name    quot name1 quot   userid   1  address    quot adress1  street1 quot    Code cur execute f quot select name userid  address from table1 where userid   1  quot   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

User · Answer

I would supplement The Demz answer with the psycopg2 version   import psycopg2  import psycopg2 extras import json connection   psycopg2 connect dbname  cdatabase  host  chost  port  cport   user  cuser  password  cpassword  cursor   connection cursor cursor factory psycopg2 extras DictCursor    This line allows dictionary access   select some records into  rows  jsonout  json dumps  dict ix  for ix in rows

User · Answer

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

User · Answer

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  quot IP COM quot   user  quot USERNAME quot   password  quot PASSWORD quot   database  quot DATABASE quot       except Exception          print  quot  nERROR  Unable to connect to the server  quot           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  quot    sql my sql txt quot    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  quot    output my json json quot    w   encoding  utf-8   as f          json dump query db sql   f  ensure ascii False  indent 4  cls DateTimeEncoder    if   name       quot   main   quot       write json      You get formatted my json json  for example                quot divroad quot   quot N quot          quot featcat quot  null         quot countyfp quot   quot 001 quot          quot date quot   quot 2020-08-28 quot

User · Answer

More information about how you ll be working with your data before transferring it would help a ton   The json module provides dump s  and load s  methods that ll help if you re using 2 6 or newer   http   docs python org library json html   -- EDITED --  Without knowing which libraries you re using I can t tell you for sure if you ll find a method like that   Normally  I ll process query results like this  examples with kinterbasdb because it s what we re currently working with    qry    Select Id  Name  Artist  Album From MP3s Order By Name  Artist    Assumes conn is a database connection  cursor   conn cursor   cursor execute qry  rows    x for x in cursor  cols    x 0  for x in cursor description  songs      for row in rows    song        for prop  val in zip cols  row       song prop    val   songs append song    Create a string representation of your array of songs  songsJSON   json dumps songs    There are undoubtedly better experts out there who ll have list comprehensions to eliminate the need for written out loops  but this works and should be something you could adapt to whatever library you re retrieving records with

User · Answer

Personally I prefer SQLObject for this sort of thing  I adapted some quick-and-dirty test code I had to get this   import simplejson  from sqlobject import      Replace this with the URI for your actual database connection   connectionForURI  sqlite   memory    sqlhub processConnection   connection    This defines the columns for your database table  See SQLObject docs for how it   does its conversions for class attributes  lt - gt  database columns  underscores to camel   case  generally   class Song SQLObject        name   StringCol       artist   StringCol       album   StringCol      Create fake data for demo - this is not needed for the real thing def MakeFakeDB        Song createTable       s1   Song name  B Song                 artist  Artist1                 album  Album1       s2   Song name  A Song                 artist  Artist2                 album  Album2    def Main          This is an iterable  not a list     all songs   Song select   orderBy Song q name       songs as dict           for song in all songs          song as dict                  name    song name               artist    song artist               album    song album          songs as dict append song as dict       print simplejson dumps songs as dict    if   name         main         MakeFakeDB       Main

User · Answer

Here is a really nice example of a pythonic way to do that   import json import psycopg2  def db database name  pepe        return psycopg2 connect database database name   def query db query  args     one False       cur   db   cursor       cur execute query  args      r    dict  cur description i  0   value                   for i  value in enumerate row   for row in cur fetchall        cur connection close       return  r 0  if r else None  if one else r  my query   query db  select   from majorroadstiger limit  s    3     json output   json dumps my query    You get an array of JSON objects    gt  gt  gt  json output     divroad    N    featcat   null   countyfp    001        Or with the following    gt  gt  gt  j2   query db  select   from majorroadstiger where fullname   s limit  s       Mission Blvd   1   one True    you get a single JSON object    gt  gt  gt  j2   json dumps j2   gt  gt  gt  j2    divroad    N    featcat   null   countyfp    001

User · Answer

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  quot database db quot       conn row factory   dict factory     c   conn cursor       c execute  quot SELECT   FROM table quot       rst   c fetchall     rst is a list of dict     return jsonify rst

User · Answer

I knocked together a short script that dumps all data from all tables  as dicts of column name   value    Unlike other solutions  it doesn t require any info about what the tables or columns are  it just finds everything and dumps it  Hope someone finds it useful   from contextlib import closing from datetime import datetime import json import MySQLdb DB NAME    x  DB USER    y  DB PASS    z   def get tables cursor       cursor execute  SHOW tables       return  r 0  for r in cursor fetchall      def get rows as dicts cursor  table       cursor execute  select   from     format table       columns    d 0  for d in cursor description      return  dict zip columns  row   for row in cursor fetchall     def dump date thing       if isinstance thing  datetime           return thing isoformat       return str thing    with closing MySQLdb connect user DB USER  passwd DB PASS  db DB NAME   as conn  closing conn cursor    as cursor      dump          for table in get tables cursor           dump table    get rows as dicts cursor  table      print json dumps dump  default dump date  indent 2

User · Answer

Most simple way   use json dumps but if its datetime will require to parse datetime into json serializer   here is mine   import MySQLdb  re  json from datetime import date  datetime  def json serial obj          JSON serializer for objects not serializable by default json code         if isinstance obj   datetime  date            return obj isoformat       raise TypeError   Type  s not serializable    type obj    conn   MySQLdb connect instance  curr   conn cursor   curr execute  SELECT   FROM  assets    data   curr fetchall   print json dumps data  default json serial    it will return json dump  one more simple method without json dumps  here get header and use zip to map with each finally made it as json but this is not change datetime into json serializer     data json      header    i 0  for i in curr description  data   curr fetchall   for i in data      data json append dict zip header  i    print data json

User · Answer

import sqlite3 import json  DB      the database db   def get all users  json str   False        conn   sqlite3 connect  DB       conn row factory   sqlite3 Row   This enables column access by name  row  column name        db   conn cursor        rows   db execute         SELECT   from Users          fetchall        conn commit       conn close        if json str          return json dumps   dict ix  for ix in rows     CREATE JSON      return rows   Callin the method no json     print get all users     prints     1  u orvar   u password123     2  u kalle   u password123      Callin the method with json     print get all users  json str   True     prints      password    password123    id   1   name    orvar      password    password123    id   2   name    kalle

User · Answer

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  quot ID quot   Integer  primary key True      Name   Column  quot Name quot   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

[python] return SQL table as JSON in python

Examples related to python

Examples related to sql

Examples related to json