How to convert a "Sqlite3 Object cursor" to a list (Python)

Question

In a cell of a table in my database I have a list of pairs. I have a module in Python that I want you to fetch that list from the database and read it as a list. I tried to run this:

interpretation = db.execute("SELECT interpretation FROM music WHERE ID = ?", (id, ))

I’ve tried to do list(interpretation), eval(interpretation), send the list to the database converted into JSON, and decode in the module that will fetch these values, but nothing worked. The problem is therefore simple: I want mine Interpretation, which is interpreted as a "Sqlite3 Object cursor" be interpreted as a list.

Answer 1

The call to the method execute - both a database connection, and a Python cursor object does not return results immediately. This is done on purpose, so that it is easier to create applications where the bank works asynchronously. (This is not the case for Sqlite, which runs in the same process - but other banks are triggered by the network layer, and can return the values in the form "Lazy")

To recover the results of a query, methods are used fetch and fetchall - or you can simply use the cursor as an iterator for: it will return the results:

cursor = db.cursor()
cursor.execute("SELECT interpretation FROM music WHERE ID = ?", (id, ))
for result in cursor:
   print(result)[0]

(in the case, "result" is a tuple with the values returned in each row of the query, If the query should return 16 columns, it sets a tuple containing the 16 values, with indexes between 0 and 15)

If you want to just throw the values into a data structure, as it is in the question - it is a little less efficient than using the for direct (mainly because you will temporarily duplicate the data in memory) - you should be able to do it simply:

cursor = db.execute(...)
results = list(cursor)  

as you put it. Only each element of the list is a tuple, with all your results - even if you only have one column. So it’s a tuple with a single element - and you can extract it with the list comprehension syntax like this:

results = [line[0] for line in cursor]