user defined type

[werner]

Hi,

I am using some custom types which "just about" work, i.e. I just have
an issue when I do this type of query.

result = result.filter(db.Drinkinfo.namesandvar.startswith('cloudy'))

It throws the following exception, which is due to my custom type not
being correctly setup.

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-902,
'isc_dsql_prepare: \n Dynamic SQL Error\n expression evaluation not
supported\n Strings cannot be added or subtracted in dialect 3') "SELECT
...
cellarbook.updated_by AS cellarbook_updated_by \nFROM cellarbook JOIN
vintage ON vintage.id = cellarbook.fk_vintage_id JOIN drinkinfo ON
drinkinfo.id = vintage.fk_drinkinfo_id \nWHERE vintage.avgscore BETWEEN
? AND ? AND drinkinfo.name + ? + drinkinfo.name2 + (? ||
drinkinfo.variety) LIKE ? || '%%'" (80, 95, ', ', ', ', 'cloudy')
File "c:\dev\twcbv4\twcbsrc\test3to4\saTest.py", line 57, in <module>
for item in result:
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py",
line 1839, in __iter__
return self._execute_and_instances(context)
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py",
line 1854, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py",
line 1399, in execute
params)
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py",
line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py",
line 1640, in _execute_context
context)
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py",
line 1633, in _execute_context
context)
File
"c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\dialects\firebird\base.py",
line 692, in do_execute
cursor.execute(statement, parameters or [])

The columns are defined like:

name = sa.Column(cts.NAMES40(), default=u'', index=True)
name2 = sa.Column(cts.NAMES40(), default=u'', index=True)
variety = sa.Column(sa.Unicode(length=40), default=u'')

@hybrid_property
def namesandvar(self):
nVar = self.name
if self.name2:
nVar += ', ' + self.name2
if self.variety:
nVar += ', ' + self.variety
return nVar

I must be doing something wrong when defining my "NAMES40" custom type
as when I change it to use Unicode the query works fine.

I used custom types but maybe there are even easier/better ways to do
what I like to do (standard length and collation, would be nice to also
have index=True), anyhow this is how I define it.

In Firebird SQL I define a domain:

CREATE DOMAIN NAMES40 AS
VARCHAR(40) CHARACTER SET UTF8
COLLATE UNICODE_CI_AI;

and then have this as the custom type:

class NAMES40(sa.types.UserDefinedType):

impl = sa.Unicode

def get_col_spec(self):
return "NAMES40"

def bind_processor(self, dialect):
def process(value):
return value
return process

def result_processor(self, dialect, coltype):
def process(value):
return value
return process

Can anyone point me to more samples using UserDefinedType and
TypeDecorator, looked at the UsageRecipes and the documentation but
obviously can't quit put it together for my use.

Werner

[Michael Bayer]

those + signs should probably be concatenation operators. You'll get concatenation as long as the type of column includes "types.Concatenable" in its inheritance hierarchy.

[werner]

Just for the archive,

On 08/02/2012 16:47, Michael Bayer wrote:
> @hybrid_property
> def namesandvar(self):
> nVar = self.name
> if self.name2:
> nVar += ', ' + self.name2
> if self.variety:
> nVar += ', ' + self.variety
> return nVar
>
> I must be doing something wrong when defining my "NAMES40" custom type as when I change it to use Unicode the query works fine.
> those + signs should probably be concatenation operators. You'll get concatenation as long as the type of column includes "types.Concatenable" in its inheritance hierarchy.

Went "back" to using straight sa.Unicode instead of my customtype and
changed the hybrid_property to the following:

@hybrid_property
def namesandvar(self):
if not self.variety in [None, u'']:
return self.drinknames + u", " + self.variety
else:
return self.drinknames

@namesandvar.expression
def namesandvar(cls):
return case([
(cls.variety != None, cls.drinknames + u", " + cls.variety),
], else_ = cls.drinknames)

Which is based on what I found in the doc here:
http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#mapper-sql-expressions

Werner