On Nov 8, 2010, at 11:59 AM, ACB wrote: > 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. >
We now use an outputtypehandler that intercepts all numeric values as strings and coerces to Decimal or int, based on the presence of "precision" and "scale", or if p and s are ambiguous, we search the string for a decimal point to determine decimal or int. Your scheme of overriding this handler can be achieved more cleanly using a PoolListener, described at http://www.sqlalchemy.org/docs/core/interfaces.html#connection-pool-events . The connection received should already have the handler that the SQLA dialect applies, then yours would replace it. As far as reflection, per http://ss64.com/ora/syntax-datatypes.html we consider NUMERIC with no p or s to be "float", so the Float type is used, which by default ensures values are python floats, not Decimal. To override this type you'd have to use the technique at http://www.sqlalchemy.org/docs/core/schema.html#overriding-reflected-columns , and add the "asdecimal=True" flag to the Float type. When using a text() construct, no information is present about the types used unless you also specify a "typemap" parameter: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text , so you get back exactly what whatever outputtypehandler in use is generating. It would be nice if cx_oracle could do the work of decimal/float/int for us, though we have identified some situations, which typically involve subqueries, where the "p" and "s" values are ambiguous, and we really don't know what type should be used. So the Decimal/int phase allows the SQLAlchemy types later in the chain to receive a lossless value which it can then make decisions on regarding ultimate Python type. > 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 sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
