10

I'm trying to query a Teradata database in Python with PyODBC. The connection to database is established alright; however, when I try to fetch result, I ran into this error "Invalid literal for Decimal: u''". Help please.

I am on RHEL6, with Python 2.7.3

Here is the code and result:

import pyodbc

sql = "select * from table"

pyodbc.pooling = False
cnx = pyodbc.connect("DRIVER={Teradata};DBCNAME=host;DATABASE=database;   AUTHENTICATION=LDAP;UID=user;PWD=password", autocommit=True, ANSI=True)
cursor = cnx.cursor()
rows = cursor.execute(sql).fetchone()

InvalidOperation                          Traceback (most recent call last)
<ipython-input-25-f2a0c81ca0e4> in <module>()
----> 1 test.fetchone()

/usr/local/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/local/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''
wangke99
  • 141
  • 2
  • 4
  • Looks like the driver is expecting a fixed point number and getting back an empty string. What does your table schema look like? What row should it be returning? – cmd Mar 05 '13 at 22:58
  • 1
    Thanks for your question - it helped me setup the Teradata database connection in Python script that needs to work with result sets :) . – Jubbles Jan 21 '14 at 19:50
  • What did u change? i am stuck at the same problem with so solution whatsoever – Rhythem Aggarwal Jan 17 '19 at 14:54

4 Answers4

2

I had this error, and I found the cause was that pyodbc was 64 bit, whereas my teradata driver was 32. The problem was solved after building a new driver with unixodbc.

grisaitis
  • 3,010
  • 2
  • 23
  • 29
1

Forcing the right locale in setup.py (before compiling) did the trick for me ,example:

import locale
locale.setlocale(locale.LC_ALL, 'es_ES.utf8')
Juan Diego Godoy Robles
  • 12,742
  • 2
  • 36
  • 47
0

I found that pyodbc out of the box might not work as per http://code.google.com/p/pyodbc/issues/detail?can=1&q=teradata&id=146

I verified the fix in that thread works. Check out https://github.com/mkleehammer/pyodbc, add extra commands to setup.py, recompile, and it appears to work.

sholsapp
  • 13,679
  • 8
  • 44
  • 62
0

If your null character is set to '?' (which I believe is the default) or anything else strange this could be a problem with returning, especially if you are working with the data once it hits python.

DrBailey
  • 163
  • 8