1

I try to import data into a mySQL database using Python, but I can't get it to work. I don't get any errors it looks as if everything is working OK, but the text file never gets imported.

I can import the text file just fine if I do it manually via the mysql command line in Terminal. What am I doing wrong?

imoprt mysql.connector
cnx = mysql.connector.connect(user ='user1', password ='12345', host ='127.0.0.1', database ='stockStatus')
cursor = cnx.cursor()
cursor.execute('use stockStatus')
cursor.execute('truncate table products')
cursor.execute("LOAD DATA INFILE '/Path/products.txt' INTO TABLE products IGNORE 1 LINES")
cnx.close()
elfving
  • 21
  • 1
  • 3
  • take a look here http://stackoverflow.com/questions/25573009/python-mysql-load-data-local-infile/25579771#25579771 – Peeyush Sep 30 '14 at 15:55
  • you can also switch to mysql connector python v2.0.1 which has LOCAL INFILE enable by default – Peeyush Sep 30 '14 at 15:56
  • I'm already on v2.0.1 – elfving Sep 30 '14 at 18:58
  • Ok, I think the problem is you are not doing cnx.commit() at the end. By default autocommit is set to False, there fore you have to explicitly call commit, or make the connection with autocommit=True – Peeyush Oct 01 '14 at 07:22

1 Answers1

0

Try using double quotes around the input file name:

cursor.execute('LOAD DATA INFILE "/Path/products.txt" INTO TABLE products IGNORE 1 LINES')
mhawke
  • 84,695
  • 9
  • 117
  • 138