CODEWITHSUNDEEP
pythonsql-serverpandassqlalchemy
tobycoleman
tobycoleman

Reputation: 1723

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)

'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]?

Upvotes: 3

Views: 1940

Answers (1)

tobycoleman
tobycoleman

Reputation: 1723

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.

Upvotes: 5

Related Questions