'How do i insert a NULL value into an integer column using python and mysql.connector

The column doesn't have constraints such as NOT NULL and, since I'm using python, I tried to insert None instead of NULL. It still doesn't work. Can anyone help?

How the table was created:

create_species = "CREATE TABLE `species` (" \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(70) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` int(20)," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \
             "  `{}` varchar(40) DEFAULT '' NOT NULL," \

How I insert in the table:

"INSERT INTO species (name, classification, designation, average_height, skin_colors, hair_colors, eye_colors, average_lifespan, language, homeworld)"
              "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');".format(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9])

Where row[7] is in some cases an integer (that's when it works) and in other cases it has the value "NA", so I transform it into None as follows:

if row[7] == "NA":
  row[7] = None

The value has to be NULL otherwise avg() and other similar operations will give me a wrong result.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'How do i insert a NULL value into an integer column using python and mysql.connector

Sources

Related Questions