Datetime Import Using Pandas/sqlalchemy

I'm having problems importing datetimes from a SQL Server database into Pandas. I'm using the following code: data = pd.read_sql('select top 10 timestamp from mytable',db) 'MyTabl

Solution 1:

Turns out the problem originates from how SQL Alchemy calls PyODBC. By default it will use the 'SQL Server' driver, which doesn't support DateTime2. When I was using PyODBC directly, I was using the 'SQL Server Native Client 10.0' driver.

To get the correct behaviour, i.e. return python datetime objects, I needed to create the SQL Alchemy engine as follows:

import sqlalchemy as sql
connectionString = 'mssql+pyodbc://username:password@my_server/my_database_name?driver=SQL Server Native Client 10.0'
engine = sql.create_engine(connectionString)

The ?driver=... part forces SQL Alchemy to use the right driver.