return SQL table as JSON in python

by

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:

>>> json_output
'[{"divroad": "N", "featcat": null, "countyfp": "001",...

Or with the following:

>>> j2 = query_db("select * from majorroadstiger where fullname= %s limit %s",\
 ("Mission Blvd", 1), one=True)

you get a single JSON object:

>>> j2 = json.dumps(j2)
>>> j2
'{"divroad": "N", "featcat": null, "countyfp": "001",...

More Related Contents:

  1. How to read a single object from a json file containing multiple objects using python? [closed]
  2. Split / Explode a column of dictionaries into separate columns with pandas
  3. How do I write JSON data to a file?
  4. How to get string objects instead of Unicode from JSON?
  5. Single vs double quotes in JSON
  6. Reading rather large JSON files [duplicate]
  7. How to JSON serialize sets?
  8. json.dumps vs flask.jsonify
  9. Pandas DENSE RANK
  10. Not all parameters were used in the SQL statement (Python, MySQL)
  11. Python/Json:Expecting property name enclosed in double quotes
  12. Python read JSON file and modify
  13. How to get JSON from webpage into Python script
  14. Python’s json module, converts int dictionary keys to strings
  15. Why is json.loads an order of magnitude faster than ast.literal_eval?
  16. Why does my text file keep overwriting the data on it?
  17. Saving UTF-8 texts with json.dumps as UTF-8, not as a \u escape sequence
  18. How to parse somewhat wrong JSON with Python?
  19. Convert numpy type to python
  20. Pandas query function not working with spaces in column names
  21. How to customise error response in FastAPI?
  22. Accessing dictionary by key in Django template
  23. Can json.loads ignore trailing commas?
  24. SQLAlchemy: how to filter date field?
  25. Displaying better error message than “No JSON object could be decoded”
  26. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  27. How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
  28. How can I serialize a numpy array while preserving matrix dimensions?
  29. Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio
  30. urllib2 and json

Leave a Comment