[DB-SIG] checking column types from cursor object in a database-independent way?

[M.-A. Lemburg]

On 19.04.2013 20:46, Michael Bayer wrote:
> 
> On Apr 19, 2013, at 1:56 PM, Dan Lenski <dlenski at gmail.com> wrote:
> 
>> Hi,
>> I have to dump some data from various databases (Oracle and Postgre, at the 
>> moment) into CSV files, while preserving the column type information so the 
>> data can be correctly loaded into another application.  Basically, I need to 
>> write a function that can distinguish string/numeric data in the columnar data 
>> from a cursor object in a fashion that's independent of the particular DBAPI 
>> module in use.
>>
>> According to PEP-0249, each DBAPI module has to define type objects (e.g. 
>> module.STRING) which "must compare equal to" the type objects in the cursor 
>> object for each column.
>>
>> The problem I'm having is... how can I figure out what are the appropriate 
>> type objects for this comparison if I *only* have access to the cursor object? 
> 
> 
> Basically I'd question that precondition.   I'd look into how the system is architected such that only a cursor is passed around, and try to substitute it/augment it with some kind of contextual object; either an object that can refer back to the DBAPI types, or even the DBAPI module itself.    You can't really assume that ".connection" is present, nor that the Connection class of the DBAPI is in the same module space as where the type objects are defined.   I'd rework the system and refuse the temptation to guess.

Yep, I think that's the way to go: simply pass the module object
with the needed symbols around with the connection and cursor object.

Note that adding something like connection.module won't work,
since it may cause circular references which could then lead to
connections being closed later than expected or modules not
getting properly cleaned up at interpreter finalization time.
At best, would have connection.modulename reference the module
name as registered in sys.modules.

>> I've been using an ugly kludge so far, to get a handle on the right DBAPI 
>> module:
>>
>>    mod = sys.modules[cur.connection.__class__.__module__] #FIXME!
>>    if cur.description[0][1] == mod.STRING:
>>        print "first column is STRING data type"
>>
>> Is there a more robust and elegant solution to this?  It seems like there 
>> ought to be a module-independent way to introspect the column data types in 
>> the cursor object.
>>
>> Thanks,
>> Dan Lenski
>>
>> _______________________________________________
>> DB-SIG maillist  -  DB-SIG at python.org
>> http://mail.python.org/mailman/listinfo/db-sig
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig
> 

-- 
Marc-Andre Lemburg
Vice Chairman
Python Software Foundation
http://www.python.org/psf/