As a pet project I have been writing my own ORM to help me better understand the decisions made by production grade ORMs like Peewee or the more complex sqlalchemy.
In line with my titles question, is it better to spawn one cursor and reuse it for multiple SQL executions or spawn a new cursor for each transaction?
I've already guessed about avoid state issues (transactions with no commit) but is there another reason why it would be better to have one cursor for each operation (insert, update, select, delete, or create)?
Have you profiled and found that the creation of cursors is a significant source of overhead?
Cursors are a DB-API 2.0 artifact, not necessarily an actual "thing" that exists. They are designed to provide a common interface for executing queries and handling results/iteration. How they are implemented under-the-hood is up to the database driver. If you're aiming to support DB-API 2.0 compatible drivers, I suggest just use the cursor() method to create a cursor for every query execution. I would recommend to NEVER have a singleton or shared cursor.
In SQLite, for example, a cursor is essentially a wrapper around a sqlite3_stmt object, as there's no such thing as a "sqlite3_cursor". The stdlib sqlite3 driver maintains an internal cache of sqlite3_stmt objects to avoid the cost of compiling queries that are frequently used.
What are the side-effects of reusing a sqlite3 cursor?