到IBM Netezza错误的Python pyodbc连接 [英] Python pyodbc connections to IBM Netezza Erroring

问题描述

所以.此问题与此处讨论的问题几乎完全相同-但是那篇文章中讨论的修复(例如它)并不能解决我的问题.

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.

我正在尝试使用Python 2.7.5和pyodbc 3.0.7将Ubuntu 12.04 64位计算机连接到IBM Netezza数据库.我正在使用unixODBC处理指定的DSN.该DSN在isql CLI中可以正常工作-所以我知道它的配置正确，并且unixODBC一直在发展.

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.

该代码目前非常简单，并且易于在REPL中重现:

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''

所以我得到一个连接，查询正确返回，然后当我尝试获取一行时... asplode.

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

有人能做到吗?

推荐答案

结果证明pyodbc无法正常转换Netezza的所有类型.我正在使用的表有两个问题:

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

• NUMERIC(7,2)
类型的列
• NVARCHAR(255)
类型的列

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

NUMERIC列在NULL上导致十进制转换错误. NVARCHAR列返回一个utf-16-le编码的字符串，这很麻烦.

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.

我还没有找到一个好的驱动程序或包装程序级别的解决方案.可以通过在SQL语句中强制转换类型来破解:

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.

这篇关于到IBM Netezza错误的Python pyodbc连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！