SQL Server DATE 作为字符串检索到 Pandas [英] SQL Server DATE retrieved into pandas as a string

问题描述

当我将日期"变量从 SQL Server 提取到 Python/Pandas 中时，它作为对象"出现.我已经安装并尝试了几个驱动程序(代码中显示的注释驱动程序尝试过)，每次都得到相同的结果:

When I pull a "Date" variable from SQL Server into Python/Pandas, it comes through as an 'Object'. I have installed and tried several drivers (commented drivers tried shown in the code), each time with the same results:

import pandas as pd
import pyodbc

conn_str = (
    r'Driver={SQL Server Native Client 11.0};'
    # r'Driver={SQL Server Native Client 10.0};'
    # r'Driver={ODBC Driver 11 for SQL Server};'
    # r'Driver={ODBC Driver 13 for SQL Server};'
    # r'Driver={SQL Server};'
    r'Server=MyServer;'
    r'Database=MyDB;'
    r'Trusted_Connection=yes;'
    )

cnxn = pyodbc.connect(conn_str)

sql = (
    "Select cast('2017-08-19' as date) [DateVar]"
    ", cast('2017-08-19' as datetime) [DateTimeVar]"
    ", cast('2017-08-19' as datetime2) [DateTime2Var]"
    )

d2 = pd.read_sql(sql,cnxn)

cnxn.close()

print(d2.dtypes)

返回结果为:

DateVar                 object
DateTimeVar     datetime64[ns]
DateTime2Var    datetime64[ns]
dtype: object

我希望 DateVar 是一个日期时间.任何想法为什么会发生这种情况??

I want that DateVar to be a datetime. Any ideas why this is happening??

和这个人一样的问题:pyodbc 将 SQL Server DATE 字段作为字符串返回但是他的修复方法是使用我已经安装但不适合我的 {SQL Server Native Client 10.0}.

Same issue as this guy: pyodbc returns SQL Server DATE fields as strings But the fix for him was to use {SQL Server Native Client 10.0} which I've installed and isn't working for me.

我连接的 SQL Server 版本是:

Version of SQL Server I'm connecting to is:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
Oct 20 2015 15:36:27 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

更新

1>

基于 Max 的输入，尝试过 sqlalchemy，但没有运气，这仍然给了我一个字符串:

Based on Max's input, have tried sqlalchemy, but no luck, this still gives me a string back:

import sqlalchemy as sa
engine = sa.create_engine('mssql+pyodbc://MyDatabase/MyDB?driver=SQL+Server+Native+Client+10.0')
d2 = pd.read_sql(sql, engine)

2>

基于 Flipper 的 Q，仅使用 Pyodbc 游标即可完成此操作，并且在使用 Native Client 11.0 时，游标中似乎返回了正确的日期数据类型:

Based on Flipper's Q, have done this with just a Pyodbc cursor and it looks like the proper date data type is being returned in the cursor when using the Native Client 11.0:

(('DateVar', datetime.date, None, 10, 10, 0, True),
 ('DateTimeVar', datetime.datetime, None, 23, 23, 3, True),
 ('DateTime2Var', datetime.datetime, None, 27, 27, 7, True))

这表明问题出在 Pandas 在加载到数据帧时对 dtype datetime.date 的处理.

This would suggest the issue is in Pandas handling of the dtype datetime.date when loading into a dataframe.

推荐答案

使用 pandas.read_sql 指定 DateVar 列值在数据帧加载时显式转换为日期时间.

Use the parse_dates parameter of pandas.read_sql to specify that DateVar column values are explicitly converted to datetime on dataframe load.

更新了原始代码片段:

...
d2 = pd.read_sql(sql=sql,
                 con=cnxn,
                 # explicitly convert DATE type to datetime object
                 parse_dates=["DateVar"])

cnxn.close()

print(d2.dtypes)

退货

DateVar         datetime64[ns]
DateTimeVar     datetime64[ns]
DateTime2Var    datetime64[ns]
dtype: object

在 Windows 上使用 pyodbc 4.0.17、pandas 0.20.3 和 SQL Server 2014 进行测试.

Tested with pyodbc 4.0.17, pandas 0.20.3, and SQL Server 2014 on Windows.

这篇关于SQL Server DATE 作为字符串检索到 Pandas的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！