python - Return data from sqlite with headers python3

Question

So this should be very simple and straightforward but for some reason I can't find anything that does this.

I want to return my Sqlite response from server to the frontend formatted as json (or at least a dict).

In order to do that, I want my information to look like:

{{headerName1: information, headerName2: information...}, {headerName1: information, headerName2:information...}}

Which seems like a reasonable request.

So far every solution I see to retrieve the headers' names are meant for one-time usage; e.g. .headers ON or cursor.description or PRAGMA table_info(tablename).

All I want is to be able to do SELECT ... FROM ... and my return value would contain the headers as well (assume I'm reading a whole table and not just one line or column).

How can I do this? (using python3)

question from:https://stackoverflow.com/questions/65934371/return-data-from-sqlite-with-headers-python3

Answer 1

First retrieve headers from cursor.description, then get results iterating over cursor's rows:

headers = list(map(lambda attr : attr[0], cursor.description))
results = [{header:row[i] for i, header in enumerate(headers)} for row in cursor]