What is the purpose of the row_factory method of an sqlite3 Connection object?

Question

Somehow the docs didn't make it too clear for me. I'm having trouble understanding the purpose of row_factory method of an sqlite3 Connection object.

Basically, could you explain the following snippet?

def connect_db():
    """Connects to the specific database."""
    rv = sqlite3.connect(app.config['DATABASE'])
    rv.row_factory = sqlite3.Row
    return rv

Solution 1

The line of code assigning sqlite3.Row to the row_factory of connection creates what some people call a 'dictionary cursor', - instead of tuples it starts returning 'dictionary' rows after fetchall or fetchone.

The pretty standard example:

import sqlite3 as sqlite

conn = sqlite.connect('companies_db.sqlite')

with conn:
    conn.row_factory = sqlite.Row
    curs = con.cursor()
    curs.execute("SELECT * FROM companies_table")
    rows = curs.fetchall()
    for row in rows:
        print(f"{row['companyid']}, {row['name']}, {row['address']}.")

Solution 2

From the sqlite3 docs:

You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.

Further:

If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting row_factory to the highly-optimized sqlite3.Row type. Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

You can find the same sort of explanation in the Flask docs.

So this line:

rv.row_factory = sqlite3.Row

Sets the row_factory to the callable sqlite3.Row, which converts the plain tuple into a more useful object.

So now, when you pull rows from your database, you won't get back a plain python tuple, but a special object that makes it easier to work with (e.g. allowing you to access columns using names whereas a plain tuple would make you use numbered indices).