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
--
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.
