MySQL for Python: Incorrect Integer value

Question

I have the same question as asked here:

Default value for empty integer fields when importing CSV data in MySQL

I keep getting the warning "Incorrect Integer value" when importing a csv file into Mysql. I've read all the relevant questions/answers here, and the link above is my direct question. But I'm trying to implement the answer given there in Python 2.7, and I can't seem to get it working.

My code below is my attempt to implement the answer from the above post. I think the issue is the syntax for using the "Set" clause in my Load DATA Local Infile statement...I would really appreciate any help since MySQL automatically converts empty INT's to "0" and that would mess up my analysis since I want blanks to be null. My code is:

import MySQLdb
db = MySQLdb.connect(blah, blah, blah)
cursor = db.cursor()

sql = """CREATE TABLE PLAYER(
       PLAYER_ID INT,
       DATE DATETIME,
       BATTING_AVERAGE INT
       )"""

cursor.execute(sql)
statement = """LOAD DATA LOCAL INFILE 'path/file.csv'
INTO TABLE PLAYER
COLUMNS TERMINATED BY ','
SET BATTING_AVERAGE = IF(@BATTING_AVERAGE='',NULL,@BATTING_AVERAGE)
IGNORE 1 LINES;"""

The error that this code gives is:

ProgramingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synatx to use near 'IGNORE 1 Lines at line 5")

cdhowie · Accepted Answer · 2013-01-24 17:05:05Z

1

The SET and IGNORE clauses are backwards. Try swapping them:

statement = """LOAD DATA LOCAL INFILE 'path/file.csv'
INTO TABLE PLAYER
COLUMNS TERMINATED BY ','
IGNORE 1 LINES
SET BATTING_AVERAGE = IF(@BATTING_AVERAGE='',NULL,@BATTING_AVERAGE);"""

answered Jan 24, 2013 at 17:05

cdhowie

172k25 gold badges303 silver badges324 bronze badges

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

6 Comments

Jon Clements Over a year ago

COLUMNS TERMINATED BY for some reason looks wrong to me. I wonder if that should be FIELDS TERMINATED BY

cdhowie Over a year ago

@JonClements Both are correct syntax: dev.mysql.com/doc/refman/5.1/en/load-data.html

user7186 Over a year ago

Thanks, this works. Although I simplified my code for this example, I have a few other columns in the table that have this same issue. When I try putting a line below the last line in 'statement' that sets a different column name, I get the same error I was getting about a ProgrammingError. If it's not too much trouble, could you add another Set clause to your code if there is another column that should have null instead of 0? Thanks so much!

cdhowie Over a year ago

@user7186 You can; delimit them with commas. SET column_a = 1, column_b = 2, ...

cdhowie Over a year ago

@user7186 Maybe because of the @ prefix, which references a variable instead of a column? Presumably that variable has never been initialized. Try removing the @ characters.

|

Collectives™ on Stack Overflow

MySQL for Python: Incorrect Integer value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related