Hi,
I'm working on the Teradata external dialect and I want to be able to
infer, at the SQLAlchemy level, the expression type for binary operations
(specifically arithmetic) that involve types supported by the dialect. The
goal is to get them to match the types of binary expressions in Teradata.
For example, if we had the following Table where SMALLINT and INTEGER are
in sqlalchemy_teradata:
table = Table('table_test', metadata,
Column('c1', SMALLINT),
Column('c2', INTEGER))
If we get the type from a BinaryExpression:
print(type((table.c.c1 + table.c.c2).type))
We get:
<class 'sqlalchemy.sql.sqltypes.SMALLINT'>
But if we were to reflect a table with the same operator and operands:
engine.execute(
'CREATE Table t1 as ('
'SELECT c1 + c2 as sum_c1_c2 from table_test)')
t = Table('t1', metadata, autoload=True)
print(type(t.columns['sum_c1_c2'].type))
We would instead get:
<class 'sqlalchemy.sql.sqltypes.INTEGER'>
Which is what we expect from Teradata. So we would like:
print(type((table.c.c1 + table.c.c2).type))
to output:
<class 'sqlalchemy.sql.sqltypes.INTEGER'>
It looks like providing your own Comparator (comparator_factory) class is
the way to designate the resulting type of a BinaryExpression. I am able to
override this class in the relevant data types and then define my own
specific type inference behavior in the Comparator's _adapt_expression()
method. I'm playing around with something basic like the following for a
mixin for all the types implemented by the dialect (inspired by the
Comparator in _LookupExpressionAdapter):
class _TDComparable:
@property
def _expression_adaptations(self):
raise NotImplementedError()
class Comparator(TypeEngine.Comparator):
_blank_dict = util.immutabledict()
def _adapt_expression(self, op, other_comparator):
othertype = other_comparator.type.__class__
lookup = self.type._expression_adaptations.get(
op, self._blank_dict).get(
othertype, self.type)
return (op, lookup)
comparator_factory = Comparator
and then in a derived type like VARCHAR I can do something like:
class VARCHAR(_TDComparable, sqltypes.VARCHAR):
def __init__(self, length=None, charset=None, **kwargs):
super(VARCHAR, self).__init__(length=length, **kwargs)
self.charset = charset
@property
def _expression_adaptations(self):
return {
operators.concat_op: {
CLOB: CLOB
}
}
As far as I can tell, this is one of the more straightforward ways to
accomplish what I want.
I've noticed though that most of the keys and values in
_expression_adaptations for the types defined in sqltypes seem to be
generic types (probably because most of the underlying logic for type
inference revolves around the more general _type_affinity?). My approach
here is slightly different, and more specific. So maybe I should define my
own private method/property for each of my dialect-implemented type instead
of using _expression_adaptations? Is what I'm doing within the full support
of SQLAlchemy? Is there a better way to accomplish what I want?
Either way, any insight into this and/or suggestions of what I could do
would be greatly appreciated.
Thanks,
Wis
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
