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

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.

CodePudding user response：

Please don't build queries like this:

"INSERT INTO foo VALUES ({}, {}, {})".format(1, "bar", None)

Assuming the values 1, "bar", and None come from an untrusted source, this leaves you open to a very serious type of software vulnerability called SQL injection. Most sources of data should be considered untrusted, and writing queries safely should always be the default.

There is another problem with the above query: it is wrong. This generates

INSERT INTO foo VALUES (1, bar, None)

which is missing quotes around bar (strings should be quoted as 'bar') and it uses None where it should use NULL.

The correct way to do this is to pass the parameters 1, "bar", and None to your database separately:

parameters = (1, "bar", None)
cursor.execute("INSERT INTO foo VALUES (%s, %s, %s)", parameters)

# or you can name parameters

parameter_dict = {
    "first": 1,
    "second": "bar",
    "third": None,
}
cursor.execute(
    "INSERT INTO foo VALUES (%(first)s, %(second)s, %(third)s)", 
    parameter_dict,
)

This protects against SQL injection and also takes care of quoting strings, converting None to NULL, etc.

Finally, you can just skip values that should be null (or use the default value for the column) and let MySQL fill them in for you, e.g.

INSERT INTO foo (first, second) VALUES (1, 'bar');