Re: difference in datetime aggregation when using sqlite3 instead of mysql

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> 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+unsubscribe@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.