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

[Michael Bayer]

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.




> 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