到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屋!