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.
