Support for the apsw SQLite driver

[Paul Harrington]

I would like to be able to use the backup API (and other SQLite APIs) from SQLAlchemy. Mike's post suggests that this should be fairly straightforward:

http://stackoverflow.com/questions/1072444/use-sqlites-backup-api-from-python-sqlalchemy

However, I think that this presupposes a DBAPI-compliant driver, no?

Roger Binns does not appear to be inclined to provide a DBAPI head http://code.google.com/p/apsw/issues/detail?id=107#c2

I did some experiments with wrapping apsw and will work through all the failing SA unit-tests.

pjjH

[Mike Bayer]

if its it DBAPI compliant, that makes the work much harder. It means the dialect would have to adapt the functions of the API into ones that look like DBAPI functions, such as the fetchone(), fetchmany(), fetchall(), execute(), executemany(), commit(), rollback() methods, the cursor() function, the connect() function, the standard DBAPI types, etc.

[Mike Bayer]

correction, if its not DBAPI compliant.

[Paul Harrington]

APSW is not DBABPI-compliant. However, I made stubs for all the functions, properties and exceptions listed in PEP 249 and started to flesh them out with implementations that delegate to the underlying apsw API. I was pleasantly surprised to get a bunch of the SA unit-tests to run even with such simple implementations as

def fetchone(self):
    try:
        return self.cursor.next()
    except apsw.StopIteration:
        return None

def fetchall(self):
    return list(self.cursor)

I believe that fetchmany and the arraysize property will be doable with some help from itertools.

Naturally, transactions and exceptions will be much harder (and maybe impossible). However, I am looking forward to trying this as I can rely on the SA unit-tests to find the problems. I'll attach something if/when it gets to a reasonable state.

[Mike Bayer]

this can be implemented in modern SQLAlchemy as a third party plugin dialect if it is still desired. A pull request would be considered also though im not sure what advantage we get from including aspw support - the things aspw can do seem to be outside of the realm of normal DBAPI use (busy handling? VFS? haven't gotten any requests for any of this kind of thing).