Reflecting user defined types
marq
I'm a beginner in SQLAlchemy - apologies if I'm asking for the
obvious...
I have a user defined type for Oracle (w/ SQLAlchemy 0.7.5)
class OracleBinaryDouble(sqlalchemy.types.UserDefinedType):
"""Native double / double precision floating point data type
for Oracle.
"""
def get_col_spec(self):
return "BINARY_DOUBLE"
which is then used in a type decorator to construct a matching user
defined type for all engines:
class BinaryDouble(sqlalchemy.types.TypeDecorator):
"""Platform-independent double precision floating point type.
Uses MySQL's DOUBLE and Oracle's BINARY_DOUBLE data type.
"""
impl = sqlalchemy.types.Numeric
def load_dialect_impl(self, dialect):
if dialect.name == 'mysql':
return
dialect.type_descriptor(sqlalchemy.dialects.mysql.FLOAT(53))
elif dialect.name == 'sqlite':
return
dialect.type_descriptor(sqlalchemy.dialects.sqlite.FLOAT)
elif dialect.name == 'oracle':
return dialect.type_descriptor(OracleBinaryDouble)
elif dialect.name == 'postgresql':
return
dialect.type_descriptor(sqlalchemy.dialects.postgresql.DOUBLE_PRECISION)
else:
return dialect.type_descriptor(FLOAT(53)) # 53+ bit
mantissa is a double precision number
When defining a database table, i.e. like
meta = MetaData()
level1a = Table('level1a', meta,
Column('l1a_file', String(333),
primary_key = True),
Column('lon', BinaryDouble),
Column('lat', BinaryDouble)
)
working with that table is fine, and works as intended (i.e., storing
double precision floating point numbers as BINARY_DOUBLE in Oracle.
When reflecting this table in Oracle, as in
Base = sqlalchemy.ext.declarative.declarative_base()
class Level1a(Base):
__table__ = sqlalchemy.Table("level1a", Base.metadata,
autoload = True, autoload_with =
engine)
I receive warnings from the Oracle driver, saying
.../python2.7/site-packages/sqlalchemy/engine/reflection.py:47:
SAWarning: Did not recognize type 'BINARY_DOUBLE' of column 'lon'
ret = fn(self, con, *args, **kw)
.../python2.7/site-packages/sqlalchemy/engine/reflection.py:47:
SAWarning: Did not recognize type 'BINARY_DOUBLE' of column 'lat'
ret = fn(self, con, *args, **kw)
What am I missing (or doing wrong)? I assume that the BinaryDouble
type must be registered somehow, but how?
Many thanks,
Christian.
Michael Bayer
Some comments. If you use sqlalchemy.types.FLOAT(53), you'll get exactly FLOAT(53) in MySQL and SQLite, so you don't need to switch for those.
Next, we have the "different types for different backends" built in a more accessible way via with_variant():
my_float = FLOAT(53).with_variant(postgresql.DOUBLE_PRECISION(), "postgresql").with_variant(OracleBinaryDouble, "oracle")
there's no first class API at the moment to register database types with the reflection dictionary; however, if you were to populate your string into sqlalchemy.dialects.oracle.base.ischema_names that would have the desired effect:
from sqlalchemy.dialects.oracle.base import ischema_names
ischema_names['BINARY_DOUBLE'] = OracleBinaryDouble
though we also can add these names to the distribution as people request.
The other way these types can be set at reflection time is by overriding the column, though this is a less automated:
Table("name", metadata, Column("double_col", OracleBinaryDouble), autoload=True)
I also had the thought that we could make this doable with the "column_reflect" event....though that won't give you what you need for now. Try out the ischema_names thing for now.
marq
> Try out the ischema_names thing for now.
Works like a charm - thanks a lot!
I know about manually overwriting column types (which works a sw ell of course), but I wanted to keep the application independent from the table structure (which is in a migrate repository)...
This looks very nice:
>
my_float = FLOAT(53).with_variant(postgresql.DOUBLE_PRECISION(), "postgresql").with_variant(OracleBinaryDouble, "oracle")
----<code>---------
# Imports
import sqlalchemy
import sqlalchemy.types
import sqlalchemy.orm
import sqlalchemy.dialects
# Define User customized type
class OracleBinaryDouble(sqlalchemy.types.UserDefinedType):
def get_col_spec(self):
return "BINARY_DOUBLE"
BinaryDouble = sqlalchemy.types.FLOAT(53).with_variant(OracleBinaryDouble, "oracle")
# Database connection
engine = sqlalchemy.create_engine("oracle://XXX:ppp@tcdbs2:1521/YYY")
conn = engine.connect()
metadata = sqlalchemy.MetaData()
# Define a table using the custom data type
table = sqlalchemy.Table('test_table', metadata, sqlalchemy.Column('double', BinaryDouble))
# Create and insert something
metadata.create_all(engine)
conn.execute(table.insert(), double = 5.0)
conn.close()
----</code>------------
which throws an error:
Traceback (most recent call last):
File "sqla_cust_types.py", line 30, in <module>
conn.execute(table.insert(), double = 5.0)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1450, in execute
params)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement
compiled_sql, distilled_params
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1650, in _execute_context
None, None)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
context = constructor(dialect, self, conn, *args)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in _init_compiled
processors = compiled._bind_processors
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 485, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 287, in _bind_processors
for bindparam in self.bind_names )
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 284, in <genexpr>
(key, value) for key, value in
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 287, in <genexpr>
for bindparam in self.bind_names )
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/types.py", line 201, in _cached_bind_processor
d = self._dialect_info(dialect)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/types.py", line 226, in _dialect_info
impl = self._gen_dialect_impl(dialect)
File "/homespace/grasppf/linux/11.1/intel/lib/python2.7/site-packages/sqlalchemy/types.py", line 492, in _gen_dialect_impl
typedesc = self.load_dialect_impl(dialect).dialect_impl(dialect)
sqlalchemy.exc.StatementError: unbound method dialect_impl() must be called with OracleBinaryDouble instance as first argument (got OracleDialect_cx_oracle instance instead) (original cause: TypeError: unbound method dialect_impl() must be called with OracleBinaryDouble instance as first argument (got OracleDialect_cx_oracle instance instead)) 'INSERT INTO test_table (double) VALUES (:double)' [{'double': 5.0}]
Thanks again!
Christian.