How to return json from sqlite in flask

Question

I try to return cities from a json list from my database but after executing the code and opening /city endpoint the browser returns "null" not the ["city1", "city2", "city3", ...]

from flask import (
    Flask,
    g,
    redirect,
    render_template,
    request,
    url_for,
    jsonify,
)
import sqlite3

app = Flask(__name__)


DATABASE = 'database.db'


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()     

@app.route('/city')
def city_list():
    db = get_db()
    data = db.execute('SELECT city FROM city').fetchall()

    return jsonify(data.append(list(data)))

Do you have any idea where I made a mistake and how to fix it? When I return this to render_template and a for loop everything is ok.

unfortunately on

return(data)

browser return

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

Please do not edit questions to "update" them with code that fixes the problem. That only confuses future readers. I rolled back the edit. — Karl Knechtel, Sep 14 '22 at 14:38

score 1 · Answer 1 · answered Mar 31 '18 at 10:20

1

.append() always returns None, because it modifies in place.

But I can't see why you're calling append at all; there is nothing to append here. Just return the jsonified data:

return jsonify(list(data))

(In fact you might not even need the list - just jsonify(data) might work.)

answered Mar 31 '18 at 10:20

Daniel Roseman

588,541
66
880
895

1

I update question because I try to return just jsonify(list(data)) / jsonify(data) before but then browser return builtins.TypeError – derriadiergummi Mar 31 '18 at 10:42

How to return json from sqlite in flask

1 Answers1