I do this, and the query it spits out in the error works fine in the postgresql command line, but not in sqlalchemy.
Traceback: http://pylonshq.com/tracebacks/9b322f31095beda23f5d846e43399d21 query: 'SELECT DISTINCT cms.niches.title AS cms_niches_title, cms.niches.niche_id AS cms_niches_niche_id, cms.slugify (cms.niches.title) AS slug \nFROM cms.niches JOIN cms.dvd_sites_niches ON cms.niches.niche_id = cms.dvd_sites_niches.niche_id ORDER BY cms.niches.title ASC' Here is my function: CREATE OR REPLACE FUNCTION cms.slugify(character varying) RETURNS character varying AS $BODY$ import re import unicodedata value = unicode(args[0]) value = value.replace("&", "") value = value.replace("_", "-") value = unicodedata.normalize("NFKD", value).encode("ascii", "ignore") value = unicode(re.sub("[^\w\s-]", "", value).strip().lower()) return re.sub("[-\s]+", "-", value) $BODY$ LANGUAGE 'plpythonu' VOLATILE; Again the function works in like pgadmin3 etc, but not in sqlalchemy. On Feb 2, 11:01 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Andy (Zenom) wrote: > > I got the server side one created in schema cms, function name is > > slugify. > > > I am trying to figure out how to let sqlalchemy know about the > > function and the schema its in. On postgresql I can do like SELECT > > cms.slugify(title) FROM cms.tablename, and it returns the slugified > > title, but to get that in sqlalchemy? Sorry should have been more > > clear. I was "assuming" I needed to somehow create my own sqlalchemy > > function, but havn't found the right answer yet. > > so that's column_property(func.cms.slugify(title)) . > > > > > > > On Feb 2, 10:50 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > >> Andy (Zenom) wrote: > >> > The one thing that is frustrating is it is re-querying the database. > >> > So if I create a simple server side function how would I do that? I am > >> > currently using trunk. > > >> how to create the server side function or how to use it from SQLA ? the > >> column_property() thing is where you'd use func.foo(). > > >> > On Feb 2, 8:57 am, Andy <andy.hol...@belator.com> wrote: > >> >> Thanks, that worked like a charm. Look forward to your PyCon > >> >> presentation. > > >> >> On Feb 2, 12:56 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > > >> >> > On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote: > > >> >> > > I don't know if my terminology is correct etc. But the basic idea > >> is > >> >> > > the following. > > >> >> > > In my model I have something like > > >> >> > > from mylib.helpers import slugify > > >> >> > > class MyModel(Base): > >> >> > > __tablename__ = "testing" > > >> >> > > id = Column(Integer, primary_key) > >> >> > > title = Column(Unicode) > >> >> > > description = Column(Unicode) > > >> >> > > @property > >> >> > > def slug(self): > >> >> > > return slugify(self.title) > > >> >> > > Now I want to be able to do something like. > >> >> > > session.query(MyModel.id, MyModel.title, MyModel.slug).all() > > >> >> > > The table obviously doesn't have a slug column, I just want to > >> >> return > >> >> > > my title as a "pseudo-column" named slug, with it passed through > >> my > >> >> > > slugify method first. > > >> >> > > I tried to do something like the following: > > >> >> > > @synonym_for("title") > >> >> > > @property > >> >> > > def slug(self): > >> >> > > return slugify(self.title) > > >> >> > > And this did not work. It seems to just return another column > >> just > >> >> > > like title (not sent through slugify first). Any idea how I can > >> do > >> >> > > what I am looking for? > > >> >> > part of the approach depends on if "slugify" can be defined as a > >> SQL > >> >> function or if it must be executed in Python. If the latter, you > >> >> probably want MyModel.slug to resolve into a column that also defines > >> >> a custom type. > > >> >> > Assuming its a Python function, note that your column can't be used > >> >> effectively in a comparison operation, like "MyModel.slug == 'foo'", > >> >> since the slugify() operation would have to be applied in aggregate. > > >> >> > Anyway assuming non-SQL function, here's a hack I came up with that > >> >> will do it: > > >> >> > from sqlalchemy import * > >> >> > from sqlalchemy.orm import * > >> >> > from sqlalchemy.ext.declarative import declarative_base > >> >> > from sqlalchemy.sql.expression import _UnaryExpression > >> >> > from sqlalchemy.types import UserDefinedType > > >> >> > Base = declarative_base() > > >> >> > def slugify(text): > >> >> > return "SLUG !" + text > > >> >> > class MySlugType(UserDefinedType): > >> >> > def result_processor(self, dialect, coltype): > >> >> > return slugify > > >> >> > class MyModel(Base): > >> >> > __tablename__ = "testing" > > >> >> > id = Column(Integer, primary_key=True) > >> >> > title = Column(Unicode) > >> >> > description = Column(Unicode) > > >> >> > slug = column_property(_UnaryExpression(title, > >> >> type_=MySlugType())) > > >> >> > engine = create_engine('sqlite://', echo=True) > >> >> > Base.metadata.create_all(engine) > >> >> > session = sessionmaker(engine)() > > >> >> > session.add(MyModel(title=u'title', description=u'description')) > >> >> > session.commit() > > >> >> > print session.query(MyModel.title, MyModel.slug).all() > > >> >> > > -- > >> >> > > You received this message because you are subscribed to the > >> Google > >> >> Groups "sqlalchemy" group. > >> >> > > To post to this group, send email to sqlalch...@googlegroups.com. > >> >> > > To unsubscribe from this group, send email to > >> >> sqlalchemy+unsubscr...@googlegroups.com. > >> >> > > For more options, visit this group > >> >> athttp://groups.google.com/group/sqlalchemy?hl=en. > > >> > -- > >> > You received this message because you are subscribed to the Google > >> Groups > >> > "sqlalchemy" group. > >> > To post to this group, send email to sqlalch...@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. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
