32

I've seen all the similar threads, read the docs, and tried many combinations to store an empty value as IntegerField in db and failed every single time.

I am using MySQL.

My models.py defines an age=models.IntegerField() field. I populate db from csv file, and some cells have no value. Django docs says:

Field.null

If True, Django will store empty values as NULL in the database. Default is False.
Note that empty string values will always get stored as empty strings, not as NULL.

Since I am working with IntegerField I want an empty string (an empty cell from the csv) to be stored as NULL in db. Therefore, I (think) have to add null=True to the age field. Actually, I've tried more than this:

age=models.IntegerField()
age=models.IntegerField(null=True)
age=models.IntegerField(null=True, blank=True)
age=models.IntegerField(null=True, blank=True, default=None)

and every time I am inserting an empty string to a db I got 

ValueError: invalid literal for int() with base 10: ''

Any guess what else can I do?

nutship
  • 4,624
  • 13
  • 47
  • 64

3 Answers3

59

A string is not an integer; and a blank string is not None or NULL. What you need to do is catch those instances where the field is blank and then cast it to None.

foo = "something" # "something" is coming from your CSV file

try:
   val = int(foo)
except ValueError:
   # foo is something that cannot be converted to
   # a number. It could be an empty string, or a
   # string like 'hello'
   # provide a default value
   val = None

# Now use val to insert into the database
f = MyModel()
f.age = val
f.save()

blank is strictly for front end validation; it doesn't have any impact on the database side:

Note that this is different than null. null is purely database-related, whereas blank is validation-related. If a field has blank=True, form validation will allow entry of an empty value. If a field has blank=False, the field will be required.

null on the other hand, has to do with the database:

If True, Django will store empty values as NULL in the database. Default is False.

An IntegerField requires value that can be converted into an integer, so when you pass in a blank string, it cannot cast it and raises an exception. Instead, if you pass in None, and you have age = models.IntegerField(null=True), it knows to store it correctly.

To summarize:

  • age = models.IntegerField()

    Field is required and needs a valid integer value. It will not accept None and will have no null values in the database. Valid values are -2147483648 to 2147483647

  • age = models.IntegerField(null=True)

    Field is required (form validation). If the field has None as a value, it will be translated to NULL in the database.

  • age = models.IntegerField(blank=True, null=True)

    Field is not required (form validation). If the field is passed in None, it will be translated to NULL

  • age = models.IntegerField(blank=True)

    Field is not required (form validation), but a valid integer value needs to be passed in because the database does not accept null. Typically here you would give it a default value with default=0 or have some validation done before submitting the value to the orm.

Burhan Khalid
  • 169,990
  • 18
  • 245
  • 284
  • @Burhan Khalid - Nice Explanation, I have a field `mobile_no = models.BigIntegerField(max_length =10, blank=True, null=True)`. When there is no mobile number entered I tried passing in `None` to the `mobile_no` field. I get an error `invalid literal for int() with base 10: 'None'`. Where could I be going wrong ? – kurrodu Jan 30 '14 at 07:10
  • You are passing the _string_ `'None'` (note the quotes) and not the value `None`. – Burhan Khalid Jan 30 '14 at 09:44
4

Try to pass None to this field not empty string ''

ndpu
  • 22,225
  • 6
  • 54
  • 69
0

You can override 'clean' method in your form class, then cleaning the integer data for avoiding empty string to be submitted, see below example:

def clean(self):
    self.cleaned_data = super(YourForm, self).clean()
    if self.cleaned_data['age'] == '': self.cleaned_data['age'] = None
    return self.cleaned_data
Simon Chen
  • 1