db.query() method (renamed db.execute_returning_dicts())

[simonw]

Most of this library deals with lists of Python dictionaries - .insert_all(), .rows, .rows_where(), .search().

The db.execute() method is the only thing that returns a sqlite3 cursor.

There is a clumsily named db.execute_returning_dicts(sql) method but it's not currently mentioned in the documentation.

It needs a better name, and needs to be properly documented.

[simonw]

Potential names:

• db.query(sql) - it's weird to have both this and db.execute() but it is at least short and memorable
• db.sql(sql)
• db.execute_d(sql) - ugly
• db.execute_dicts(sql) - confusing
• db.execute_sql(sql) - easily confused with db.execute(sql)

I think db.query(sql) may be the best option here.

[simonw]

One small problem with the existing method:

sqlite-utils/sqlite_utils/db.py

Lines 362 to 365 in 8cedc6a

	def execute_returning_dicts(self, sql, params=None):
	cursor = self.execute(sql, params or tuple())
	keys = [d[0] for d in cursor.description]
	return [dict(zip(keys, row)) for row in cursor.fetchall()]

It returns a full list, but what if the user would rather have a generator they can iterate over without loading the results into memory in one go?

[simonw]

The Python docs say: https://docs.python.org/3/library/sqlite3.html

To retrieve data after executing a SELECT statement, you can either treat the cursor as an iterator, call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.

Looking at the C source code, both fetchmany() and fetchall() work under the hood by assembling a Python list: https://github.com/python/cpython/blob/be1cb3214d09d4bf0288bc45f3c1f167f67e4514/Modules/_sqlite/cursor.c#L907-L972 - see calls to PyList_Append()

So it looks like the most efficient way to iterate over a cursor may well be for row in cursor: - which I think calls this C function: https://github.com/python/cpython/blob/be1cb3214d09d4bf0288bc45f3c1f167f67e4514/Modules/_sqlite/cursor.c#L813-L876

[simonw]

Still needs documentation, which will involve rewriting the whole Executing queries section.

[simonw]

That documentation section is pretty weak at the moment - here's the whole thing:

Executing queries

The db.execute() and db.executescript() methods provide wrappers around .execute() and .executescript() on the underlying SQLite connection. These wrappers log to the tracer function if one has been registered.

db = Database(memory=True)
db["dogs"].insert({"name": "Cleo"})
db.execute("update dogs set name = 'Cleopaws'")

You can pass parameters as an optional second argument, using either a list or a dictionary. These will be correctly quoted and escaped.

# Using ? and a list:
db.execute("update dogs set name = ?", ["Cleopaws"])
# Or using :name and a dictionary:
db.execute("update dogs set name = :name", {"name": "Cleopaws"})

• Talks about .execute() - I want to talk about .query() instead
• Doesn't clarify that .execute() returns a Cursor - and assumes you know what to do with one
• Doesn't show an example of a select query at all
• The "tracer function" bit is confusing (should at least link to docs further down)
• For UPDATE should show how to access the number of rows modified (probably using .execute() there)

It does at least cover the two types of parameters, though that could be bulked out.

[simonw]

The new documentation: https://sqlite-utils.datasette.io/en/latest/python-api.html#executing-queries