How to reflect an oracle database with BINARY_DOUBLE type columns

Question

I tried to reflect an existing oracle database into sqlalchemy metadata:

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table

db_uri = 'oracle://USER:PASS@MYDBTNSNAME'
engine = create_engine(db_uri)

# create a MetaData instance
metadata = MetaData()

# reflect db schema to MetaData
metadata.reflect(bind=engine)

This returns the following:

SAWarning: Did not recognize type 'BINARY_DOUBLE' of column 'column_1'(coltype, colname))

I have tried to import native types and also the types from dialect oracle using

from sqlalchemy.types import *
from sqlalchemy.dialects.oracle import *

but it seems it does not recognize BINARY_DOUBLE type

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-13-b69d481f6a4e> in <module>()
      1 from sqlalchemy.types import *
----> 2 from sqlalchemy.dialects.oracle import *

AttributeError: module 'sqlalchemy.dialects.oracle' has no attribute 'BINARY_DOUBLE'

I am using SQLAlchemy, version '1.2.1'

Probably you need this. Which mentioned the fix as from sqlalchemy.dialects.oracle.base import ischema_names and ischema_names['BINARY_DOUBLE'] = OracleBinaryDouble this — Tarun Lalwani
– Tarun Lalwani, Commented Mar 26, 2018 at 9:05
@TarunLalwani I tried it. If I didn't miss anything, it only maps the column type to NullType() after applying metadata.reflect() function. I need to map it to something like cx-oracle.readthedocs.io/en/latest/… — aturegano
– aturegano, Commented Mar 26, 2018 at 13:48
Unfortunately don't have oracle to test and debug what it does — Tarun Lalwani
– Tarun Lalwani, Commented Mar 26, 2018 at 13:52

Olaf Górski · Accepted Answer · 2018-04-01 19:33:19Z

1

Have you tried overriding the default mapping relfection in your db? Like so

from sqlalchemy.dialects.oracle.base import BINARY_DOUBLE

group_table = sa.Table('groups', metadata,
    sa.Column('your_column', BINARY_DOUBLE(asdecimal=True)),
    autoload=True,
    include_columns=[
    'your_column',
    '...'
    ],
)

Or just importing that BINARY_DOBULE from sqlalchemy.dialects.oracle.base

I'd ask that in a comment, but I can't do so as I just joined.

answered Apr 1, 2018 at 19:33

Olaf Górski

1645 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aturegano Over a year ago

Just importing BINARY_DOUBLE from sqlalchemy.dialects.oracle.base does assign NullType() when using metadata.reflect(bind=engine). Therefore, it does not solve the problem

aturegano · Accepted Answer · 2019-03-01 08:35:18Z

1

As described in sqlalchemy changelog, this functionality has been included in version 1.2.8:

[oracle] [bug] Added reflection capabilities for the oracle.BINARY_FLOAT, oracle.BINARY_DOUBLE datatypes

I have checked it using 1.2.18 version and now reflection works.

edited Mar 1, 2019 at 8:35

answered Mar 1, 2019 at 8:16

aturegano

1,0701 gold badge18 silver badges35 bronze badges

Comments

J_H · Accepted Answer · 2018-03-29 03:33:44Z

0

You're trying to use a column type that is not well supported by sqlalchemy / DB driver. So avoid that type.

Create a view as (roughly) SELECT * FROM base_table, with the minor tweak of casting binary double columns to a more convenient numeric type. Then reflect the view.

answered Mar 29, 2018 at 3:33

J_H

21.2k5 gold badges29 silver badges50 bronze badges

1 Comment

aturegano Over a year ago

I cannot change the database schema. My question is how to make SQLAlchemy handle BINARY_DOUBLE

Collectives™ on Stack Overflow

How to reflect an oracle database with BINARY_DOUBLE type columns

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related