Andy Dustman wrote: > Lately it seems there has been a lot of grousing from the asynchronous > folks (Twisted et al) about not being able to use DB-API databases > (specifically MySQL) in an async environment. I recently wrote this > post about it: > > http://mysql-python.blogspot.com/2010/02/asynchronous-programming-and-mysqldb.html > > In writing it, I realized that the DB-API by it's very nature is a > synchronous interface, and if we are going to support asynchronous > access, we should have some kind of API for it. This can either be a > DB-API extension or a new API.
True. I'd opt for a DB-API extension much like we added for the two-phase commit transaction logic, if that's possible. > A very brief survey of relational databases: > > MySQL: The C client library has no documented asynchronous support. > However the wire protocol is relatively simple, and there are > implementations in Python and other languages, and in principle async > support could be done. The main issue is that you still could only > have one active result set per connection, but that's a concurrency > issue. There are async perl and php drivers. > > PostgreSQL: I think there is direct C client library support. Looking > at the docs for psycopg2, there is some support for async, but it > appears to be disabled: > > https://dndg.it/cgi-bin/gitweb.cgi?p=public/psycopg2.git;a=blob_plain;f=doc/async.txt;hb=HEAD > > SQLite: Seems to have some async support, but it uses threads, and > it's in the database itself (writing to desk), not the client. Well, > since it's basically an embedded database anyway, the distinction is > basically meaningless. > > ODBC stuff: There seems to be some async support floating around out > there, but looking through the mx.ODBC docs briefly, there doesn't > seem to be support for it. ODBC does support asynchronous processing, but whether this is supported or not depends on the ODBC drivers. > Any strong feelings about what this support should look like? Just like with the TPC methods, we'll need to come up with something that most databases which do support such asynchronous processing can manage. For ODBC, you basically just need to enable/disable asynchronous processing on connections or cursors (with cursors inheriting the setting from their connections at creation time). The various APIs then return a special return code to signal that the API is still waiting for results. The main question for ODBC would be how to return control back to the calling Python application and how to enter back into the waiting loop to fetch the requested information. I suppose this would have to be exposed using an interface like we have for generators in Python. Another question is where to apply such non-waiting API calls, e.g. only for command preparation, command execution, for every row fetch operation, etc. In most applications we've written, we've used threads or separate processes to avoid having to wait for results from long running queries in the main application, so we've hardly ever had a need for such asynchronous mechanisms. PS: I've updated the subject to match the topic. Thanks, -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Feb 23 2010) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ ::: Try our new mxODBC.Connect Python Database Interface for free ! :::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 http://www.egenix.com/company/contact/ _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
