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.
