0

With some struggles I connected to a hfsql server using ODBC. I've tried both pypyodbc and pyodbc. My goal is to get some insights in the data (and visualize some aspects).

For some planning visualization I need to read out some of the data, which works fine for most tables and columns. However, when I try to read out a column containing dates i get a ValueError:

    invalid literal for int() with base 10: ''

All rows contain a valid date.

This is the code that currently produces the error above:

deadlines = db.cursor()
query = ("SELECT DeliveryDate FROM Orders WHERE Finished = 0")
deadlines.execute(query)
print(deadlines.fetchone()) #<- this goes wrong

db is the database (it works with other queries)

    print(deadlines.description)

gives: [('deliverydate', <class 'datetime.date'>, 11, 9, 9, 0, True)]

I also tried:

pandas.read_sql(query,db,parse_dates={'DeliveryDate': {"dayfirst": True}})

(dates are e.g. 27-6-2022) Which unfortunately gives the same error.

Any help would be appreciated, cheers,

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
WvdL1995
  • 29
  • 5
  • You have tagged this question as both `pypyodbc` and `pyodbc`. Have you actually tried `pyodbc` to see if it works any better? – Gord Thompson Jun 27 '22 at 23:08
  • Thank you for the response. I tried both pypyodbc and pyodbc (I work on two different systems and one has pypyodbc and the other pyodbc installed) so far they behave exactly the same way: expected results for all fields except "date" fields. – WvdL1995 Jun 28 '22 at 06:25
  • Does `SELECT CAST(DeliveryDate AS varchar(12)) AS dd FROM Orders …` avoid the error? – Gord Thompson Jun 28 '22 at 09:48
  • Were you able to solve this to your satisfaction? It looks like a driver issue to me. Have you contacted the driver vendor for assistance? – Gord Thompson Jun 29 '22 at 23:14
  • Your suggestion with cast(...) etc. solved the problem. Thank you. (I responded to your earlier message, but appearently the message doesn't show), thanks again. – WvdL1995 Jun 30 '22 at 12:33

1 Answers1

1
SELECT CAST(DeliveryDate AS varchar(12)) AS dd FROM Orders …

(as suggested in a comment to the question) solved the issue.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
WvdL1995
  • 29
  • 5