Python pyodbc connections to IBM Netezza Erroring

Question

So. This issue is almost exactly the same as the one discussed here -- but the fix (such as it is) discussed in that post doesn't fix things for me.

I'm trying to use Python 2.7.5 and pyodbc 3.0.7 to connect from an Ubuntu 12.04 64bit machine to an IBM Netezza Database. I'm using unixODBC to handle specifying a DSN. This DSN works beautifully from the isql CLI -- so I know it's configured correctly, and unixODBC is ticking right along.

The code is currently dead simple, and easy to reproduce in a REPL:

In [1]: import pyodbc
In [2]: conn = pyodbc.connect(dsn='NZSQL')
In [3]: curs = conn.cursor()
In [4]: curs.execute("SELECT * FROM DB..FOO ORDER BY created_on DESC LIMIT 10")
Out[4]: <pyodbc.Cursor at 0x1a70ab0>

In [5]: curs.fetchall()
---------------------------------------------------------------------------
InvalidOperation                          Traceback (most recent call last)
<ipython-input-5-ad813e4432e9> in <module>()
----> 1 curs.fetchall()

/usr/lib/python2.7/decimal.pyc in __new__(cls, value, context)
    546                     context = getcontext()
    547                 return context._raise_error(ConversionSyntax,
--> 548                                 "Invalid literal for Decimal: %r" % value)
    549
    550             if m.group('sign') == "-":

/usr/lib/python2.7/decimal.pyc in _raise_error(self, condition, explanation, *args)
   3864         # Errors should only be risked on copies of the context
   3865         # self._ignored_flags = []
-> 3866         raise error(explanation)
   3867
   3868     def _ignore_all_flags(self):

InvalidOperation: Invalid literal for Decimal: u''

So I get a connection, the query returns correctly, and then when I try to get a row... asplode.

Anybody ever managed to do this?

Answer 1

Turns out pyodbc can't gracefully convert all of Netezza's types. The table I was working with had two that are problematic:

• A column of type NUMERIC(7,2)
• A column of type NVARCHAR(255)

The NUMERIC column causes a decimal conversion error on NULL. The NVARCHAR column returns a utf-16-le encoded string, which is a pain in the ass.

I haven't found a good driver-or-wrapper-level solution yet. This can be hacked by casting types in the SQL statement:

SELECT
     foo::FLOAT AS was_numeric
     , bar::VARCHAR(255) AS was_nvarchar

I'll post here if I find a lower-level answer.