how can i return a SQL query results in flask GET API

Question

I have the following Flask Python3.7 API

when I do a curl call

curl --location --request GET 'http://127.0.0.1:5000/' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic cm9vdDpkZWZhdWx0' \
--data-raw ''

I keep getting

TypeError: <sqlite3.Row object at 0x1104e5f70> is not JSON serializable

How can i get a json response with all the columns in the database when I do a GET API code?

Here is the code

def query_db(query, args=(), one=False):

    cur = get_db().execute(query, args)

    rv = cur.fetchall()

    cur.close()

    return (rv[0] if rv else None) if one else rv


@app.route('/')

def index():

    user = query_db('select * from table', one=True)

    return jsonify(user)

However, if i changed into

def index():

    user = query_db('select * from mouse_tracking', one=True)

    return json.dumps(user["user_id"]), json.dumps(user["date"])

I will return the data in the user_id column, but not the date column data

Any thoughts? Thanks!

Mangisto · Accepted Answer · 2020-07-03 19:27:32Z

1

Flask does not support multiple return values, you can use dumps and tuple

from flask import Flask, jsonify
from json import dumps

app = Flask(__name__)

@app.route("/")
def index():
    return dumps(({"Test": "Test"}, {"Test1": "Test1"}))

app.run("0.0.0.0", 5000)

on request you will get as [{"Hey": "Hey"}, {"Hey1": "Hey2"}]

answered Jul 3, 2020 at 19:27

Mangisto

792 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how can i return a SQL query results in flask GET API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related