LOAD DATA LOCAL INFILE with incremental field

Question

I have multiple unstructured txt files in a directory and I want to insert all of them into mysql; basically, the entire content of each text file should be placed into a row . In MySQL, I have 2 columns: ID (auto increment), and LastName(nvarchar(45)). I used Python to connect to MySql; used LOAD DATA LOCAL INFILE to insert the whole content. But when I run the code I see the following messages in Python console: . Also, when I check MySql, I see nothing but a bunch of empty rows with Ids being automatically generated. Here is the code:

import MySQLdb
import sys
import os
result = os.listdir("C:\\Users\\msalimi\\Google Drive\\s\\Discharge_Summary")
for x in result:
     db = MySQLdb.connect("localhost", "root", "Pass", "myblog")
     cursor = db.cursor()
     file1 = os.path.join(r'C:\\Discharge_Summary\\'+x)     
     cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE clamp_test" %(file1,));         
     db.commit()
db.close()

Can someone please tell me what is wrong with the code? What is the right way to achieve my goal?

It's trying to parse the ID column from your data... Read the error `Incorrect Integer` — OneCricketeer, May 26 '18 at 01:12
@cricket_007 thanks, I am new to Phyton and not much familiar with it. Can you please instruct me how to do that? — nina_dev, May 26 '18 at 01:15
I can't see your file, but 1) https://stackoverflow.com/questions/26903304/reading-data-from-a-csv-file-in-python 2) https://stackoverflow.com/q/5687718/2308683 — OneCricketeer, May 26 '18 at 01:17

score 0 · Answer 1 · answered May 27 '18 at 06:25

0

I edited my code with:

.....cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE clamp_test LINES TERMINATED BY '\r' (Lastname) SET id = NULL" %(file1,))

and it worked :)

answered May 27 '18 at 06:25

nina_dev

625
2
10
19

LOAD DATA LOCAL INFILE with incremental field

1 Answers1