Django/Python doesn't like None value for FloatField

I have an Account class in Django with an interest_rate attribute (corresponding to account.interest_rate in my database). Here's the declaration for interest_rate:

interest_rate = models.FloatField(null=True, blank=True)

If I do something like this, it works:

account = Account()
account.interest_rate = 5
account.save()

But if I do this, it doesn't work:

account = Account()
account.interest_rate = None
account.save()

I get this error:

Traceback 开发者_运维知识库(most recent call last):
  File "./import.py", line 18, in <module>
    cProfile.run('g.process()', 'prof')
  File "/usr/lib/python2.6/cProfile.py", line 29, in run
    prof = prof.run(statement)
  File "/usr/lib/python2.6/cProfile.py", line 135, in run
    return self.runctx(cmd, dict, dict)
  File "/usr/lib/python2.6/cProfile.py", line 140, in runctx
    exec cmd in globals, locals
  File "<string>", line 1, in <module>
  File "/home/jason/projects/mcifdjango/mcif/models/generic_import.py", line 34, in process
    Account.save_in_bulk(self.rows)
  File "/home/jason/projects/mcifdjango/mcif/models/account.py", line 45, in save_in_bulk
    cursor.execute(Account.bulk_insert_statement(rows))
  File "/usr/lib/pymodules/python2.6/django/db/backends/util.py", line 15, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/pymodules/python2.6/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 168, in execute
    if not self._defer_warnings: self._warning_check()
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 82, in _warning_check
    warn(w[-1], self.Warning, 3)
_mysql_exceptions.Warning: Data truncated for column 'interest_rate' at row 1

Why won't it let me save a null on a nullable field? Am I doing it wrong?

Is the field nullable in mysql?

I have seen this error in the past when I created a model with a field that was originally not allowed to be nullable, when the table was created and then later changed to allow it to be nullable. Since mysql has the null = false set from the original table creation, it will fail the insert even if django lets it through.

To fix this, I would check the table definition in mysql, if the field is null=false, you can manually change it, or you can delete the table (if you don't care about the data), and then rerun a ./manage.py syncdb to recreate the table.

If you are using south to manage your schema migrations you can create a new schema migration and then run that.

If the mysql table instance doesn't show null=false, then it is something else.

Update: We found out that mysql had column as nullable, so I'm updating answer.

I'm using Django version 1.2.5, which version are you using?

I created the model in my system.

from django.db import models

#================================================================================
class Account(models.Model):
    interest_rate = models.FloatField(null=True, blank=True)

And then ran it via shell, and it worked for me.

Python 2.6.6 (r266:84292, Sep  8 2010, 16:19:33) 
[GCC 4.2.1 (Apple Inc. build 5664)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from aggra.models import Account
>>> account = Account()
>>> account.interest_rate = None
>>> account.save()
>>> exit()

Here is my create statement from django. aggra is my app name.

$ python manage.py sql aggra
BEGIN;
CREATE TABLE `aggra_account` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `interest_rate` double precision
)
;
COMMIT;

Here is what my table looks like from mysql.

CREATE TABLE `aggra_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `interest_rate` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Does that look like yours?

Update 2 We found out that it looked the same, and was using roughly the same version of django and python.

What is your version of mysql?

What do you have for SQL mode? Mine is empty.

mysql> show variables where Variable_name = 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

If you have it set to strict, then you will need to change it to be more lenient.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

See also.

http://forums.mysql.com/read.php?11,132672,132693#msg-132693

I was building my SQL query wrong, putting blank strings where I should have been putting NULLs. I fixed that and now it works fine.

I was building a query that looked like this:

INSERT INTO account (interest_rate) values ('')

When it should have been this:

INSERT INTO account (interest_rate) values (NULL)

Both versions work fine in MySQL but Python/Django don't like the former version.