Thanks,
Ok, i use MySQLdb as database driver...
I solved the problem with func.unix_timestamp
def lg(self):
""" extrait les créneaux libres groupés """
xref = time.time()
haut = model.aliased(model.Plage)
milieu = model.aliased(model.Plage)
bas = model.aliased(model.Plage)
s = model.session.query(milieu)
s = self.filtrage_requete(s, milieu)
s = s.filter(milieu.EtatCourant=="L")
*s =
s.filter(model.func.unix_timestamp(milieu.Fin)-(self.duree_cible*60)>=model.func.unix_timestamp(milieu.Debut))
*
if param.dossier_concerne.AgendaGroupement and
param.dossier_concerne.AgendaSensGroupement==1: # on groupe par le haut
uniquement
s = s.filter(haut.Fin==milieu.Debut)
s = s.filter(haut.EtatCourant=="O")
if param.dossier_concerne.AgendaGroupement and
param.dossier_concerne.AgendaSensGroupement==0: # on groupe par le bas
uniquement
s = s.filter(bas.Debut==milieu.Fin)
s = s.filter(bas.EtatCourant=="O")
s = s.order_by(milieu.Debut)
s = s.limit(100)
s = s.all()
print "lg", time.time()-xref
return s
As u can see, your wonderful library is used in many projects around the
world !)
You're the best!
2010/11/18 Michael Bayer <mike...@zzzcomputing.com>
> This has everything to do with the database driver in use and the database
> itself. Acceptance of a Python timedelta object by the DBAPI and proper
> interpretation of it against the result of a TIMEDIFF call (which is MySQL
> specific) is not a given on most DBAPIs. For cases like these your best bet
> is to first look at all SQL being emitted, and try to stick to foolproof
> types like ints as far as what you pass to the database. As for SQLAlchemy,
> we're just passing SQL strings and Python constructs to cursor.execute().
>
> On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote:
>
> Hi,
>
> I found a usecase that is illogical
>
> Here is the first code and his result in term. All is *False*
>
> milieu = model.aliased(model.Plage)
> s2 = model.session.query(
> milieu,
> model.func.timediff(milieu.Fin, milieu.Debut),
> *model.func.timediff(milieu.Fin,
> milieu.Debut)>datetime.timedelta(minutes=self.duree_cible)*
> )
> for e in s2:
> print e
>
> (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8,
> 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600),
> False)
> (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14,
> 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8,
> 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14,
> 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8,
> 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14,
> 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8,
> 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14,
> 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8,
> 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14,
> 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8,
> 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14,
> 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8,
> 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14,
> 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8,
> 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14,
> 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8,
> 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14,
> 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8,
> 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14,
> 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8,
> 0),datetime.datetime(2010, 12, 21, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14,
> 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8,
> 0),datetime.datetime(2010, 12, 27, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14,
> 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8,
> 0),datetime.datetime(2010, 12, 28, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14,
> 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011,
> 1, 3, 10, 0)), datetime.timedelta(0, 7200), False)
> (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14,
> 0),datetime.datetime(2011, 1, 3, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011,
> 1, 4, 10, 0)), datetime.timedelta(0, 7200), False)
> (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14,
> 0),datetime.datetime(2011, 1, 4, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8,
> 0),datetime.datetime(2011, 1, 10, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14,
> 0),datetime.datetime(2011, 1, 10, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8,
> 0),datetime.datetime(2011, 1, 11, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14,
> 0),datetime.datetime(2011, 1, 11, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8,
> 0),datetime.datetime(2011, 1, 17, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(624L,'L',datetime.datetime(2011, 1, 17, 14,
> 0),datetime.datetime(2011, 1, 17, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(625L,'L',datetime.datetime(2011, 1, 18, 8,
> 0),datetime.datetime(2011, 1, 18, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(626L,'L',datetime.datetime(2011, 1, 18, 14,
> 0),datetime.datetime(2011, 1, 18, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(627L,'L',datetime.datetime(2011, 1, 24, 8,
> 0),datetime.datetime(2011, 1, 24, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(628L,'L',datetime.datetime(2011, 1, 24, 14,
> 0),datetime.datetime(2011, 1, 24, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(629L,'L',datetime.datetime(2011, 1, 25, 8,
> 0),datetime.datetime(2011, 1, 25, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(630L,'L',datetime.datetime(2011, 1, 25, 14,
> 0),datetime.datetime(2011, 1, 25, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(631L,'L',datetime.datetime(2011, 1, 31, 8,
> 0),datetime.datetime(2011, 1, 31, 10, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(632L,'L',datetime.datetime(2011, 1, 31, 14,
> 0),datetime.datetime(2011, 1, 31, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(633L,'L',datetime.datetime(2011, 2, 1, 8, 0),datetime.datetime(2011,
> 2, 1, 10, 0)), datetime.timedelta(0, 7200), False)
> (Plage(634L,'L',datetime.datetime(2011, 2, 1, 14,
> 0),datetime.datetime(2011, 2, 1, 16, 0)), datetime.timedelta(0, 7200),
> False)
> (Plage(635L,'L',datetime.datetime(2010, 11, 16, 9,
> 20),datetime.datetime(2010, 11, 16, 10, 0)), datetime.timedelta(0, 2400),
> False)
> (Plage(636L,'O',datetime.datetime(2010, 11, 16, 8,
> 10),datetime.datetime(2010, 11, 16, 9, 20)), datetime.timedelta(0, 4200),
> False)
>
>
>
> Here is the second code and results in term. I just change inequality and
> it works.
> milieu = model.aliased(model.Plage)
> s2 = model.session.query(
> milieu,
> model.func.timediff(milieu.Fin, milieu.Debut),
> *model.func.timediff(milieu.Fin,
> milieu.Debut)-datetime.timedelta(minutes=self.duree_cible)>0*
> )
> for e in s2:
> print e
>
>
> (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8,
> 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600),
> False)
> (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14,
> 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8,
> 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14,
> 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8,
> 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14,
> 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8,
> 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14,
> 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8,
> 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14,
> 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8,
> 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14,
> 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8,
> 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14,
> 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8,
> 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14,
> 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8,
> 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14,
> 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8,
> 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14,
> 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8,
> 0),datetime.datetime(2010, 12, 21, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14,
> 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8,
> 0),datetime.datetime(2010, 12, 27, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14,
> 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8,
> 0),datetime.datetime(2010, 12, 28, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14,
> 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011,
> 1, 3, 10, 0)), datetime.timedelta(0, 7200), True)
> (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14,
> 0),datetime.datetime(2011, 1, 3, 16, 0)), datetime.timedelta(0, 7200), True)
> (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011,
> 1, 4, 10, 0)), datetime.timedelta(0, 7200), True)
> (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14,
> 0),datetime.datetime(2011, 1, 4, 16, 0)), datetime.timedelta(0, 7200), True)
> (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8,
> 0),datetime.datetime(2011, 1, 10, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14,
> 0),datetime.datetime(2011, 1, 10, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8,
> 0),datetime.datetime(2011, 1, 11, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14,
> 0),datetime.datetime(2011, 1, 11, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8,
> 0),datetime.datetime(2011, 1, 17, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(624L,'L',datetime.datetime(2011, 1, 17, 14,
> 0),datetime.datetime(2011, 1, 17, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(625L,'L',datetime.datetime(2011, 1, 18, 8,
> 0),datetime.datetime(2011, 1, 18, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(626L,'L',datetime.datetime(2011, 1, 18, 14,
> 0),datetime.datetime(2011, 1, 18, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(627L,'L',datetime.datetime(2011, 1, 24, 8,
> 0),datetime.datetime(2011, 1, 24, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(628L,'L',datetime.datetime(2011, 1, 24, 14,
> 0),datetime.datetime(2011, 1, 24, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(629L,'L',datetime.datetime(2011, 1, 25, 8,
> 0),datetime.datetime(2011, 1, 25, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(630L,'L',datetime.datetime(2011, 1, 25, 14,
> 0),datetime.datetime(2011, 1, 25, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(631L,'L',datetime.datetime(2011, 1, 31, 8,
> 0),datetime.datetime(2011, 1, 31, 10, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(632L,'L',datetime.datetime(2011, 1, 31, 14,
> 0),datetime.datetime(2011, 1, 31, 16, 0)), datetime.timedelta(0, 7200),
> True)
> (Plage(633L,'L',datetime.datetime(2011, 2, 1, 8, 0),datetime.datetime(2011,
> 2, 1, 10, 0)), datetime.timedelta(0, 7200), True)
> (Plage(634L,'L',datetime.datetime(2011, 2, 1, 14,
> 0),datetime.datetime(2011, 2, 1, 16, 0)), datetime.timedelta(0, 7200), True)
> (Plage(635L,'L',datetime.datetime(2010, 11, 16, 9,
> 20),datetime.datetime(2010, 11, 16, 10, 0)), datetime.timedelta(0, 2400),
> True)
> (Plage(636L,'O',datetime.datetime(2010, 11, 16, 8,
> 10),datetime.datetime(2010, 11, 16, 9, 20)), datetime.timedelta(0, 4200),
> True)
>
> *Is it normal????*
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
