DateTime import using Pandas/SQLAlchemy

Question

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)

'MyTable' contains a column 'Timestamp', which is of type DateTime2.

If db is a pyodbc database connection this works fine, and my timestamps are returned as data type 'datetime64[ns]'. However if db an SQL Alchemy engine created using create_engine('mssql+pyodbc://...') then the timestamps returned in data are of type 'object' and cause problems later on in my code.

Any idea why this happens? I'm using pandas version 0.14.1, pyodbc version 3.0.7 and SQL alchemy version 0.9.4. How best can I force the data into datetime64[ns]?

To force it, you can use the parse_dates keyword of read_sql (see pandas.pydata.org/pandas-docs/stable/generated/…). But this shouldn't actually be necessary and seems like a bug to me (in pandas or sqlalchemy), can you report it on github.com/pydata/pandas/issues? — joris
– joris, Commented Sep 1, 2014 at 15:08
you can accept your own answer, as you did find it out yourself. — joris
– joris, Commented Sep 3, 2014 at 11:25

tobycoleman · Accepted Answer · 2014-09-03 11:01:11Z

5

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.

answered Sep 3, 2014 at 11:01

tobycoleman

1,7431 gold badge18 silver badges37 bronze badges

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

1 Comment

johnDanger Over a year ago

The native client is now deprecated

Collectives™ on Stack Overflow

DateTime import using Pandas/SQLAlchemy

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related