How can I configure an SQL select statement in such a way that, in addition to the selected contents, I also receive their column names in the result?

Question

The following initial situation:

I have a Python script with which I fetch data from SQLite databases via SELECT statements.

For example: sql_statement = ''' SELECT * FROM results WHERE id=1 '''
Output: [(1, '2022-05-01 12:00:00', 'test')]

Target:

But I want my output to include the names of the database columns, so the output should look like this, for example:

[(id=1, date='2022-05-01 12:00:00', content='test')]
or [(id:1, date:'2022-05-01 12:00:00', content:'test')]
or [([id,1], [date,'2022-05-01 12:00:00'], [content,'test'])]

It is only important to me that it is a combination of table column names and their selected value (no matter whether as tuple, list, JSON, ...).

Question:

How do I adjust my SQL statement so that I get the desired result without having permanently changed row_factory?

Thank you very much for your help!

Addition:

I have already looked at PRAGMA table_info('table_name'), .headers on and also .mode column, but unfortunately they did not help me with my solution. I would like to do this for a single query and therefore not change the row_factory if possible.

Possible ideas as approaches

A theoretical idea would also create two statments, which are then aggregated, e.g.

SELECT * FROM results WHERE id=1
with
SELECT 'name' FROM PRAGMA table_info('results')

python code

import sqlite3

database = "db.db" 
db_conn = sqlite3.connect(database) 
db_cursor = db_conn.cursor()

id=1 
sql_statement = ''' SELECT * FROM results WHERE id=''' + str(id) + ''' ''' 
db_cursor.execute(sql_statement) 
fetch_result = db_cursor.fetchall() 

db_conn.close() 

print(fetch_result)

sql code

CREATE TABLE "results" (
    "id"    INTEGER NOT NULL UNIQUE,
    "date"  TEXT NOT NULL,
    "content"   TEXT NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
)

please provide your code and the library you use to execute SQL queries by Python. — Hadi Hajihosseini, Apr 30 '23 at 13:51
Possible duplicate (or at least very related): https://stackoverflow.com/questions/3300464/how-can-i-get-dict-from-sqlite-query — DeepSpace, Apr 30 '23 at 13:52
Thank you @DeepSpace! I will try the suggested solution right away. I hadn't found this one during my research. — DevArchitectMaster, Apr 30 '23 at 14:00
... sorry for the mess, unfortunately that messed up the formatting.... I'll post the source code again without markdown, then it should work @nbk — DevArchitectMaster, Apr 30 '23 at 14:15
import sqlite3; database = "db.db"; db_conn = sqlite3.connect(database); db_cursor = db_conn.cursor(); id="1"; sql_statement = ''' SELECT * FROM results WHERE id=''' + id + ''' '''; db_cursor.execute(sql_statement); fetch_result = db_cursor.fetchall(); db_conn.close(); print(fetch_result); — DevArchitectMaster, Apr 30 '23 at 14:15
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Apr 30 '23 at 14:23
@nbk Sorry, it's my first question (but that's not meant to be a justification or excuse for asking a bad question). I have revised my question and hopefully added all the necessary data so that my scenario can be reproduced. If any information is missing, please let me know and I will add it as soon as possible. — DevArchitectMaster, Apr 30 '23 at 14:44
have you actually read the link from @DeepSpace there are multiple soltions for that problem, best i found was https://stackoverflow.com/a/55986968/5193536 — nbk, Apr 30 '23 at 14:55
Yes, I read the link from @DeepSpace, but the database connection is modified, i.e. all further queries then also have the column names in them. I am looking for a solution where only one query sends the column names and all other queries run according to the "default" configuration. Do you understand what I mean @nbk? — DevArchitectMaster, Apr 30 '23 at 15:08
`print(dict(zip([item[0] for item in db_cursor.description], db_cursor.fetchall())))` — roganjosh, Apr 30 '23 at 15:15
It shouldn't do. As far as I know, the DB API carries `.description` by default. The fact that you have to take the `[0]` index is because of some strange specification where the cursor carries, IIRC, 7 values by default for each field — roganjosh, Apr 30 '23 at 15:18
@DeepSpace the property is baked into the [DB API spec](https://peps.python.org/pep-0249/#cursor-attributes) — roganjosh, Apr 30 '23 at 15:29

score -1 · Accepted Answer · answered Apr 30 '23 at 15:37

You can use the .description attribute of the cursor if you don't want to set up a factory. This attribute is part of the official Python DB API and should always be available for any bindings.

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()

c.execute("""
    CREATE TABLE testing (
        id INTEGER,
        value VARCHAR
    )
    """)
conn.commit()

c.executemany("""
    INSERT INTO testing VALUES (?, ?)
    """, [[1, 'hello'], [2, 'bye']])

conn.commit()

c.execute("SELECT * FROM testing")
cols = [item[0] for item in c.description]
print([dict(zip(cols, row)) for row in c.fetchall()])

c.close()
conn.close()