0

I'm using pymysql to load a large csv file into a database, because of memory limitations im using load infile rather than insert. however after the code completes when i query the server it for the data in the table it returns an empty set.

import pymysql


conn = pymysql.connect(host = 'localhost', port = 3306, user = 'root', passwd = '', local_infile = True)
cur = conn.cursor()

cur.execute("CREATE SCHEMA IF NOT EXISTS `test`DEFAULT "
            "CHARACTER SET utf8 COLLATE utf8_unicode_ci ;")
cur.execute("CREATE TABLE IF NOT EXISTS "
            "`test`.`scores` ( `date` DATE NOT NULL, "
            "`name` VARCHAR(15) NOT NULL,"
            "`score` DECIMAL(10,3) NOT NULL);")
conn.commit()

def push(fileName = '/home/pi/test.csv', tableName = '`test`.`scores`'):
    push = """LOAD DATA LOCAL INFILE "%s" INTO TABLE %s
           FIELDS TERMINATED BY ','
           LINES TERMINATED BY '\r\n'
           IGNORE 1 LINES
           (date, name, score);""" % (fileName, tableName)
    cur.execute(push)
    conn.commit()
push()

I get some truncation warnings but no other errors or warnings to work off of. any ideas on how to fix this?

user3170242
  • 107
  • 11

1 Answers1

0

I did a few things to fix this, First I changed the config files for my sql server to allow load infile, following this MySQL: Enable LOAD DATA LOCAL INFILE. Then the problem was with the line,

LINES TERMINATED BY '\r\n'

the fix was to change it to 

LINES TERMINATED BY '\n'

after that the script runs fine and is significantly faster than inserting row by row

Community
  • 1
  • 1
user3170242
  • 107
  • 11