Using SQLite’s Row object with Python

Figure 1 below shows a simple customer table in SQLite:

Figure 1. The table this article uses.

Consider the Python code below to connect to an SQLite database an iterate over a result set.

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

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.

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:

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.

Leave a Comment

Your email address will not be published. Required fields are marked *