Row Factory in SQLite

 in Python  tagged Flask by
This entry is part 2 of 2 in the series Flask

Flask

In SQLite with Python, query results are tuples by default. row_factory transforms each row into a dictionary-like object, making your code more readable. To say this again, by default, query results from fetchall() in SQLite are: A list of tuples. Each row is a tuple, and the entire result set is a list.

Without row_factory 

conn = sqlite3.connect("my.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM Friends")
rows = cursor.fetchall()

for row in rows:
    print(row[1])  # Access by index

With row_factory 

conn = sqlite3.connect("my.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM Friends")
rows = cursor.fetchall()

for row in rows:
    print(row["FirstName"])  # Access by column name

Flask Best Practice – Reusable connection helper: 

def get_db_connection():
    conn = sqlite3.connect("my.db")
    conn.row_factory = sqlite3.Row
    return conn
# route using it:
@app.route("/")
def index():
    conn = get_db_connection()
    friends = conn.execute("SELECT * FROM Friends").fetchall()
    conn.close()
    return render_template("index.html", friends=friends)

In Templates (Jinja2) 

<!-- With row_factory: -->
{% for friend in friends %}
  <li>{{ friend['FirstName'] }} {{ friend['LastName'] }}</li>
{% endfor %}
<!-- Without row_factory: -->
{% for friend in friends %}
  <li>{{ friend[1] }} {{ friend[2] }}</li>
{% endfor %}

A More Full Example

Here is some code that you could use to actually set this up. The code below will help you build your SQLite database friends.db. 

CREATE TABLE Friends (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL
);

INSERT INTO Friends (FirstName, LastName) VALUES
('Alice', 'Smith'),
('Bob', 'Johnson'),
('Charlie', 'Brown');

Here is your Flask Python file. 

from flask import Flask, render_template
import sqlite3
app = Flask(__name__)
DATABASE = 'friends.db'
# conn.row_factory = sqlite3.Row tells SQLite to return rows as dictionary-like objects.
# This lets you do friend['FirstName'] in Jinja instead of friend[1].
def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row  # This is the key line
    return conn
    
def get_no_factory_conn():
    conn = sqlite3.connect(DATABASE)
    return conn
    
@app.route("/")
def index():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Friends")
    friends = cursor.fetchall()
    conn.close()
    return render_template("index.html", friends=friends)

@app.route("/no_factory")
def no_factory():
    conn = get_no_factory_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Friends")
    friends = cursor.fetchall()
    conn.close()
    return render_template("no_factory.html", friends=friends)
    
if __name__ == "__main__":
    app.run(debug=True)

index.html and 

<!DOCTYPE html>
<html>
<head>
    <title>My Friends</title>
</head>
<body>
    <h1>List of Friends (using row_factory)</h1>
    <ul>
        {% for friend in friends %}
            <li>{{ friend['FirstName'] }} {{ friend['LastName'] }}</li>
        {% endfor %}
    </ul>
	<a href="{{ url_for('no_factory') }}">Friends - no factory</a>
</body>
</html>

Row factory

<!DOCTYPE html>
<html>
<head>
    <title>My Friends</title>
</head>
<body>
    <h1>List of Friends (using row_factory)</h1>
    <ul>
        {% for friend in friends %}
            <li>{{ friend['FirstName'] }} {{ friend['LastName'] }}</li>
        {% endfor %}
    </ul>
	<a href="{{ url_for('no_factory') }}">Friends - no factory</a>
</body>
</html>

No factory

<!DOCTYPE html>
<html>
<head>
    <title>My Friends</title>
</head>
<body>
    <h1>List of Friends - no factory</h1>
    <ul>
        {% for friend in friends %}
            <li>{{ friend[1] }} {{ friend[2] }}</li>
        {% endfor %}
    </ul>
	<a href="{{ url_for('index') }}">Friends - factory</a>
</body>
</html>
Series Navigation<< Flask Introduction

Leave a Reply