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

Question

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)

share your table structure , one question category_id allow null? — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Sep 24, 2018 at 12:18
@ZaynulAbadinTuhin The query formed by python looks something like 'INSERT INTO table_name VALUES ('a', 'b', .... 'NULL'). In this case the NULL field in the end happens to be of int type. — Sai Vidya
– Sai Vidya, Commented Sep 24, 2018 at 12:41

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-24 12:55:16Z

2

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,))

answered Sep 24, 2018 at 12:55

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

s__ · Accepted Answer · 2018-09-24 12:43:12Z

0

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

edited Sep 24, 2018 at 12:43

s__

9,5433 gold badges30 silver badges48 bronze badges

answered Sep 24, 2018 at 12:28

kjagoo

1

1 Comment

Sai Vidya Over a year ago

I'm trying to do a bulk insert here. I would want the code to be able to handle this for any NULL value irrespective of field type.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related