How to iterate through the columns in a row using sqlite3.Row
Peter Otten
__peter__ at web.de
Mon Mar 13 06:26:19 EDT 2017
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
More information about the Python-list
mailing list