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?

Answer 1

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:

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

Answer 2

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())