如何在pyodbc下将datetime.time返回类型更改为字符串? [英] How to change datetime.time return type to string under pyodbc?

问题描述

我已将现有的大型 Python 2 项目的代码库从 pymssql 移动到 pyodbc.迁移很简单，一切都构建并运行良好，除了 pyodbc 将时间类型的 SQL Server 列返回为 datetime.time，其中 pymssql(以及 PostgreSQL 本机驱动程序)返回字符串.

意思是，我收到一个 datetime.time(11, 54, 13, 161000) 对象而不是 11:54:13.161000.有什么方法可以在不求助于某种存储过程的情况下全局更改此行为?处理返回的对象而不是返回的字符串将需要几天的重构和测试.

解决方案

你可以使用 pyodbc 输出转换器函数 捕获从 SQL Server 返回的 TIME 值并将它们格式化为字符串.

之前:

import pyodbccnxn = pyodbc.connect('DSN=SQLmyDb;', autocommit=True)crsr = cnxn.cursor()rtn = crsr.execute("SELECT CAST('11:54:13.161' AS TIME)").fetchval()打印(repr(rtn)) # datetime.time(11, 54, 13, 161000)crsr.close()cnxn.close()

之后:

import pyodbc导入结构def time_as_string(raw_bytes):tup = struct.unpack("<4hI", raw_bytes) # 例如 (11, 54, 13, 0, 161000000)调整 = [tup[i]//100 if i == 4 else tup[i] for i in [0, 1, 2, 4]]返回 "{:02d}:{:02d}:{:02d}.{:07d}".format(*tweaked)cnxn = pyodbc.connect('DSN=SQLmyDb;', autocommit=True)cnxn.add_output_converter(pyodbc.SQL_SS_TIME2, time_as_string)crsr = cnxn.cursor()rtn = crsr.execute("SELECT CAST('11:54:13.161' AS TIME)").fetchval()打印(repr(rtn))#'11:54:13.1610000'crsr.close()cnxn.close()

I have moved the codebase for a large existing Python 2 project from pymssql to pyodbc. The move was easy, everything builds, and runs fine, except that pyodbc returns SQL Server columns of type time as datetime.time, where pymssql (as well as the PostgreSQL native drivers) return strings.

Meaning, I receive a datetime.time(11, 54, 13, 161000) object instead of 11:54:13.161000. Is there any way I can change this behavior globally without resorting to a stored procedure of some sort? Handling a returned object instead of a returned string will involve several days worth of refactoring and testing.

解决方案

You could use a pyodbc Output Converter function to capture the TIME values returned from SQL Server and format them as strings.

Before:

import pyodbc

cnxn = pyodbc.connect('DSN=SQLmyDb;', autocommit=True)
crsr = cnxn.cursor()

rtn = crsr.execute("SELECT CAST('11:54:13.161' AS TIME)").fetchval()
print(repr(rtn))  # datetime.time(11, 54, 13, 161000)

crsr.close()
cnxn.close()

After:

import pyodbc
import struct


def time_as_string(raw_bytes):
    tup = struct.unpack("<4hI", raw_bytes)  # e.g., (11, 54, 13, 0, 161000000)
    tweaked = [tup[i] // 100 if i == 4 else tup[i] for i in [0, 1, 2, 4]]
    return "{:02d}:{:02d}:{:02d}.{:07d}".format(*tweaked)


cnxn = pyodbc.connect('DSN=SQLmyDb;', autocommit=True)
cnxn.add_output_converter(pyodbc.SQL_SS_TIME2, time_as_string)
crsr = cnxn.cursor()

rtn = crsr.execute("SELECT CAST('11:54:13.161' AS TIME)").fetchval()
print(repr(rtn))  # '11:54:13.1610000'

crsr.close()
cnxn.close()

这篇关于如何在pyodbc下将datetime.time返回类型更改为字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！