Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

python

sql

sqlite

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
like image 386
Vaish MK Avatar asked May 16 '17 18:05

Vaish MK


People also ask

What is the purpose of a connection object in sqlite3?

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.

What is the purpose of the Fetchall () method of a sqlite3 database cursor?

fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.

What type of object does the sqlite3 connect function return?

The sqlite3. connect() function returns a Connection object that we will use to interact with the SQLite database held in the file aquarium.

How fetch data from sqlite3 database in Python?

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.


2 Answers

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']}.")
like image 152
Alex Fedotov Avatar answered Oct 21 '22 17:10

Alex Fedotov


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).

like image 39
juanpa.arrivillaga Avatar answered Oct 21 '22 16:10

juanpa.arrivillaga