Hi!
> 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")
However, it doesn't work for me (v0.7.6), or am I doing something wrong? -
----<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.
On Wednesday, March 14, 2012 7:59:00 PM UTC, Michael Bayer wrote:
>
>
> 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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/G9x0M7o8WbkJ.
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.
