Creating column operator / function

[svitek...@googlemail.com]

Hi.

I want to create a function or operator, that will convert column or
string from utf-8 encoding to ascii.
I'm not sure, how function / operators work in SA. So let's start from
the beginnig:
1) using postgresql 8.3, sqlalchemy 0.5.3

2) had created function in pgsql:
CREATE FUNCTION to_ascii(bytea, name)
RETURNS text AS 'to_ascii_encname' LANGUAGE internal RETURNS NULL ON
NULL INPUT;

3) converting from utf-8 to ascii:
to_ascii(convert_to('čačašaša','latin2'), 'latin2')
result: cacasasa

4) using it like this:
... WHERE to_ascii(convert_to(<column_name>,'latin2'), 'latin2') ILIKE
to_ascii(convert_to(<non_ascii_str>,'latin2'), 'latin2')

5) my vision is to use it like this:
session.query(Client).filter( ascii(Client.name).ilike(ascii(name)).all
()
or
session.query(Client).filter( ascii(Client.name).ascii_ilike(name).all
()
or something other :)

I have tried this, but had no luck:
def ascii_op(val, enc='latin2'):
return "to_ascii(convert_to(%(val)s,'%(enc)s'), '%(enc)s')" % dict
(val=val, enc=enc)

def ascii(column):
return _UnaryExpression(column, modifier=ascii_op)

returning:
WHERE client.name <function ascii_op at 0xb7901ed4> ILIKE mario
<function ascii_op at 0xb7901ed4>

thx 4 help guys.. :)

[Michael Bayer]

if you can make just a single "ascii()" stored procedure, then you can
say:

query(Client).filter(func.ascii("foo") == "bar")

or you can combine those

def ascii(arg):
return func.to_ascii(func.convert_to(arg, "latin2"))

otherwise you can use ext.compiler:

from sqlalchemy.sql.expression import Function
from sqlalchemy.ext.compiler import compiles

class ascii_op(Function):
def __init__(self, val, enc='latin2'):
self.val = val
self.enc = enc

@compiles(ascii_op)
def compile_ascii_op(element, compiler, **kw):

return "to_ascii(convert_to(%(val)s,'%(enc)s'), '%(enc)s')" %

element.__dict__

[Pavel m_ax Svitek]

This solution works great and it's quick:

from sqlalchemy.sql.expression import func
def ascii(arg):
return func.to_ascii(func.convert_to(arg, "'latin2'"), "'latin2'")

Thanks Michael!

On Jul 17, 5:20 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: