How to iterate through the columns in a row using sqlite3.Row

[Peter Otten]

Chris Green wrote:

> MRAB <python at mrabarnett.plus.com> wrote:
>> On 2017-03-12 22:44, Chris Green wrote:
>> > This should be simple but I can't manage it at the moment!  :-)
>> >
>> > I have opened a database connection and have set the row_factory to
>> > sqlite3.Row.
>> >
>> > So how do I actually iterate through a row of data having used
>> > fetchone to read a row.
>> >
>> > I.e. I have:-
>> >
>> >         self.conn = sqlite3.connect(dbname)
>> >         self.conn.row_factory = sqlite3.Row
>> >         self.cursor = self.conn.cursor()
>> >         self.table = table
>> >         ...
>> >         ...
>> >         sql = "SELECT * FROM " + self.table + " WHERE
>> >         firstName||lastName = ?" self.cursor.execute(sql, (name,))
>> >         row = self.cursor.fetchone()
>> >
>> > I want a for loop which gives me the column names and values.
>> >
>> The docs tell you that the sqlite3.Row instances are like tuples, but
>> with additional features, such as a .keys() method that will tell you
>> the column names. Try:
>> 
>> print(row.keys())
>> 
>> With a little experimentation you'll find that can pass it to dict. Try:
>> 
>> print(dict(row))
>> 
> so are you saying that I should be able to write:-
> 
>     rowAsDict = dict(row)

You can either forget about about the custom row_factory and use 
cursor.description

>>> import sqlite3
>>> db = sqlite3.connect(":memory:")
>>> cs = db.cursor()
>>> cs.execute("select 1 foo, 2 bar")
<sqlite3.Cursor object at 0x7f9a5b37f3b0>
>>> names = [col[0] for col in cs.description]
>>> names
['foo', 'bar']
>>> for row in cs:
...     for name, value in zip(names, row):
...         print(name, value, sep=": ")
... 
foo: 1
bar: 2

or use sqlite3.Row and its keys() method:

>>> cs.row_factory = sqlite3.Row
>>> for row in cs.execute("select 1 ham, 2 spam"):
...     for name in row.keys():
...         print(name, row[name], sep=": ")
... 
ham: 1
spam: 2