pyodbc返回SQL Server DATE字段作为字符串 [英] pyodbc returns SQL Server DATE fields as strings

问题描述

我正在使用pyodbc查询具有DATE类型列的SQL Server 2008数据库表。

I'm using pyodbc to query a SQL Server 2008 database table with columns of DATE type.

结果数据行包含日期字符串，而不是python datetime.date或datetime.datetime实例。

The resulting rows of data contain date strings rather than python datetime.date or datetime.datetime instances.

这似乎仅是DATE类型的列的问题；正确处理了DATETIME类型的列并返回datetime.datetime实例。

import pyodbc
from pprint import pformat
db = pyodbc.connect("DRIVER={SQL Server};SERVER=.\\SQLEXPRESS;DATABASE=scratch;Trusted_Connection=yes")
print pformat(db.cursor().execute("select * from Contract").description)

结果：

(('id', <type 'int'>, None, 10, 10, 0, False),
 ('name', <type 'str'>, None, 23, 23, 0, False),
 ('some_date', <type 'unicode'>, None, 10, 10, 0, True),
 ('write_time', <type 'datetime.datetime'>, None, 23, 23, 3, False))

请注意， some_date 列表示为unicode字符串类型，在数据库中，此列定义为DATE：

Note that the some_date column is indicated as type unicode string, however, in the database this column is defined as DATE:

CREATE TABLE dbo.Contract(
    id INT NOT NULL,
    name VARCHAR(23) NOT NULL,
    some_date DATE NULL,
    write_time DATETIME NOT NULL)

是

推荐答案

使用SQL Server本机客户端。例如在连接字符串中放入 Driver = {SQL Server Native Client 10.0} ，而不是
DRIVER = {SQL Server} 。

Use the SQL Server native client. e.g. Put Driver={SQL Server Native Client 10.0} in your connection string,instead of DRIVER={SQL Server}.

使用SQL Server ODBC驱动程序复制日期返回为字符串的方案。当使用SQL Server本机客户端的2008+兼容版本时，日期类型将按预期返回，但看起来datetime2作为字符串返回（在我的有限测试中）。

Reproduced your scenario with date being returned as string using SQL Server ODBC driver. When using a 2008+ compatible version of the SQL Server native client, the date type is returned as expected, but it looks like datetime2 gets returned as string (in my limited testing).

表定义：

create table dbo.datetest (
    [date] date not null,
    [datetime] datetime not null,
    [datetime2] datetime2 not null
);

insert into
    dbo.datetest
values
    (CAST(current_timestamp as DATE),
     CAST(current_timestamp as datetime),
     CAST(current_timestamp as datetime2));

示例：

import pyodbc
from pprint import pformat
db = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                    server='TESTSRVR', database='TESTDB',
                    trusted_connection='yes')
print pformat(db.cursor().execute("select * from dbo.datetest").description)

结果：

(('date', <type 'datetime.date'>, None, 10, 10, 0, False),
 ('datetime', <type 'datetime.datetime'>, None, 23, 23, 3, False),
 ('datetime2', <type 'unicode'>, None, 27, 27, 0, False))

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