How to Convert sqlite3 Rows into Python Objects

Today, I have a quick one for you, based on a recent problem I had. If you’ve ever used sqlite3 to work with a local database in a Python project, then you’ve probably had this question yourself: how do you convert a sqlite3 row into a Python object?

Table of Contents

Making Sense of sqlite3

First things first, let’s talk about how sqlite3 works. Basically, it’s a really thin SQLite wrapper. We provide the path to a SQLite file, and the library lets us execute SQL commands as strings on that database.

import sqlite3
connection = sqlite3.connect("data.db")

Now let’s imagine we have an established database with data already in it. Chances are we want to make a series of queries to add, update, and get data. That’s easy enough! We make a cursor object and write out our query.

cur = connection.cursor()
cur.execute("INSERT INTO users (name) VALUES (?)", ("Jeremy",))

Of course, things get a little tricky when we want to get data from the database. Sure, we can write a normal SELECT query, but how exactly does that data come back? In sqlite3, we have to run our query and fetch the result from our cursor object. Any ideas how that data is formatted? Just a list of tuples.

cur = connection.cursor()
users: list[tuple] = cur.execute("SELECT * FROM users").fetchall()

Personally, I didn’t quite care for this because there’s no information in a tuple to tell me what value is at each index. As a result, we have to go back to our table structure and query to figure out exactly what information is at each index. To me, this is a huge problem. In no time, our code is going to be packed full of magic numbers.

for row in users:
    print(f"User ID: {row[0]}")

Now, fortunately, the developers of sqlite3 were aware of this problem, so they exposed a row factory. With the row factory, we can dictate exactly how we want the rows to be returned. Of course, pretty much everyone says not to use this feature since it’s inefficient, so what do we do?

Well, one option is to use one of the provided row factories for sqlite3, Row. Instead of storing each row as a tuple, this row factory stores all rows as a Row object. The Row object is quite nice as it lets us continue using regular indices as not to break existing code. Even better, we can treat the Row as a case insensitive dictionary. In other words, as long as we spell our column names correctly, we can use any casing to retrieve that exact cell of data.

connection.row_factory = sqlite3.Row
cur = connection.cursor()
users: list[sqlite3.Row] = cur.execute("SELECT * FROM users").fetchall()

for row in users:
    print(f"User ID: {row['user_id']}")

However, to me, this is still very error prone. Luckily, there are other options.

Converting Rows to Custom Objects

While the code is significantly more readable, a new problem arises: spelling. For that, I have a somewhat straightforward solution: data classes. For every type of data we want to retrieve, we can model it using a data class.

from dataclasses import dataclass

@dataclass
class User:
    user_id: int
    discord_id: int
    nickname_id: int
    arena_score: int

Then, when we retrieve our list of rows, we repackage them as the appropriate model class. The perk here is that our main application doesn’t even have to know our code uses a database. We’re just tossing around data models. Of course, the downside is the potential performance drop. For my purposes, this didn’t really matter, and I’ll gladly take the readability and type checking benefits any day of the week.

connection.row_factory = sqlite3.Row
cur = connection.cursor()
users: list[User] = cur.execute("SELECT * FROM users").fetchall()

for row in users:
    user: User = User(*row)
    print(f"User ID: {row.user_id}")

And if we want to simplify the translation step, we can actually set the data model per cursor, so we always get the right data:

cur = connection.cursor()
cur.row_factory = lambda cursor, row: User(*row)
users: list[User] = cur.execute("SELECT * FROM users").fetchall()

for row in users:
    print(f"User ID: {row.user_id}")

Now, that’s pretty cool! With that said, that’s about all I have time to cover today. This article is already late! If you like this and want more details, feel free to let me know. Otherwise, check out some of these related articles:

• I Finally Figured Out Python’s Module and Package System
• How to Automatically Generate Fitbit Access Tokens Using Python
• The Complete Guide to SnakeMD: A Python Library for Generating Markdown

Well, take care!