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
Connection objects. Each open SQLite database is represented by a Connection object, which is created using sqlite3. connect() . Their main purpose is creating Cursor objects, and Transaction control.
fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.
The sqlite3. connect() function returns a Connection object that we will use to interact with the SQLite database held in the file aquarium.
SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.
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']}.")
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With