4

I've look for solutions to this error but I'm still striking out. I'm using a 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 errors out when it's trying to fetch the data from the database in the query specified. I've tried filtering out null results and 0 results, which doesn't help. I've tried casting it to a varchar and using isnull to catch anything that won't have data and nothing seems to be making a difference.

I've tried running the query in the database and it returns fine and if i try to filter for nulls the results are exactly the same so there effectively are no null results. If I filter out 0's there are about 100 less rows but even when I take those out of the script it's still throwing an error which doesn't make sense to me.

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

As requested in the comments, the DBMS I was trying to connect to was Vertica and the issue wasn't with Vertica itself the queries work fine on there. It was the combination of CentOS 6.4, a python 2.7 venv and Vertica 6.1 driver that resulted in this odd behaviour. As it turns out I was converting my date_column to an epoch and in doing so the result was not being read correctly. It worked fine if it was being returned with only numeric columns but not if I introduced a varchar column.

rhealitycheck
  • 580
  • 2
  • 7
  • 17
  • Which DBMS are you connecting to? What if you select just decimal_col1 in your query? Still error out? What if you just select decimal_col2? What if you fetch one row at a time? Does the error occur on the first fetch or somewhere else? What does your connection string look like (minus the username and password of course)? – Steven Rumbalski Oct 10 '14 at 15:35
  • Ok so I tried doing just the single columns and interestingly it works if I use these 3 together: date_column, decimal_col1, decimal_col2 and it works for each column individually but if i try to select the varchar_column with any of the others i get that error – rhealitycheck Oct 10 '14 at 16:37
  • 1
    actually @StevenRumbalski your method really helped. it's the date_column + varchar_column that were for some reason throwing the error if i used them together. i cast the date_col to a varchar and it works. seems strange but thanks for the ideas! – rhealitycheck Oct 10 '14 at 16:41
  • It might be helpful for future readers if you would at least indicate the DBMS you are using. I just tried to recreate your issue using Python 2.7.5, pyodbc 3.0.7, and SQL Server Native Client 10.0 on Windows, but your code worked fine for me. – Gord Thompson Oct 11 '14 at 17:03
  • @GordThompson updated in question. – rhealitycheck Oct 13 '14 at 15:55
  • 1
    Try to cast that decimal to either float or string type and fix this issue on the Python side later. I think that will work, even though it is ugly. Possibly related: http://stackoverflow.com/questions/21536059/python-pyodbc-connections-to-ibm-netezza-erroring – Leonid Sep 06 '16 at 04:24

0 Answers0