Enrico, It should be available on 0.7.10 Simon, you are right. The expression is indeed a must.
class Plan(Base): @hybrid_property def calculated_date(self): return date(self.year, self.month, self.day) @calculated_date.expression def calculated_date(self): return sa.cast(self.year + '-' + self.month + '-' + self.day, sa.Date) -----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Simon King Sent: Friday, October 04, 2013 7:01 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Query for date between a range I'm not sure that will work on it's own, will it? When used in a class context (Plan.calculated_date), you will end up calling the date function with 3 SQLAlchemy column objects, which won't work. At a minimum, you'd need this: class Plan(Base): @hybrid_property def calculated_date(self): return date(self.year, self.month, self.day) @calculated_date.expression def calculated_date(cls): # I suspect that what you put in here depends on the database return sa.cast(cls.year + '-' + cls.month + '-' + cls.day, sa.Date) On Fri, Oct 4, 2013 at 2:25 PM, Ofir Herzas <herz...@gmail.com> wrote: > I'm sorry, you should use hybrid_property: > > from sqlalchemy.ext.hybrid import hybrid_property > > class Plan(Base): > @hybrid_property > def calculated_date(self): > return date(self.year, self.month, self.day) > > > Also, in your query, don't use between: > session.query(Plan).\ > filter(Plan.calculated_date >= from_date).\ > filter(Plan.calculated_date <= to_date)) > > Cheers, > Ofir > > -----Original Message----- > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] > On Behalf Of Enrico Morelli > Sent: Friday, October 04, 2013 4:05 PM > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] Query for date between a range > > On Fri, 4 Oct 2013 15:55:07 +0300 > "Ofir Herzas" <herz...@gmail.com> wrote: > >> You can create a custom field in your model and check against it: >> >> class Plan(Base): >> . >> . >> . >> @property >> def calculated_date(self): >> return date(self.year, self.month, self.day) >> >> >> Then, in your query, use that field: >> session.query(Plan).filter(Plan.calculated_date.between(from_date, >> to_date)) >> >> Haven't checked it myself, but I guess it should work ... > > Thanks, but now I receive the error: > > AttributeError: 'property' object has no attribute 'between' > >> >> -----Original Message----- >> From: sqlalchemy@googlegroups.com >> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli >> Sent: Friday, October 04, 2013 12:07 PM >> To: sqlalchemy@googlegroups.com >> Subject: [sqlalchemy] Query for date between a range >> >> Dear all, >> >> I've a table where the date is separated in single fields, one for >> year, one for day and one for month. So I need to query for a date >> range. I search in Internet and I found the following query that >> seems to be works: SELECT * FROM plan WHERE year * 10000 + month * >> 100 + day BETWEEN +'20130101' AND '20130131'; >> >> Now I'm trying to translate to sqlalchemy, but I receive the >> following >> error: >> >> DataError: (DataError) invalid input syntax for integer: >> "2013-01-01T00:00:00" LINE 3: ...year * 1000 + plan.month * 100 + >> plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, >> plan.month AS plan_month, plan.instrument_id AS plan_instrument_id, >> count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year * >> %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s >> AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data, >> plan.month, plan.instrument_id ORDER BY month, instrument_id' >> {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0), >> 'month_1': 100, 'year_1': 1000, 'param_2': >> datetime.datetime(2013, 10, 3, 0, 0)} >> >> >> The latest attempt to write the correct code is the following (the >> range come from a web form using a javascript plugin): >> >> from_date = request.POST.get('from_date', '') to_date = >> request.POST.get('to_date', '') from_date = >> datetime.strptime(from_date, '%Y-%m-%d') to_date = >> datetime.strptime(to_date, '%Y-%m-%d') if from_date.day < 10: >> day = "0%s" % from_date.day >> else: >> day = "%s" % from_date.day >> if from_date.month < 10: >> month = "0%s" % from_date.month >> else: >> month = "%s" % from_date.month >> if to_date.day < 10: >> tday = "0%s" % to_date.day >> else: >> tday = "%s" % to_date.day >> if to_date.month < 10: >> tmonth = "0%s" % to_date.month >> else: >> tmonth = "%s" % to_date.month >> fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % >> (to_date.year, tmonth, tday) print fd, td results = >> Session.query(Plan.data, >> Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter( >> and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date, >> to_date), Plan.data.ilike('%%%s%%' % item),)).group_by( >> Plan.data, Plan.month, >> Plan.instrument_id ).order_by('month', 'instrument_id').all() >> >> Where I'm wrong? >> >> Thanks to all >> -- >> ------------------------------------------------------------- >> Enrico Morelli >> System Administrator | Programmer | Web Developer >> >> CERM - Polo Scientifico >> Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY >> phone: +39 055 457 4269 >> fax: +39 055 457 4253 >> ------------------------------------------------------------- >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, >> send an email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > > -- > ------------------------------------------------------------- > Enrico Morelli > System Administrator | Programmer | Web Developer > > CERM - Polo Scientifico > Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY > phone: +39 055 457 4269 > fax: +39 055 457 4253 > ------------------------------------------------------------- > > -- > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > -- > You received this message because you are subscribed to the Google Groups "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.