Return SQL Table as JSON in Python

return SQL table as JSON in python

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",...

Convert SQL into json in Python

Well, if you simply do:

json_string = json.dumps(cursor.fetchall())

you'll get an array of arrays...

[["earning1", "date1"], ["earning2", "date2"], ...]

Another way would be to use:

json_string = json.dumps(dict(cursor.fetchall()))

That will give you a json object with earnings as indexes...

{"earning1": "date1", "earning2": "date2", ...}

If that's not what you want, then you need to specify how you want your result to look...

Convert SQL data into JSON in Python

You can do that by:

# first import json
import json
...
# returns a dict containing all rows
rows = cursor.fetchall() 

# Print rows in json format
print(json.dumps(rows))

Edit: Object of type Decimal is not JSON serializable can be fixed with an serializer

import decimal

def dec_serializer(o):
    if isinstance(o, decimal.Decimal):
        return float(o)

json.dump(results, default=dec_serializer)

Python converting mysql query result to json

You can use cursor description to extract row headers:
row_headers=[x[0] for x in cursor.description] after the execute statement. Then you can zip it with the result of sql to produce json data.
So your code will be something like:

from flask import Flask
from flask.ext.mysqldb import MySQL
import json
app = Flask(__name__)
app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'hello_db'
mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute('''SELECT * FROM Users WHERE id=1''')
   row_headers=[x[0] for x in cur.description] #this will extract row headers
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))
   return json.dumps(json_data)

if __name__ == '__main__':
   app.run(debug=True)

In the return statement you can use jsonify instead of json.dumps as suggested by RickLan in the comments.

Converting sql table data to json

Found really helpul answer here which completely solves my purpose , it sets my table column names as the keys for json objects in json array .
Thanks to algrebe for the link.

My running code with required json output is :

  @app.route('/getTransaction', methods=['GET','POST'])
  def getTransaction():

  uid = request.form["uid"]
  db = my.connect("somehost.com","someuser","somepwd","someDB")
  cur = db.cursor()

  cur.execute("select * from Transactions where uid='%s'" %(uid))

  **columns = cur.description
  result = [{columns[index][0]:column for index, column in enumerate(value)}   for value in cur.fetchall()]**

  db.close()

  **return json.dumps(result,cls=DateTimeEncoder)**

Python with JSON Create Table SQL

If all you want to do is get the SQL command as text, you can do this with a combination of string concatenation (or formatting) and for loops.

If you want to "connect" to a database and execute the queries, read up on "database connectors" for the relevant database (such as MySQL, SQLite, SQLPlus etc).

---

Edit

Here's one way to do it. I don't want to just give you an answer without an explanation, but I hope this is self explanatory. Feel free to ask anything confusing.

c_name = ['departmentid', 'name', 'groupname', 'modifieddate']
d_type =['integer', 'character varying', 'character varying', 'timestamp without time zone']

sql_query = "CREATE TABLE MY_TABLE(\n"

for column, datatype in zip(c_name, d_type):
    sql_query += f"\t\"{column}\" \"{datatype}\",\n"

sql_query += ");"

print(sql_query)

The zip() function helps deal with two (or more) iterables simultaneously without using range() and len() to create a counter.

---

Related Topics