- Flask Introduction
- Row Factory in SQLite
- Flask Authentication
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>