Figure 1 below shows a simple customer table in SQLite:
|
1 2 3 4 |
CREATE TABLE "customer" ( "Id" INTEGER PRIMARY KEY AUTOINCREMENT, "Name" TEXT ); |
Figure 1. The table this article uses.
Consider the Python code below to connect to an SQLite database an iterate over a result set.
|
1 2 3 4 5 6 7 8 9 10 |
import sqlite3 sqlitedb = 'downloads.sqlite' conn = sqlite3.connect(sqlitedb) cursor = conn.cursor() result = cursor.execute('SELECT * FROM customer') rows = result.fetchall() for row in rows: print(row[1]) conn.close() |
Figure 2a. By default, SQLite cursor.execute returns a list of tuples.
By default, the cursor.execute method resolves to a list of tuples where each tuple contains field values. Field columns queried this way cannot be read by field name--you must use the ordinal position of the column in the tuple to get its value. However, if you add this line
|
1 |
conn.row_factory = sqlite3.Row |
after establishing the connection, rows resolves to list of SQLite Row objects. Data columns represented by Row objects can be fetched by either ordinal position (like a list) or by using the field name as a key (like a dictionary). Like a dictionary you can also uses its keys() method to get the list of field names.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
import sqlite3 sqlitedb = 'downloads.sqlite' conn = sqlite3.connect(sqlitedb) # Assign the row factory: conn.row_factory = sqlite3.Row cursor = conn.cursor() result = cursor.execute('SELECT * FROM customer') rows = result.fetchall() for row in rows: print(row[1]) // Works print(row['Name']) // Works print(row['name']) // Works print(row.keys()) // Works conn.close() |
Figure 2b. Using the SQLite Row factory object gets you row results fetchable by field name.
The Row object is more forgiving than a simple Python dictionary because its key-based access isn't case-sensitive (as shown above).
The SQLite docs recommend you use the built-in Row factory object because of the case-insensitivity and minimal memory overhad (as shown above). But, if you want result rows as simple Python dictionaries, you can provide your own dictionary factory like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d import sqlite3 sqlitedb = 'downloads.sqlite' conn = sqlite3.connect(sqlitedb) conn.row_factory = dict_factory cursor = conn.cursor() result = cursor.execute('SELECT * FROM customer') rows = result.fetchall() for row in rows: print(row[1]) // Does not work print('Name') // Works print('name') // Does not work print(row.keys()) // Works. conn.close() |
Figure 2c. Using a custom factory to get row results as a pure dictionary.
Beware, though, that keyed access is now case-sensitive and you can't fetch values by their ordinal position. In most cases, it's probably best to use SQLite's built-in Row factory object as shown in Figure 2b.
