On Mar 14, 2012, at 4:45 AM, marq wrote:
> Hello,
>
> 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
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")
>
> 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?
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
