2

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?

Improve this question

2 Answers 2

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The column is nullable in MySQL.
I appreciate the thorough inquiry. I'm on Django 1.2.3, Python 2.6.6. When I do SHOW CREATE TABLE it shows this for interest_rate: interest_rate float DEFAULT NULL`. As you can see, it's a float as opposed to a double (which probably doesn't make as much sense but that's what I'm working with). Perhaps I'll try a double.
@Jason Swett, I think it might be a mysql server setting, see my answer I have updated it with a way to check your sql_mode and a reference to sql_mode page on mysql so that you can change the value and see if that helps.
I'm on MySQL 5.1.49 and my sql_mode is also blank. I feel like it must be a Python/Django issue because I have this same table hooked up to a PHP app and it's working fine there.
Okay, now I can say pretty confidently that it's a Python/Django issue as opposed to a MySQL one. When I tell my app to spit out the raw SQL for my INSERT and then run that directly in MySQL, the statement runs just fine. When I let the statement run inside Django, I get the error.
1

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.

Improve this answer

1 Comment

I'm glad you found your answer. Can you update your original question with what you had wrong, and what your fix was so that people will see how you fixed it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.