mxODBC (was "split problem if the delimiter is inside the text limiter")

[M.-A. Lemburg]

On 2009-03-19 13:40, Tim Chase wrote:
>> DB-API 2.0 has cursor.executemany() to make this differentiation
>> at the API level. mxODBC will lift this requirement in the next
>> version, promised :-)
> 
> glad to hear...will executemany() take an arbitrary iterable?  My
>  (albeit somewhat-antiquated) version balked at anything that wasn't a
> list/tuple (don't remember off the top of my head which it was).  For a
> lot of my ETL work, it would be nice to pass a generator so I don't have
> to keep huge datasets in memory.

cursor.executemany() is designed to work on bulk data. It allows
passing a whole chunk of data to the database in one go. It will
currently accept any sequence, but not an iterator. However, we may
add support for general iterators to the next version.

If you want to use a generator with mxODBC 3.0, you are probably
better off using e.g.

cmd = 'INSERT ... VALUES (?,?,?)'
for row in iterable:
    cursor.execute(cmd, row)

Since you always use the same command object, this will trigger
an optimization in mxODBC whereby the prepare step for the command
is only applied once and the already prepared command then
reused for all subsequent executes.

>>> (and don't get me started on mxODBC's failure to
>>> determine the data-type for parameters in subqueries, raising exceptions
>>> on perfectly valid queries</rant>)
>>
>> I'm not sure what you are referring to here. mxODBC can only provide
>> an interface to the underlying ODBC driver.
> 
> Okay...feel free to deflect the minced oaths at MS's SQL Server ODBC
> driver then. :)  The main problem came with queries like
> 
>   cursor.execute("""
>     SELECT a, b, c
>     FROM table_x
>     WHERE providerid = ?
>       AND a IN (
>        SELECT z
>        FROM table_y
>        WHERE accountid = ?
>        )""", (providerid, accountid)
>     )
> 
> The "accountid = ?" in the sub-query triggers some inability in some
> layer to determine what datatype it should be converted to, so it raises
> a FrustrateTim exception :)

Parameter binding markers are not always supported in all contexts.

The error that I get from the SQL Server ODBC driver for the above is:

mx.ODBC.Error.ProgrammingError: ('42000', 0, '[Microsoft][SQL Server Native
Client 10.0]Syntax error, permission violation, or other nonspecific error', 7498)

In such a case, you may have more luck by using mxODBC's
cursor.executedirect(). This uses a different mechanism for preparing
and binding the data to the command which basically moves the
process to the database server side.

-- 
Marc-Andre Lemburg
eGenix.com

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