FAQ
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<https://github.com/wxgeorge/django-db-backend-divergence>)
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<https://docs.djangoproject.com/en/1.5/ref/databases>note an
old bug concerning date aggregation with sqlite in windows<https://code.djangoproject.com/ticket/10031>,
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).

All comments appreciated!

Wes

--
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/205179bf-a853-424a-9a8e-8b3297ab856c%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Search Discussions

  • Avraham Serour at Oct 26, 2013 at 7:02 pm
    Using sqlite for dev is common practive, don't worry, it is not naive to do
    it.
    I remember seeing somehing about contenttypes and sqlite, I'm too hungry to
    remember now, maybe someone else in the list can give a better input than
    me.

    on the query result just do a if DEBUG or if DEVELOPMENT or if
    backend==sqlite or if returned type == string then create a datetime object
    from the string.

    On Sat, Oct 26, 2013 at 9:47 PM, Wesley George 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 <https://github.com/wxgeorge/django-db-backend-divergence>) 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<https://docs.djangoproject.com/en/1.5/ref/databases>note an
    old bug concerning date aggregation with sqlite in windows<https://code.djangoproject.com/ticket/10031>,
    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).

    All comments appreciated!

    Wes

    --
    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/205179bf-a853-424a-9a8e-8b3297ab856c%40googlegroups.com
    .
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    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/CAFWa6tL2oALzmL1PsWW90OU3BKFeQZ2Qmyr1fWyNbzbFVYNGmw%40mail.gmail.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Ramiro Morales at Oct 26, 2013 at 7:50 pm

    On Sat, Oct 26, 2013 at 3:47 PM, Wesley George 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.

    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.

    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/CAO7PdF_dzLuk_kMZxQeKyy2U-KmFKPMW6MGsmSi0nDHeYfeYuw%40mail.gmail.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Wesley George at Oct 26, 2013 at 9:13 pm

    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 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!

    --
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdjango-users @
categoriesdjango, python
postedOct 26, '13 at 6:56p
activeOct 26, '13 at 9:13p
posts4
users3
websitedjangoproject.com

People

Translate

site design / logo © 2022 Grokbase