Set Sqlite query results as variables

Question

Issue:

Hi, right now I am making queries to sqlite and assigning the result to variables like this:

Table structure: rowid, name, something

cursor.execute("SELECT * FROM my_table WHERE my_condition = 'ExampleForSO'")
found_record = cursor.fetchone()

record_id = found_record[0]
record_name = found_record[1]
record_something = found_record[2]
print(record_name)

However, it's very possible that someday I have to add a new column to the table. Let's put the example of adding that column:

Table structure: rowid, age, name, something

In that scenario, if we run the same code, name and something will be assigned wrongly and the print will not get me the name but the age, so I have to edit the code manually to fit the current index. However, I am working now with tables of more than 100 fields for a complex UI and doing this is tiresome.

---

Desired output:

I am wondering if there is a better way to catch results by using dicts or something like this:

Note for lurkers: The next snipped is made up code that does not works, do not use it.

cursor.execute_to(my_dict, 
                  '''SELECT rowid as my_dict["id"], 
                     name as my_dict["name"], 
                     something as my_dict["something"] 
                     FROM my_table WHERE my_condition = "ExampleForSO"''')

print(my_dict['name'])

I am probably wrong with this approach, but that's close to what I want. That way if I don't access the results as an index, and if add a new column, no matter where it's, the output would be the same.

What is the correct way to achieve it? Is there any other alternatives?

Answer 1

You can use namedtuple and then specify connection.row_factory in sqlite. Example:

import sqlite3
from collections import namedtuple

# specify my row structure using  namedtuple
MyRecord = namedtuple('MyRecord', 'record_id record_name record_something')

con = sqlite3.connect(":memory:")
con.isolation_level = None
con.row_factory = lambda cursor, row: MyRecord(*row)

cur = con.cursor()

cur.execute("CREATE TABLE my_table (record_id integer PRIMARY KEY, record_name text NOT NULL, record_something text NOT NULL)")
cur.execute("INSERT INTO my_table (record_name, record_something) VALUES (?, ?)", ('Andrej', 'This is something'))
cur.execute("INSERT INTO my_table (record_name, record_something) VALUES (?, ?)", ('Andrej', 'This is something too'))
cur.execute("INSERT INTO my_table (record_name, record_something) VALUES (?, ?)", ('Adrika', 'This is new!'))

for row in cur.execute("SELECT * FROM my_table WHERE record_name LIKE 'A%'"):
    print(f'ID={row.record_id} NAME={row.record_name} SOMETHING={row.record_something}')

con.close()

Prints:

ID=1 NAME=Andrej SOMETHING=This is something
ID=2 NAME=Andrej SOMETHING=This is something too
ID=3 NAME=Adrika SOMETHING=This is new!