Scratch the last, helps if I have my development.ini pointed to the proper database server :) Its working fine. Thanks for all the help :)
On Feb 2, 11:24 am, "Andy (Zenom)" <stress...@gmail.com> wrote: > 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.
