Send JSON response from Sqlite queries in Python

Question

I have a Python http server which listens to JSON based requests. After receiving the request, it parses the key from the JSON input, and queries Sqlite database which has such a key. Now I want to respond the request with a result JSON message. I am new to Python, and I don't know how.

My code structure is like below:

 import ...

 key=...;//get key from request
 con = lite.connect('test.db')
 with con:
    con.row_factory = lite.Row
    cur = con.cursor()
    cur.execute("SELECT * FROM mytable ");
    while True:

        row = cur.fetchone()

        if row == None:
            break
        if key==row['key']:
            # How can I add the record to the response?

And the handler will write the response like this (in a class inherit BaseHTTPRequestHandler and started by a thread)

self.send_response(200)
self.send_header('Content-Type', 'application/json')
self.end_headers()
self.wfile.write(??????) # What do I need to write here?

@gosom How can I organize such a dict from the sqlite result/rows? I use LocalData.records[row[0]]=row to store each matching row, and self.wfile.write(json.dumps(LocalData.records)) to write to the output. It says "... is not JSON serializable". — mrmoment
– mrmoment, Commented Aug 29, 2014 at 8:54

Aziz · Accepted Answer · 2021-08-31 00:19:49Z

6

you can try this

import sqlite3


def row_to_dict(cursor: sqlite3.Cursor, row: sqlite3.Row) -> dict:
    data = {}
    for idx, col in enumerate(cursor.description):
        data[col[0]] = row[idx]
    return data

with sqlite3.connect(db_path) as con:
    con.row_factory = row_to_dict
    result = con.execute('SELECT * FROM table_name')
    print(result.fetchall())

edited Aug 31, 2021 at 0:19

answered Aug 30, 2021 at 23:10

Aziz

911 silver badge5 bronze badges

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

Comments

freakish · Accepted Answer · 2014-08-29 08:52:28Z

5

Returning JSON response is as easy as that:

import json
import sqlite3

def get_my_jsonified_data(key):
    with sqlite3.connect('test.db') as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM mytable WHERE column=?;", [key])
        data = cursor.fetchall()
        return json.dumps(data)

(assuming that lite is an alias for sqlite3)

Note few other things:

I've removed while True: loop. It's horribly inefficient, insecure and harder to read;
I've added check for key inside the SQL query (why would you want to load the unnecessary data from DB anyway?)

answered Aug 29, 2014 at 8:52

freakish

57k12 gold badges141 silver badges181 bronze badges

10 Comments

mrmoment Over a year ago

Thanks. For the checking for key, it is my fault as I am adding the codes piece by piece so that's not necessary obviously (just use sql). Another question is that fetchall() could have problems (memory, response size, etc.) if the result is big?

freakish Over a year ago

@mrmoment Unless you are doing a streaming (which doesn't look like that) then you have to load all the data into memory anyway. And if you are doing a streaming then use cursor.fetchamany(10000) for example. Fetching item by item is horribly inefficient.

mrmoment Over a year ago

Got it. I think I can use "limit" clause to reduce the results for fetchall(). Thanks again.

Prahlad Yeri Over a year ago

When I try this in Python3, I get TypeError: Object of type 'Row' is not JSON serializable on json.dumps(data). Do you need to configure anything else?

freakish Over a year ago

@PrahladYeri It seems that you are using conn.row_factory = sqlite3.Row somewhere. In that situation you can try json.dumps([tuple(row) for row in data]).

|

Thomas Freedman · Accepted Answer · 2023-06-12 04:18:27Z

Still don't have enough points to comment apparently. @freakish - yes I'm using conn.row_factory = sqlite3.Row to get the results as python dictionary and yes your solution does work, but it doesn't encode the result as a list of dictionaries using the column names as I need.

The problem, in essense, is that sqlite returns sqlite objects that json.dumps can't deal with. I need a way to create an output like this for 3 rows:

{
'json': [
         {'column1': value, 'column2': value, 'column3': value},
         {'column1': value, 'column2': value, 'column3': value},
         {'column1': value, 'column2': value, 'column3': value}
        ]
}

I believe this will do it, but not as cleanly as your 1 liner :)

rows = []
for row in data:
    cols = {}
    for col in row.keys():
        cols[col] = row[col]
    rows.append(cols)
jsn = json.dumps(rows)

Collectives™ on Stack Overflow

Send JSON response from Sqlite queries in Python

3 Answers 3

Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related