1

Let's say I have a csv with columns 1,2,3,4 and I am trying to import all of those columns into a mysql table. Some lines will have blank values in a random column (possibly multiple columns). When trying to import the csv as is, I get the error Incorrect integer value: '' for column. If I replace all of the blank entries with NULL, I get the same error, Incorrect integer value: 'NULL' for column. I realize that this is because it's setting the value to a string rather than an int, while the column type is int.

I set the columns to accept NULL values. I turned off mysql's strict mode and that somewhat solves the issue, except the values show up as 0 instead of NULL.

The only solution I can think of is having a separate query to replace all values that are 0 to NULL, but I was wondering if there is a way to do it all within one query.

Thank you

steven chen
  • 57
  • 5
  • 1
    Don't replace all of the empty string (`''`) values with the string `'NULL'`, replace them with the python type, `None`. The dbapi library will handle converting that to the string `'NULL'` where necessary. Whenever in python land, if you want `NULL` in your database, use `None`. – SuperShoot Sep 07 '19 at 04:04
  • So this is pretty much a duplicate of this: https://stackoverflow.com/q/5507948/6560549 – SuperShoot Sep 07 '19 at 04:08

0 Answers0