使用Python的Win32 ODBC模块检索Oracle时间戳。

Given an Oracle table created using the following:

使用以下方法创建的Oracle表:

CREATE TABLE Log(WhenAdded TIMESTAMP(6) WITH TIME ZONE);

Using the Python ODBC module from its Win32 extensions (from the win32all package), I tried the following:

从Win32扩展(从win32all包)中使用Python ODBC模块，我尝试了以下步骤:

import dbi, odbc

connection = odbc.odbc("Driver=Oracle in OraHome92;Dbq=SERVER;Uid=USER;Pwd=PASSWD")

cursor = connection.cursor()
cursor.execute("SELECT WhenAdded FROM Log")

results = cursor.fetchall()

When I run this, I get the following:

当我运行这个时，我得到如下:

Traceback (most recent call last):
...
    results = cursor.fetchall()
dbi.operation-error: [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected %s got %s 
in FETCH

The other data types I've tried (VARCHAR2, BLOB) do not cause this problem. Is there a way of retrieving timestamps?

我尝试过的其他数据类型(VARCHAR2, BLOB)不会导致这个问题。有没有一种检索时间戳的方法?

2 个解决方案

#1

I believe this is a bug in the Oracle ODBC driver. Basically, the Oracle ODBC driver does not support the TIMESTAMP WITH (LOCAL) TIME ZONE data types, only the TIMESTAMP data type. As you have discovered, one workaround is in fact to use the TO_CHAR method.

我相信这是Oracle ODBC驱动程序中的一个bug。基本上，Oracle ODBC驱动程序不支持(本地)时区数据类型的时间戳，只有时间戳数据类型。正如您所发现的，一个解决方案实际上是使用TO_CHAR方法。

In your example you are not actually reading the time zone information. If you have control of the table you could convert it to a straight TIMESTAMP column. If you don't have control over the table, another solution may be to create a view that converts from TIMESTAMP WITH TIME ZONE to TIMESTAMP via a string - sorry, I don't know if there is a way to convert directly from TIMESTAMP WITH TIME ZONE to TIMESTAMP.

在您的示例中，您实际上并没有读取时区信息。如果您有对表的控制，您可以将它转换为一个直的时间戳列。如果您无法控制表，另一个解决方案可能是创建一个视图，该视图将时间戳与时区转换为时间戳，然后通过一个字符串——对不起，我不知道是否有一种方法可以直接从时间戳转换到时间戳。

#2

My solution to this, that I hope can be bettered, is to use Oracle to explicitly convert the TIMESTAMP into a string:

我的解决方案是使用Oracle将时间戳显式地转换为字符串:

cursor.execute("SELECT TO_CHAR(WhenAdded, 'YYYY-MM-DD HH:MI:SSAM') FROM Log")

This works, but isn't portable. I'd like to use the same Python script against a SQL Server database, so an Oracle-specific solution (such as TO_CHAR) won't work.

这是可行的，但不能移植。我希望在SQL Server数据库上使用相同的Python脚本，因此一个特定于oracle的解决方案(例如TO_CHAR)将不起作用。

#1

#2