Oracle NUMBER casting to FLOAT

[ron.dunn]

Columns which are declared as NUMBER are being returned by the driver as Python FLOAT values.

This seems incorrect, as they have no decimal places, and should probably be returned as an INTEGER.

Is there any way to force this behaviour at the driver level?

[AlexP]

Hello,

Please provide the code you are using to define field type, since correct data is displayed when using the description method. For example, for the Dept table, whose Deptno field is declared as NUMBER(2, 0), the field is defined as <class 'int'>.

Code: Select all

>>> import pyodbc
>>> con = pyodbc.connect('DSN=ora')
>>> cur = con.cursor()
>>> cur.execute("select * from dept");
<pyodbc.Cursor object at 0x034855D0>
>>> cur.description
(('DEPTNO', <class 'int'>, None, 2, 2, 0, False), ('DNAME', <class 'str'>, None, 14, 14, 0, True), ('LOC', <class 'str'>, None, 13, 13, 0, True))
>>> 

[ron.dunn]

Here is a table definition taken from the CHINOOK sample database.

Code: Select all

CREATE TABLE InvoiceLine
(
    InvoiceLineId NUMBER NOT NULL,
    InvoiceId NUMBER NOT NULL,
    TrackId NUMBER NOT NULL,
    UnitPrice NUMBER(10,2) NOT NULL,
    Quantity NUMBER NOT NULL,
    CONSTRAINT PK_InvoiceLine PRIMARY KEY  (InvoiceLineId)
);

I'm running this database on AWS RDS Oracle.

Here is an equivalent script to yours, which shows that a column defined as NUMBER, not NUMBER(X,0), is being returned as FLOAT. An interesting point is that both precision and scale are being returned as 38 in this case.

Code: Select all

>>> import pyodbc
>>> connection_string = """
... 	Driver={Devart ODBC Driver for Oracle}; 
... 	Server=XXX; 
... 	SID=XXX; 
... 	Username=XXX;
... 	Password=XXX;
... 	UseUnicode=1; 
... 	Direct=1"""
... 
>>> con = pyodbc.connect (connection_string)
>>> cur = con.cursor()
>>> cur.execute ('select * from chinook.invoiceline')
<pyodbc.Cursor object at 0x0074F2C0>
>>> cur.description
(('INVOICELINEID', <class 'float'>, None, 38, 38, 38, False), ('INVOICEID', <class 'float'>, None, 38, 38, 38, False), ('TRACKID', <class 'float'>, None, 38, 38, 38, False), ('UNITPRICE', <class 'float'>, None, 10, 10, 2, False), ('QUANTITY', <class 'float'>, None, 38, 38, 38, False))
>>> 

[AlexP]

According to the Oracle documentation, if precision and scalе are not set for a NUMBER field, then such field may contain values from 1 x 10^-130 to 9.99...9 x 10^125 up to 38 significant digits. Because of that we set precision and scalе to the value of 38.