Python ODBC decimal error

I've searched for solutions to this error, but I'm still amazed. I am using python 2.7 venv with pyodbc 3.0.6

connection = pyodbc.connect(myconnstring)
cursor = connection.cursor()
cursor.execute("""SELECT varchar_column, date_column as epoch, decimal_col1, decimal_col2
FROM   table;""")
rows = cursor.fetchall()
#do some more stuff down here with rows.


Basically it is an error when trying to get data from the database in the specified query. I've tried filtering for null results and 0 results, which doesn't help. I tried casting it to varchar and using isnull to catch anything that would have no data and nothing seems to matter.

I tried to run a query against the database and it returns fine and if I try to filter for null values ​​the results are exactly the same so that there are no null results in fact. If I filter out 0, there are about 100 lines less, but even when I output them from the script, it still throws an error that doesn't make sense to me.

Traceback (most recent call last):
  File "", line 29, in <module>
    rows = cursor.fetchall()
  File "/opt/python-2.7.3/lib/python2.7/", line 548, in __new__
    "Invalid literal for Decimal: %r" % value)
  File "/opt/python-2.7.3/lib/python2.7/", line 3866, in _raise_error
    raise error(explanation)
decimal.InvalidOperation: Invalid literal for Decimal: u'1-'


As pointed out in the comments, the DBMS I tried to connect to was Vertica and the problem is not that Vertica itself works there. It was a combination of CentOS 6.4, python 2.7 venv, and the Vertica 6.1 driver that led to this strange behavior. As it turns out, I was converting date_column to epoch and the result was not reading correctly. It worked fine if it only returned with numeric columns, but not if I entered a varchar column.


source to share

All Articles