[DB-SIG] Some obscurity with paramstyle

[M.-A. Lemburg]

Daniele Varrazzo wrote:
>> On Jul 19, 2011 3:44 AM, "Federico Di Gregorio"
>> <federico.digregorio at dndg.it> wrote:
>>> At least 2 drivers (psycopg and pysqlite) provide a Python->backend
>>> mechanism based on PEP 246, "Object Adaptation". If other implementors
>>> are interested I can write a short explanation about how it works and
>>> why it was chosen only for the Python->backend path and not for the
>>> reverse.
> 
> On Tue, Jul 19, 2011 at 11:05 AM, Vernon Cole <vernondcole at gmail.com> wrote:
>> Yes, please. I would like to see that. Adodbapi has an output conversion
>> mechanism, but I don't like it particularly well.
> 
> For a description:
> 
> http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax
> http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects

While this is a nice system, it's also very slow. It uses function
calls and string parsing/conversion for adapting each value. This
works if you only have to insert/fetch a few rows, but won't be
feasible for larger volumes.

I think we need something more low-level, which tries to
avoid (Python) function calls if possible, e.g. it should be
possible to write adapters in C and only point to them using
a symbol (sketching here):

cursor.setinputconverter(BINARY, module.BINARY_INPUT_CONVERTER)
cursor.setoutputconverter(BINARY, module.BINARY_OUTPUT_CONVERTER)

For mxODBC we'd then use something like this:

# Convert Python unicode object data to SQLWCHAR data
cursor.setinputconverter(unicode, module.SQLWCHAR_INPUT_CONVERTER)

# Convert SQL_WCHAR type code data to Python unicode
cursor.setoutputconverter(SQL.WCHAR, module.SQLCHAR_OUTPUT_CONVERTER_UNICODE)

The advantage here is that the database module could work
directly on the internal data structure to implement
the conversion rather than having to round-trip to Python.

In order to provide the psycopg style adaption mechanism,
the converter functions would have to be registered with
converter codes, e.g.

module.registerinputconverter(module.POINT_INPUT_CONVERTER, adapt_point)
module.registeroutputconverter(module.POINT_OUTPUT_CONVERTER, cast_point)

Lookup would then be a simple dictionary lookup on output
(via the column type code) and a dictionary lookup/isinstance()
loop for input mappings.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jul 19 2011)
>>> 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/