On Feb 5, 2012, at 7:50 PM, Anthony Foglia wrote:
> How do I set up an engine so that Oracle Number fields are not
> converted to Decimal?
>
> I'm dealing with a bunch of code with hand-rolled SQL query strings,
> and as the first step was going to use connections from SqlAlchemy's
> connection pool, as I change the queries one-by-one. The only problem
> is that our main queries get 500 Number columns and SqlAlchemy is
> converting them to Decimals, slowing the queries down by a factor of
> 10.
>
> Here's an example
>
>>>> engine = sqlalchemy.create_engine("oracle+cx_oracle://"+connString)
>>>> conn = engine.pool.connect()
>>>> cursor = conn.cursor()
>>>> cursor.execute("""SELECT * FROM MY_TABLE""")
>>>> r = cursor.fetchone()
>>>> r[-1]
> Decimal('0.878935370620606')
>
> The conversion is done via the outputtypehandler of the connection
> object. I can circumvent it by either setting
>
>>>> cursor.connection.outputtypehandler = None
>
> or
>
>>>> cursor.outputtypehandler = lambda *args : None
>
> but is there a better, more standard way to convert the Numbers to
> floats and not Decimals for arbitrary queries?
>
> (Setting engine.dialect.supports_native_decimal to False doesn't work.
> I believe the dialect is creating the output type handler before any
> connection is opened.)
The output handler is there because otherwise cx_oracle immediately converts to
Python float, which is then a lossy format. So we need to turn that off on
cx_oracle's side immediately. There's a lot of discussion of this at
http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#precision-numerics .
My first suggestion would be to just use cdecimal. That way performance would
not be an issue - I patch it in at program start time using the example at
http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.Numeric .
I've been using this approach in production financial applications for
several years without issue.
If you use the Float() type, the Decimal will be coerced into a regular
float(), but you're looking for performance here so that's not the solution.
There's not a public API right now to turn off this handling - it would imply
the _OracleNumeric type and other parts of the cx_oracle dialect would need to
be further complicated to support two modes of operation, and it was enormously
difficult to get precision numeric round trips as it is. A monkeypatch that
would force it off would be:
engine = create_engine(...)
engine.dialect._to_decimal = float
another way you could do it would be to use a connection pool event. Using
the connect event:
from sqlalchemy import event
@event.listens_for(engine, "connect")
def connect(connection, rec):
connection.outputtypehandler = None
Note that disabling the outputtypehandler will also mess up unicode handling,
unless you replace it with another output handler that returns a "unicode"
cursor.var().
--
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.
