On Fri, 4 Oct 2013 16:03:18 +0200 Enrico Morelli <more...@cerm.unifi.it> wrote:
> On Fri, 4 Oct 2013 16:25:00 +0300 > "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 > > Argh!! I've to use and old version of sqlalchemy (0.6.8) that hasn't > the hybrid module. It's a dog that bites its own tail. Sigh! In which version I can found the hybrid module? My application is in a production environment and I'm afraid to update sqlalchemy. Usually I create a virtual environment where the python modules are frozen to avoid problems. > > > > -----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. > > > > -- ------------------------------------------------------------- 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.
