DECIMAL columns: Cursor.description is <type 'float'>?

[alexander...@gmx.net]

DECIMAL columns are stored as decimal.Decimal in Row objects.
But their type_code in Cursor.description is <type 'float'>

Is this the expected behaviour?

C:\>type issue_1.py
#!/usr/bin/env python
# -*- coding: ISO-8859-1 -*-

import ConfigParser
import types
import pyodbc

def main(Dsn, Uid, Pwd, Database):
Connection = pyodbc.connect("DSN=%s;UID=%s;PWD=%s;DATABASE=%s" %
(Dsn, Uid, Pwd, Database))
Cursor = Connection.cursor()
Cursor.execute("SELECT CAST(3.5 AS DECIMAL(2,1)) AS x;")
Rows = Cursor.fetchall()
print "type(column in Row) : ", type(Rows[0].x)
print "type_code in Cursor.description: ", Cursor.description[0]
[1]

if __name__ == "__main__":
Config = ConfigParser.ConfigParser()
Config.read("logon.cfg")
main(
Config.get("Teradata", "Dsn")
,Config.get("Teradata", "Uid")
,Config.get("Teradata", "Pwd")
,Config.get("Teradata", "Database")
)

C:\>python issue_1.py
type(column in Row) : <class 'decimal.Decimal'>
type_code in Cursor.description: <type 'float'>

C:\>

[MS Windows Server 2003 SP2 + Python 2.5.1 + pyodbc 2.1.3 + Teradata
12 RDBMS]

[mkleehammer]

In general, no. If you run this test on other databases/drivers
you'll find they are often decimal.Decimal objects.

The data types in the cursor come from the driver, so apparently your
driver is telling us it is a float/real. We can verify this by
turning on ODBC tracing and running your program.

Also, a faster way to see the data types is simply:

cursor.execute(..)
print cursor.description

[alexander...@gmx.net]

mkleehammer, thank you for taking the time to answer my question.

The relevant (?) part of the odbc trace is enclosed below.
SQLDescribeCol returns 3 as DataTypePtr.
I do not have the odbc header files at hand, but I believe 3 is
SQL_DECIMAL.

I skimmed cursor.cpp and suppose that create_name_map() retrieves the
column metadata?
create_name_map() and PythonTypeFromSqlType() seem to be OK.
But I do not understand where Py_BuildValue() is defined.

python issue_1 d88-bf4 ENTER SQLExecDirect
HSTMT 00A62528
UCHAR * 0x009E2EB4 [ -3] "SELECT CAST(3.5 AS DECIMAL
(2,1)) AS x;\ 0"
SDWORD -3

python issue_1 d88-bf4 EXIT SQLExecDirect with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
UCHAR * 0x009E2EB4 [ -3] "SELECT CAST(3.5 AS DECIMAL
(2,1)) AS x;\ 0"
SDWORD -3

python issue_1 d88-bf4 ENTER SQLRowCount
HSTMT 00A62528
SQLLEN * 0x0021FC60

python issue_1 d88-bf4 EXIT SQLRowCount with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
SQLLEN * 0x0021FC60 (1)

python issue_1 d88-bf4 ENTER SQLNumResultCols
HSTMT 00A62528
SWORD * 0x0021FC58

python issue_1 d88-bf4 EXIT SQLNumResultCols with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
SWORD * 0x0021FC58 (1)

python issue_1 d88-bf4 ENTER SQLDescribeCol
HSTMT 00A62528
UWORD 1
UCHAR * 0x0021FB58
SWORD 200
SWORD * 0x0021FB4C
SWORD * 0x0021FB40
SQLULEN * 0x0021FB44
SWORD * 0x0021FB48
SWORD * 0x0021FB54

python issue_1 d88-bf4 EXIT SQLDescribeCol with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
UWORD 1
UCHAR * 0x0021FB58 [ 1] "x"
SWORD 200
SWORD * 0x0021FB4C (1)
SWORD * 0x0021FB40 (3)
SQLULEN * 0x0021FB44 (2)
SWORD * 0x0021FB48 (1)
SWORD * 0x0021FB54 (0)

python issue_1 d88-bf4 ENTER SQLDescribeCol
HSTMT 00A62528
UWORD 1
UCHAR * 0x0021FB08
SWORD 300
SWORD * 0x00000000
SWORD * 0x0021FAFC
SQLULEN * 0x0021FAEC
SWORD * 0x0021FAF4
SWORD * 0x0021FAF0

python issue_1 d88-bf4 EXIT SQLDescribeCol with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
UWORD 1
UCHAR * 0x0021FB08
SWORD 300
SWORD * 0x00000000
SWORD * 0x0021FAFC (3)
SQLULEN * 0x0021FAEC (2)
SWORD * 0x0021FAF4 (1)
SWORD * 0x0021FAF0 (0)

python issue_1 d88-bf4 ENTER SQLFetch
HSTMT 00A62528

python issue_1 d88-bf4 EXIT SQLFetch with return code 0
(SQL_SUCCESS)
HSTMT 00A62528

python issue_1 d88-bf4 ENTER SQLGetData
HSTMT 00A62528
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x0021FC60
SQLLEN 7
SQLLEN * 0x0021FC78

python issue_1 d88-bf4 EXIT SQLGetData with return code 0
(SQL_SUCCESS)
HSTMT 00A62528
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x0021FC60 [ 3] "3.5"
SQLLEN 7
SQLLEN * 0x0021FC78 (3)

python issue_1 d88-bf4 ENTER SQLFetch
HSTMT 00A62528

python issue_1 d88-bf4 EXIT SQLFetch with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 00A62528

[mkleehammer]

This has been fixed in 2.1.5. Thanks for the troubleshooting
information.

It was a missing 'break' in a select statement. I haven't made that
mistake in eons. I'm surprised the C compiler didn't warn...

[alexander...@gmx.net]

Great, with 2.1.5. the output is:

C:\>python < issue_1.py
type(column in Row) : <class 'decimal.Decimal'>

type_code in Cursor.description: <class 'decimal.Decimal'>

C:\>

Thank you very much!

Alexander

[mkleehammer]

Alexander,

Thanks for the book!