Sunday, March 15, 2009

Importing NULL values into Sqlite

Finally starting up the blog again. I'm hoping to document troublesome things I've run so that it will maybe help somebody else.

Today's challenge was Sqlite's .import statement. Apparently, there is no way to import a NULL value into a database. Even for an integer or float field, a blank value will simply be stored as an empty string. You have to use an update statement after the fact to fix the value:
update tablename set field=NULL where field='';

The real problem was the way this problem presented itself in Django. I was trying to run a command that updated entries in the database, and I kept getting the following error:
ValueError: invalid literal for int() with base 10: ''
in a function called get_db_prep_value. Not very intuitive, but I finally figured out the issue by debugging it with Winpdb. Changing all my non-char fields that allow NULL values to proper NULLs fixed the problem.