Allow to use many cursors from the same database connection
Actually the Transaction concept merge both concept of connection and cursor from the PEP0249.
Indeed I think the backend should be refactored to be closer to the PEP0249 API which is the one that all database modules follow.
Having many cursors from the same connection will allow to replace fetchall call by iteration on the cursors and thus save some memories.
The backend API could be:
connect: which return a connection to the database name
create, drop, dump, restore, list, init and test: to manage database
nextid, setnextid, currid, lock: which preform the custom query
has_constraint, has_returning, has_multirow_insert: which test the database capabilities
The cursor.get_cache should be moved to Transaction.