pyodbc 将 SQL Server DATE 字段作为字符串返回

发布于 2024-12-01 09:30:54 字数 1027 浏览 3 评论 0原文

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

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

这似乎只是 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 字符串类型，但是，在数据库中定义了此列作为日期：

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

这是正常的吗？我怎样才能最好地纠正它？

原文

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

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

This only appears to be an issue for columns of type DATE; columns of type DATETIME are handled correctly and return a datetime.datetime instance.

Example

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)

Results:

(('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))

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)

Is this normal, and how can I best correct it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半寸时光 2024-12-08 09:30:54

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

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

表定义：

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))

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}.

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).

Table definition:

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));

Example:

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)

Results:

(('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))

回复收藏 0 原文

~没有更多了~