2

How do I display the results of my database query out in CocoaRestClient using python flask?

Here's the code:

import json
import sys
import datetime
import MySQLdb as mdb
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/home')
def hello_world():
    return "Welcome to Omnimoda."

@app.route('/items/list')
def get_items():
    #Change accordingly:
    #Connection Details:
    hostname = "localhost"
    username = "un"
    password = "pw"
    database = "db"
    q_list_one = "SELECT * FROM item_info"
    con = mdb.connect(hostname, username, password, database)
    cur = con.cursor()
    json_return = {}
    try:
        cur.execute(q_list_one)
        r_list_one = cur.fetchall()
    except Error as error:
        print(error)
    finally:
        cur.close()
        con.close()

    return jsonify(r_list_one)

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

I got the jsonify bit from here, but all I'm getting is a ValueError: dictionary update sequence element #0 has length 6; 2 is required(full error traceback here)

Without the jsonify, I get a TypeError: 'tuple' object is not callable.

Here's what the database looks like:

Anything else I can try? Thanks.

enter image description here

Community
  • 1
  • 1
zack_falcon
  • 4,186
  • 20
  • 62
  • 108
  • Which line did the the error emerge? I think a full tracestack would help. – ljk321 Jun 03 '15 at 08:05
  • @skyline75489, I added the entire error message as a link to pastebin. – zack_falcon Jun 03 '15 at 08:11
  • 1
    @zack_falcon: Please don't add extra info to separate websites. Question should be self-contained. – plaes Jun 03 '15 at 08:28
  • Does this answer your question? [How to return data from a MySQL query in a Flask app?](https://stackoverflow.com/questions/21709992/how-to-return-data-from-a-mysql-query-in-a-flask-app) – questionto42 Feb 01 '22 at 00:21

3 Answers3

7

You are using jsonify wrong. You'll need to use it with keyword argument, just like in the answer from the link your refered to:

return jsonify(data=r_list_one)

The official doc gives an example:

return jsonify(username=g.user.username,
               email=g.user.email,
               id=g.user.id)

Above code will results in a response like this:

{
    "username": "admin",
    "email": "admin@localhost",
    "id": 42
}

So you can see that the keywords are necessary when using jsonify.

ljk321
  • 16,242
  • 7
  • 48
  • 60
  • FYI--this is now fixed in Flask so that `jsonify()` works on tuples: pallets/flask#1671 Docs here: http://flask.pocoo.org/docs/dev/api/#flask.json.jsonify – Jeff Widman May 23 '16 at 22:12
1

You can Use json.dumps(). In your imports do this.

import json

and in your return use it as 

return json.dumps(r_list_one)
faizan shafiq
  • 31
  • 7
0

You might consider (untested) ideas from Output pyodbc cursor results as python dictionary.

There, you also get a sequence and each row value must then be mapped to the column headers as the dictionary keys.

That would be reached for each row separately with:

dict(zip([column[0] for column in cursor.description], row))

And the loop over all rows:

[dict(zip([column[0] for column in cursor.description], row))
         for row in cursor.fetchall()]

In your case:

[dict(zip([column[0] for column in cur.description], row)) 
         for row in r_list_one]

You should close the cursor only after this, in the code, it is already closed at the time of return.

Such a dictionary approach should work like a jsonify() since json is saved in a dictionary format.

This saves the headers of each column for each value of each row. It is actually a waste of memory. If you know the column names and their position anyway, you can gain some speed and save some memory if you only ask for the column number to get a value of a row's column, like row[1] for the second column.

Saving each row as a dictionary with its headers is quite an overhead.

questionto42
  • 7,175
  • 4
  • 57
  • 90