Problem with Python and MySQL

Question

I am trying to import a large text file into a MySQL database. The SQL statement is as follows:

LOAD DATA INFILE '/tmp/epf/full/album_popularity_per_genre' 
INTO TABLE album_popularity_per_genre 
CHARACTER SET UTF8 FIELDS TERMINATED BY X'01' LINES TERMINATED BY '\n' 
IGNORE 45 LINES (export_date, storefront_id, genre_id, album_id, album_rank)

The above works when I run it in phpMyAdmin, however when I write a simple function in Python that uses the above SQL statement I get an error.

Here is the Python code,

def test():
    dbConnection = MySQLdb.connect(
    charset='utf8', 
    host='localhost', 
    user='root', 
    passwd='root', 
    db='epf')

    cursor = dbConnection.cursor()

    exStr = """LOAD DATA INFILE '/tmp/epf/full/album_popularity_per_genre' 
               INTO TABLE album_popularity_per_genre CHARACTER SET UTF8 
               FIELDS TERMINATED BY X'01' LINES TERMINATED BY '\n' 
               IGNORE 45 LINES 
               (export_date, storefront_id, genre_id, album_id, album_rank)"""

    try:
        cursor.execute(exStr)
    except MySQLdb.Warning, e:
        print "Warning %s" % (str(e))
    except MySQLdb.IntegrityError, e:     
        print "Error %d: %s" % (e.args[0], e.args[1])

    #Clean up
    cursor.close()
    dbConnection.close()

The error I get is as follows,

Warning Data truncated for column 'album_rank' at row 1

My question now is, why does the raw SQL statement work but when I try to run the Python code, no data is imported into the database?

Keith · Accepted Answer · 2011-05-24 19:58:54Z

2

The Python DBAPI is implicitly transactional. Try adding dbConnection.commit() after the execute.

edited May 24, 2011 at 19:58

answered May 24, 2011 at 15:55

Keith

43.2k11 gold badges61 silver badges77 bronze badges

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

2 Comments

David Over a year ago

I think you mean dbConnection.commit(). I've tried that but I'm still having the same problems. Thanks.

David Over a year ago

I just rechecked the database and your suggestion did work. Thank you. Also I did make a mistake a warning for an error. I'll just ignore that warning.

Collectives™ on Stack Overflow

Problem with Python and MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related