[sqlalchemy] Re: filtering by datetime elements
On Saturday 17 May 2008 17:53:42 Eric Abrahamsen wrote: Hi there, I'm new to this, so please be patient if I'm a little slow... I'm trying to filter Article objects by a datetime field ('pubdate'), and expected that I would be able to do something like this: arts = sess.query(Article).filter(and_(Article.pubdate.year==year, Article.pubdate.month==month, Article.id==id)).one() This gives me: AttributeError: 'InstrumentedAttribute' object has no attribute 'year' Apparently methods on attributes are reserved for sqlalchemy internals, and I'm not working with a straight Python object, as I'd thought. That makes sense, but can anyone suggest a simple substitute for what I'm trying to do here? it's SQL that is not working with python objects, and the column pubdate (associated with type DateTime on python side) has no attr .year or .month. lookup the messages in the group, there were some sugestions long time ago, but AFAIremember one was something with strings, another with separate columns. wishlist suggestion: how about type-related attributes on columns, using which will autogenerate some expr off that column? e.g. select( mytable.c.mydate.year == '1998') to automagicaly yield something like func.substr(mytable.c.mydate,1,4)) == '1998' (supposing the type of mydate stores datetime as mmdd string) ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering by datetime elements
On May 17, 2008, at 11:17 PM, [EMAIL PROTECTED] wrote: it's SQL that is not working with python objects, and the column pubdate (associated with type DateTime on python side) has no attr .year or .month. lookup the messages in the group, there were some sugestions long time ago, but AFAIremember one was something with strings, another with separate columns. Thanks svil, this is good to know. I suppose there's no reason why I can't pull a simpler query into Python and then filter it by date there. It seems like doing this in the SQL query is going to be hackish no matter what, particularly when it's so simple to do in Python... Thanks again, Eric --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering by datetime elements
On Sat, May 17, 2008 at 11:20 AM, Eric Abrahamsen [EMAIL PROTECTED] wrote: On May 17, 2008, at 11:17 PM, [EMAIL PROTECTED] wrote: it's SQL that is not working with python objects, and the column pubdate (associated with type DateTime on python side) has no attr .year or .month. lookup the messages in the group, there were some sugestions long time ago, but AFAIremember one was something with strings, another with separate columns. Thanks svil, this is good to know. I suppose there's no reason why I can't pull a simpler query into Python and then filter it by date there. It seems like doing this in the SQL query is going to be hackish no matter what, particularly when it's so simple to do in Python... you could convert your month variable to same format as article.pubdate (datetime) http://lucasmanual.com/mywiki/PythonManual#head-7b8d3475aa2baaa193b02b72fccd6eb009a1ee63 or modify the datetime to separate date and time columns. Lucas -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ TurboGears Manual-Howto http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering by datetime elements
On May 17, 2008, at 10:53 AM, Eric Abrahamsen wrote: Hi there, I'm new to this, so please be patient if I'm a little slow... I'm trying to filter Article objects by a datetime field ('pubdate'), and expected that I would be able to do something like this: arts = sess.query(Article).filter(and_(Article.pubdate.year==year, Article.pubdate.month==month, Article.id==id)).one() This gives me: AttributeError: 'InstrumentedAttribute' object has no attribute 'year' Apparently methods on attributes are reserved for sqlalchemy internals, and I'm not working with a straight Python object, as I'd thought. That makes sense, but can anyone suggest a simple substitute for what I'm trying to do here? two approaches here: 1. use datepart functions. currently these are specific to individual databases (heres the postgres version): filter(func.date_part(year, Class.somedate)==year) 2. use comparison/BETWEEN; this has the advantage in that if the column has an index on it, it can be used: filter(Class.somedate.between(date(year, 1, 1), date(year + 1, 1, 1))) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---