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?
Oracle NUMBER casting to FLOAT
Re: Oracle NUMBER casting to FLOAT
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'>.
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))
>>>
Re: Oracle NUMBER casting to FLOAT
Here is a table definition taken from the CHINOOK sample database.
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
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)
);
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))
>>>
Re: Oracle NUMBER casting to FLOAT
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.