Allow customization of cursor objects about apsw

[rogerbinns]

From rogerbinns on November 26, 2008 12:26:01

See discussion about this on the mailing list. Cursors only have two methods -
execute and executemany. Consequently you can trivially make a proxy object. You
can implement fetchall just using list:

def fetchall(cursor): return list(cursor)

However it seems reasonable to have a fetchall method so I will add that to the cursor.

Status: Accepted
Labels: -Type-Defect Type-Enhancement

[rogerbinns]

From rogerbinns on November 27, 2008 00:53:40

cursor.fetchall added in r344 . Do you have any other use cases?

Status: Started

[rogerbinns]

From pavlos.casa on November 27, 2008 01:24:38

Thanks Roger,

APSW is simple. Keep it like this! No need to add anything to the cursor object
unless of course you think its a good idea.

Here is what motivated my suggestion. We have a large scale python application that
we would like to run on sqlite (for development, testing, and smaller deployments).
The database layer, though abstracted in a few modules, makes heavy use of cursors
and their associated methods. It was easy to subclass the apsw.Connection object to
provide an interface expected by the database modules but not so easy for the cursor
object. In effect one needs to create a new cursor class that holds a reference to
the cursor returned from apsw.Connection and dispatch methods to it appropriately.
It's not a problem really to do it like this but I thought to pass the info back to
you as it may useful.

Regards

[rogerbinns]

From rogerbinns on November 27, 2008 01:30:19

That exact discussion was on the mailing list :-) Yes you need to do inheritance
basically by encapsulation but as there are so few cursor methods it is easy.
fetchall was arguably a nice method to have even though it is longer to type than list. http://itsystementwicklung.de/pipermail/list-pysqlite/2008-November/000219.html What other methods have you found you need to add?

[rogerbinns]

From pavlos.casa on November 27, 2008 01:33:06

Rereading my comment I think I have not clarified enough. In our application, and I
expect in many large scale applications there are calls like:

cursor = connection.cursor()
cursor.execute(...)
cursor.fetchmany() ... etc etc.

though the connection can be subclassed nicely to provide whatever interface required
the cursor must be wrapped in a proxy object. Which is ok really but if apsw had a
standard way for users to define their own custom cursor objects then the one
provided by apsw can be kept very simple and to the spirit of the apsw design.

[rogerbinns]

From pavlos.casa on November 27, 2008 01:33:52

and in any case thanks for a great library

[rogerbinns]

From pavlos.casa on November 27, 2008 01:53:16

Thanks for pointing me to the discussion. Indeed it's the same request.

FYI we had to add the following methods:

fetchone
fetchmany
rowcount

[rogerbinns]

From rogerbinns on November 27, 2008 02:03:24

(You are welcome for the library :)

I understand exactly what you are doing and the needs. In fact it is exactly how I
came up with the API in the first place. I was initially using pysqlite and wrapping
its cursor to do things like be an iterator, have tracing etc. After deciding to do
my own thing with APSW those are the methods I implemented. However my own needs are
unlikely to be 100% exact which is why I want to find out what methods you need, and
use my judgement as to whether they should be added. I would like APSW to be 99%
applicable to everyone out of the box and then leave the remaining special needs to
the people who are best able to address them. If your underlying goal is DBAPI
compliance then ask on the mailing list - Ed Pasma made some sort of wrapper that
does that mostly. However SQLite does not operate the same way DBAPI thinks databases.

Making a 'factory' for cursors would actually be quite difficult due to behind the
scenes plumbing which is why the suggested way is to wrap using a proxy object. I'll
add something to the documentation about it, as well as doing some experimentation on
other approaches.

Your specific example above of a fetchmany method is pointless with SQLite. It takes
N times effort to get N rows as it does to get one. There is no underlying network
transport or any benefit to batching results. I'd suggest using the cursor as an
iterator which leads to a far nicer programming style and modify the other libraries
(MySQL, Oracle etc) to make their cursors behave as iterators (under their hood you'd
need fetchmany and friends.) rowcount is really pointless for SQLite. It calculates
every row and doesn't know in advance how many there are. It is the same effort as
len(cursor.fetchall()).

It is hard decision (for you) as to whether you keep smoothing over SQLite until it
looks like other databases, or have alternate code that works with it the way it does.

[rogerbinns]

From pavlos.casa on November 27, 2008 02:14:58

Thanks for the feedback. Understood. Will provide more feedback as we go along should
we think it's useful.

[rogerbinns]

From rogerbinns on December 05, 2008 14:55:49

3.6.6.2- r1 documentation now includes a tips section, one of which is about
customizing the cursor. http://apsw.googlecode.com/svn/publish/tips.html#customizing-cursors I assume that is sufficient for now :)

Status: Verified