sqlalchemy version 0.5.3 sqlalchemy.text() used to return float
results when the database type was numeric while sqlalchemy.select()
would return decimal results, which was the desired result. In order
to avoid this I used an output type handler to ensure that all NUMERIC
results from the cx_Oracle connection were always decimals.
With the upgrade to 0.6.5, sqlalchemy.text() returns decimals
regardless of whether the cx_Oracle output type handler was used or
not.
However, sqlalchemy.select() with autoload=True returns float values
when the Oracle column type is NUMBER with no precision defined
regardless of whether the cx_Oracle output type handler was used or
not.
Is it a bug that sqlalchemy.select() is returning float values (is
this behavior different in 0.6.6 different), and is there a way to get
sqlalchemy to consistently return decimal values for Oracle NUMERIC
columns while still using autoload=True.
Here is an example showing all the returned type combinations:
#!/usr/bin/env python
import decimal
import datetime
import cx_Oracle
import sqlalchemy as sa
# msg_seq_nb integer
# lsale_id number
# dlayd_days_ct number(2)
# entrd_pr number(18,8)
query = """
select msg_seq_nb, lsale_id, dlayd_days_ct, entrd_pr
from TRCOWN1.LSALE_MSTR
where trd_rpt_dt = '2 Nov 2010' and
dlayd_days_ct is not Null and
entrd_pr is not Null
"""
connection_string = "TRCREAD/xxxxxxx...@tdr3d"
cx_oracle_connection = cx_Oracle.Connection(connection_string)
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
def print_results(results_title, results):
print results_title
for row in results:
print '\t' + '\n\t'.join(map(repr, zip(map(type, row), row)))
break
print
print_results('basic cx_Oracle result', cursor.fetchall())
cursor.close()
def float_as_decimal(cursor, name, defaultType, size, precision,
scale):
if defaultType == cx_Oracle.NUMBER:
return cursor.var(str, 100, cursor.arraysize,
outconverter=decimal.Decimal)
cx_oracle_connection.outputtypehandler = float_as_decimal
cursor = cx_Oracle.Cursor(cx_oracle_connection)
cursor.execute(query)
print_results('cx_Oracle result with type converter',
cursor.fetchall())
cursor.close()
engine = sa.create_engine('oracle://trcr...@tdr3d',
connect_args={'password': ' XXXXXXXXXX '})
metadata = sa.MetaData(engine)
sqlalchemy_connection = engine.connect()
def get_sa_text_rows():
selector = sa.text(query)
return sqlalchemy_connection.execute(selector)
def get_sa_select_rows():
trd_mstr = sa.Table('LSALE_MSTR', metadata, autoload=True,
schema='TRCOWN1')
selector = sa.select([trd_mstr.c.msg_seq_nb,
trd_mstr.c.lsale_id,
trd_mstr.c.dlayd_days_ct,
trd_mstr.c.entrd_pr],
whereclause=((trd_mstr.c.trd_rpt_dt ==
datetime.datetime(2010, 11, 2)) &
(trd_mstr.c.dlayd_days_ct !=
None) &
(trd_mstr.c.entrd_pr != None)),
)
return sqlalchemy_connection.execute(selector)
print_results('basic sqlalchemy text result', get_sa_text_rows())
print_results('basic sqlalchemy select result', get_sa_select_rows())
sqlalchemy_connection.connection.connection.outputtypehandler =
float_as_decimal
print_results('sqlalchemy text result with type converter',
get_sa_text_rows())
print_results('sqlalchemy select result with type converter',
get_sa_select_rows())
sqlalchemy_connection.close()
#Output
#basic cx_Oracle result
# (<type 'int'>, 16043)
# (<type 'int'>, 85784770)
# (<type 'int'>, 0)
# (<type 'float'>, 100.274)
#
#cx_Oracle result with type converter
# (<class 'decimal.Decimal'>, Decimal('16043'))
# (<class 'decimal.Decimal'>, Decimal('85784770'))
# (<class 'decimal.Decimal'>, Decimal('0'))
# (<class 'decimal.Decimal'>, Decimal('100.274'))
#
#basic sqlalchemy text result
# (<type 'int'>, 16043)
# (<type 'int'>, 85784770)
# (<type 'int'>, 0)
# (<class 'decimal.Decimal'>, Decimal('100.274'))
#
#basic sqlalchemy select result
# (<type 'int'>, 16043)
# (<type 'float'>, 85784770.0)
# (<type 'int'>, 0)
# (<class 'decimal.Decimal'>, Decimal('100.274'))
#
#sqlalchemy text result with type converter
# (<class 'decimal.Decimal'>, Decimal('16043'))
# (<class 'decimal.Decimal'>, Decimal('85784770'))
# (<class 'decimal.Decimal'>, Decimal('0'))
# (<class 'decimal.Decimal'>, Decimal('100.274'))
#
#sqlalchemy select result with type converter
# (<class 'decimal.Decimal'>, Decimal('16043'))
# (<type 'float'>, 85784770.0)
# (<class 'decimal.Decimal'>, Decimal('0'))
# (<class 'decimal.Decimal'>, Decimal('100.274'))
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
