Retrieving a datetime value from a sql table to dataframe using sqlalchemy

Question

I'm trying to retrieve a table into a dataframe but I'm getting a "ValueError: hour must be in 0..23".

Here is my code :

from sqlalchemy import create_engine
import pyodbc
import pandas as pd

SERVER = '(local)'
DATABASE = 'Projects'
DRIVER = 'SQL Server' 
DATABASE_CONNECTION = f'mssql://@{SERVER}/{DATABASE}?driver={DRIVER}'

engine = create_engine(DATABASE_CONNECTION)
connection = engine.connect()

data = pd.read_sql_query('select TOP 1 * from PRODSYNTHESIS',connection)
#ValueError: hour must be in 0..23

connection.close()
engine.dispose()

table schema

My understanding is that I should probably be using python's datetime module to handle this datatype but not sure exactly how to get there. All other solutions I've found relate to the dateframe itself not the data type coming through from sql.

As a side question, why are you using TOP 1 without an ORDER BY? Without an ORDER BY that might as well say "Return an arbitrary row from the table." — Thom A
– Thom A ♦, Commented May 24, 2020 at 16:51
No specific reason to use TOP 1. Was just limiting the number of rows returned by the query. — amphinomos
– amphinomos, Commented May 24, 2020 at 20:40
Then introduce an ORDER BY. You won't get consistent results without one. — Thom A
– Thom A ♦, Commented May 24, 2020 at 20:48

David Browne - Microsoft · Accepted Answer · 2020-05-24 17:15:23Z

2

Not sure, but you can always woraround by converting the date to an ISO8601 string in the query itself. EG:

data = pd.read_sql_query('select TOP 1 Project_id, Project_name, convert(varchar(23), Date_results, 126) Date_results, P_Injecte from PRODSYNTHESIS',connection)

answered May 24, 2020 at 17:15

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

amphinomos Over a year ago

Thanks! That works but as you said returns a string ie.2019-09-20T01:10:00. Do you know why the conversion is necessary? Why can't it pass through as such? In any case, the datetime library has a .isoformat() method which looks like I could use later in the code.

David Browne - Microsoft Over a year ago

I do not. But you might try with an updated ODBC driver. The one you are using hasn’t been updated in almost 20 years and is a Legacy Windows component. See learn.microsoft.com/en-us/sql/connect/odbc/…

amphinomos Over a year ago

Good point. I was testing this on a old database and didn't even think this could affect the ORM.

Collectives™ on Stack Overflow

Retrieving a datetime value from a sql table to dataframe using sqlalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related