Datefield auto_now_add not working with sqlite3?

[Serdar T.]

Hi everyone,
I'm having a problem with the "auto_now_add" option for DateField.

I was under the impression that when you set this option, a datestamp
is set automatically for you. But when I try inserting records into my
database without supplying a date for this field, I get a NULL value
error:

"calendar.date_added_to_db may not be NULL"

Below is the relevant portion of my model definition:

class Event(models.Model):
date = models.DateField()
title = models.TextField()
url = models.URLField()
date_added_to_db = models.DateField(auto_now_add=True)

And the results of python manage.py sqlall:

BEGIN;
CREATE TABLE "calendar" (
"id" integer NOT NULL PRIMARY KEY,
"date" date NOT NULL,
"title" text NOT NULL,
"url" varchar(200) NOT NULL,
"date_added_to_db" date NOT NULL
)
;
COMMIT;

I'm confused as to why Django's ORM would translate the
"date_added_to_db" field as the above SQL. It would seem more
appropriate for the "date_added_to_db" field to be translated as the
following SQL:

"DATETIME NOT NULL DEFAULT CURRENT_DATE"

With the above field definition, the datestamp is set automatically
for me when a record is created (which is the behavior I'm after).

Am I misconfiguring something, or is this an issue perhaps with the
implementation of the auto_now_add option for sqlite3? Or perhaps it's
convention to pass in the date/timestamp (though in that case, I don't
see the point of having the "auto_now_add" option....)?

If it helps, I'm using sqlite3, Django 1.1 and Python 2.6.2.

Any advice is appreciated.

[Karen Tracey]

On Mon, Oct 5, 2009 at 3:52 PM, Serdar T. <zstum...@gmail.com> wrote:

Hi everyone,
I'm having a problem with the "auto_now_add" option for DateField.

I was under the impression that when you set this option, a datestamp
is set automatically for you. But when I try inserting records into my
database without supplying a date for this field, I get a NULL value
error:

"calendar.date_added_to_db may not be NULL"

You left out the detail of exactly how you are inserting records.
 

Below is the relevant portion of my model definition:

class Event(models.Model):
   date = models.DateField()
   title = models.TextField()
   url = models.URLField()
   date_added_to_db = models.DateField(auto_now_add=True)

Using this model and commenting out the url and date fields just for simplicity, I cannot recreate any problem with auto_now_add:

>>> from ttt.models import Event
>>> Event.objects.create(title="First")
<Event: Event object>
>>> Event.objects.all()[0].date_added_to_db
datetime.date(2009, 10, 5)
>>> Event.objects.all()[0].title
u'First'
>>>

And the results of python manage.py sqlall:

BEGIN;
CREATE TABLE "calendar" (
   "id" integer NOT NULL PRIMARY KEY,
   "date" date NOT NULL,
   "title" text NOT NULL,
   "url" varchar(200) NOT NULL,
   "date_added_to_db" date NOT NULL
)
;
COMMIT;

I'm confused as to why Django's ORM would translate the
"date_added_to_db" field as the above SQL. It would seem more
appropriate for the "date_added_to_db" field to be translated as the
following SQL:

    "DATETIME NOT NULL DEFAULT CURRENT_DATE"

With the above field definition, the datestamp is set automatically
for me when a record is created (which is the behavior I'm after).

Am I misconfiguring something, or is this an issue perhaps with the
implementation of the auto_now_add option for sqlite3? Or perhaps it's
convention to pass in the date/timestamp (though in that case, I don't
see the point of having the "auto_now_add" option....)?

Django does not implement any defaults, including auto_now_add, by specifying the default at the SQL level.  Rather Django ensures that the default value is specified when it saves a row to the database. 

So if you are going outside of Django to insert records, the default value will not be applied.  In this case you may want to alter the table to specify the default in SQL so that it is applied regardless of whether records are inserted using Django or not. 

If you are creating objects with the Django ORM and still not seeing the value auto-set, then there may be a bug, but we need details on exactly what you are doing in this case.  I cannot recreate a problem so far based on the information you have provided.

Karen

[Serdar]

> Django does not implement any defaults, including auto_now_add, by
> specifying the default at the SQL level.  Rather Django ensures that the
> default value is specified when it saves a row to the database.

Aha! That explains it.

>
> So if you are going outside of Django to insert records, the default value
> will not be applied.

I am indeed trying to perform the insert from outside of Django.

 In this case you may want to alter the table to
> specify the default in SQL so that it is applied regardless of whether
> records are inserted using Django or not.
>

Sqlite doesn't allow you to modify columns -- only add them, correct?
If that's the case, I think I'll have to add the column using SQL, and
then map to it in my model defintion, right?

Unless you see something wrong with that method, I'll give it a try.
Meantime, thanks much for the quick reply!

Regards,
Serdar

[Karen Tracey]

On Mon, Oct 5, 2009 at 4:43 PM, Serdar <zstum...@gmail.com> wrote:

Sqlite doesn't allow you to modify columns -- only add them, correct?
If that's the case, I think I'll have to add the column using SQL, and
then map to it in my model defintion, right?

I don't know, I don't do much with sqlite except casual testing.  But yes, if it doesn't allow you to modify the column after creation then you'll have to add it after creating the table and then adjust your Django model too match.  Or don't use syncdb but rather take and alter the SQL output you posted earlier and manually create the table in sqlite to match what you want. Or there are probably other ways....

Karen