title: checking Python DB cursor column types in a database-independent way
body: <p>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 <strong>in a fashion that's independent of the particular DBAPI module in use.</strong></p>
  
  <p>According to <a href="http://www.python.org/dev/peps/pep-0249/#type-objects" rel="nofollow">DB-API v2 docs</a>, each DBAPI module has to define type objects (e.g. <code>module.STRING</code>) which "must compare equal to" the type objects in the cursor object for each column (e.g. <code>cursor.description[n][1]</code> for the n-th column).</p>
  
  <p>The problem I'm having is... how can I figure out what are the appropriate type objects for this comparison if I <em>only</em> have access to the cursor object? How can I get a handle to the appropriate DBAPI module from the cursor object. Any advice or suggestions?</p>
  
  <p>I've been using an ugly kludge so far:
  </p>
  
  <pre class="lang-py prettyprint-override"><code>def FigureOutDBTypes(cursor):
      if 'psycopg2' in str(type(cursor)):
          coltypes = [(str if x[1]==1043 else int) for x in cursor.description] 
      elif 'Oracle' in str(type(cursor)):
          typemap = dict(NUMBER = int,
                         DATE = int,
                         DATETIME = int,
                         TIMESTAMP = int,
                         STRING = str)
          coltypes = [typemap.get(x[1] and x[1].__name__, int) for x in cursor.description]
      else:
          raise RuntimeError("Don't know how to get type information from DB cursor.", cursor)
      return coltypes
  </code></pre>
  
  <p>I'd like a more robust and elegant solution to this. Any advice or suggestions?</p>
  
  <p>EDIT: The best thing I've come up with so far is to get a handle to the appropriate module using the following almost-as-horrible hack:</p>
  
  <pre class="lang-py prettyprint-override"><code>mod = sys.modules[cur.connection.__class__.__module__] #FIXME!
  coltypes = [(str if x[1]==mod.STRING else int) for x in cursor.description]
  </code></pre>