CODEWITHSUNDEEP
pythonsql-serverpandassqlalchemy
amphinomos
amphinomos

Reputation: 37

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

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.

Upvotes: 0

Views: 351

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

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)

Upvotes: 2

Related Questions