CODEWITHSUNDEEP
pythonmysqlpymysql
Sai Vidya
Sai Vidya

Reputation: 11

How to use MySQL's NULL and DEFAULT keywords for INSERT query in python?

I'm trying to run an INSERT query via a python program for thousands of records which have nullable fields as well. My requirement is such that the result set is transformed into key-value pairs in a dictionary and then parse the values into a list for executing INSERT query. However, when I try to add a NULL value to an int type field and execute INSERT via the program, I hit an error as shown below:

Warning: (1366, "Incorrect integer value: 'NULL' for column 'category_id' at row 1") result = self._query(query)

In the MySQL DB if I execute the query with 'NULL' or 'DEFAULT' keyword for the value of 'category_id', the value gets updated as NULL as expected.

Can someone please help zero in on what I'm missing here? Following is my code snippet:

    for s in result:
        temp_dict = {}
        for key, value in enumerate(s):
            if value is None:
                temp_dict[key] = pymysql.NULL
            else:
                temp_dict[key] = str(value)

Upvotes: 1

Views: 835

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

i think null is being converted to string as a result you got error could you try by passing the value None, not "NULL":

value = None
cursor.execute("INSERT INTO table (`column1`) VALUES (%s)", (value,))

Upvotes: 2

kjagoo
kjagoo

Reputation: 1

For a field expecting an Integer, MySQl replace a NULL field with 0 which should be the default value.

As you query you could use the IS NULL to single out the values with null values which you could then manipulate the value to an integer e.g 0

or you could tweek the query to only return values without a null at category_id by adding

WHERE category_id IS NOT NULL

SELECT * 
FROM table 
WHERE category_id IS NOT NULL

Upvotes: 0

Related Questions