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
> <wesley...@gmail.com<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 django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
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.
