On Saturday, October 26, 2013 3:49:42 PM UTC-4, Ramiro Morales wrote:
> On Sat, Oct 26, 2013 at 3:47 PM, Wesley George
> <[email protected]<javascript:>>
> wrote:
> >
> > I'm working on a django application that uses the mysql backend in
> production but the sqlite3 db backend during dev to speed up testing.
> >
> > I was surprised to find these backends differ when populating extra
> model fields with aggregated datetimes. Specifically, if you use
> Queryset.extra to create a field that is a maximum of some datetime
> objects, the sqlite django backend makes this field a string, while the
> mysql backend makes this field a datetime object. I've put a minimal django
> app at github (here) that exemplifies this difference on my setup, Django
> 1.5.1, python 2.7.4, Ubuntu 13.04.
> >
> > I know there are situations where these backends will behave
> differently, I took the above aggregation operation to be basic enough that
> this shouldn't be such a situation. The django docs on database backends
> note an old bug concerning date aggregation with sqlite in windows, but I
> didn't think this should apply.
> >
> > I'd love to hear from some other Django users. For example,
> >
> > Is this behaviour expected?
> >
> > (was I naive in subbing the sqlite backend in during development?)
> >
> > Is this a known bug?
> > Is there a better way to use django's ORM to accomplish what I'm doing?
> >
> > (e.g. create a full-fledged DateTime field on the model, that must be
> kept up to date when the other table is updated).
>
> I'd recommend to use the same components in your development
> enviroment as in the production one, and not only for issues like
> this.
>
I think I will rethink my setup. Maybe keeping sqlite for test during
development, but also be running the tests against a mysql backend in the
production branch after merges.
>
> At the low level, sqlite3, has no datetime (and similar) data types,
> all of them are stored as strings.
>
> The Django sqlite DB backend makes use of some pysqlite-provided hooks
> to convert information coming from the DB for columns corresponding to
> model DateTimeField's, etc. fields This latter knowledge is the one
> that allows it to keep track and perform the casting correctly.
>
> But I suspect all bets are off when one is using .extra() because in
> that case the Python code would need to interpret the arbitrary. user
> defined SQL query and deduct the type result(s).
> But, if I change::
>
> qset = qset.extra(select={'expiry_dt':'SELECT MAX(sub.end_dt) '\
> ' FROM
> example_app_subscription AS sub '\
> ' WHERE sub.user_id=id'})
>
> to use the ORM's native annotation capabilities::
>
> qset = qset.annotate(expiry_dt=models.Max('subscription__end_dt'))
> Then I get::
>
> database engine: sqlite3
> aggregated datatime type: <type 'datetime.datetime'>
>
> Hopefully this is something you can actually use in your real project.
Wow ... I didn't know about annotate and I have no reason to not use the
ORM this way. Approaching the 8 months of using Django, I've spent a lot
of time with the docs, but never found that; I suppose I could have known
better, but this pointer is much appreciated. Many thanks Ramiro!
>
> See
> https://docs.djangoproject.com/en/1.5/topics/db/aggregation/#following-relationships-backwards
>
>
> Regards,
>
> --
> Ramiro Morales
> @ramiromorales
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/a2769f6d-94ac-4e13-a9b2-9076e768e0c0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
