[sqlalchemy] Re: Matching a DateTime-field
On Dec 11, 10:55 am, "King Simon-NFHD78" <[EMAIL PROTECTED]> wrote: > It may not matter to you, but I wouldn't have thought this would be a > very efficient query, because the database is going to have to call the > DATE_FORMAT function twice for every row in your table. I would have > thought a more efficient version would be one that asks for all rows > between the first of one month and the first of another month > (especially if the date column is indexed). > > Something like: > > from datetime import date > session.query(List).filter( > and_(List.expire >= date(2007, 12, 1), > List.expire < date(2008, 1, 1)) > ).all() > > Adding one month to a date is pretty easy, but if you wanted to do any > more complicated date calculations, the dateutil library is very good: > > http://labix.org/python-dateutil Ah yes, was so obsessed with the solution. Letting the mysql work is much more efficient. It will matter under heavy load. I will check out dateutil, thanks. br Adam --~--~-~--~~~---~--~~ 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: Matching a DateTime-field
Hello!
Thanks for the pointers.
Here is the solution for MySQL :
session.query(List).filter(and_(func.DATE_FORMAT(List.expire,'%Y')
==2007 ,func.DATE_FORMAT(List.expire,"%m") == 12)).all()
On Dec 10, 6:08 pm, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Yeah, it was a "for instance" answer, you'll need to use the correct MySql
> syntax of course.
>
> On 12/10/07, Adam B <[EMAIL PROTECTED]> wrote:
>
>
>
> > On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> > > Any query using sql expressions is going to want to use correctly typed
> > data
> > > -- you're trying to query a date column with a string value. The LIKE
> > > operator is for string data.
>
> > > I'm not up on my mssql date expressions, but the answer is going to
> > resemble
> > > something like this:
>
> > > .filter(and_(func.datepart('year', List.expire) == 2007,
> > > func.datepart('month', List.expire) == the_month_number))
>
> > Ok, isnt this mssql specifik? I only find datepart in various
> > VB / .net documentation/solutions.
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Any query using sql expressions is going to want to use correctly typed data
> -- you're trying to query a date column with a string value. The LIKE
> operator is for string data.
>
> I'm not up on my mssql date expressions, but the answer is going to resemble
> something like this:
>
> .filter(and_(func.datepart('year', List.expire) == 2007,
> func.datepart('month', List.expire) == the_month_number))
>
Ok, isnt this mssql specifik? I only find datepart in various
VB / .net documentation/solutions.
--~--~-~--~~~---~--~~
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: Matching a DateTime-field
On Dec 10, 1:16 am, "Rick Morrison" <[EMAIL PROTECTED]> wrote:
> Any query using sql expressions is going to want to use correctly typed data
> -- you're trying to query a date column with a string value. The LIKE
> operator is for string data.
>
> I'm not up on my mssql date expressions, but the answer is going to resemble
> something like this:
>
> .filter(and_(func.datepart('year', List.expire) == 2007,
> func.datepart('month', List.expire) == the_month_number))
>
>
Ah yes, i had no idea how to match the dates the way you presented.
Many Thanks !
br
Adam
--~--~-~--~~~---~--~~
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] Matching a DateTime-field
Hello!
I'm trying to do a query that gets all lists within a specific month,
but
SQLAlchemy whines. :/
The error:
/usr/local/lib/python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/
sqlalchemy/databases/mysql.py:1475: Warning: Incorrect datetime value:
'"2007-"+str(month)+"%"' for column 'expire' at row 1
cursor.execute(statement, parameters)
The code:
L =
session.query(List).join('friends').filter(Friend.username==identity.current.user_name).filter(List.expire.like('"2007-"+str(month)
+"%"')).all()
columnt "expire" is DateTime in the model.
Any ideas how to do this?
br
Adam
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
