How to store empty value as an Integerfield

django, mysql, null, python

Solution

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.

Problem

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?

Original source