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](http://stackoverflow.com/questions/5394228/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")


Answer: 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);"""
    

