CODEWITHSUNDEEP
mysqlpython-3.x
nina_dev
nina_dev

Reputation: 695

loop through multiple unstructured txt files to insert into MySql using DATA LOCAL INFILE

I have multiple txt files in a directory and I want to insert all of them into mysql; the content of each file show should occupy a row. In MySQL, I have 2 columns: ID (auto increment), and LastName(nvarchar(45)). First, I used insert into, and I got the error that I cannot insert too many lines! Then, I used the following code, but it gives me error 1064. Here is the code:

import MySQLdb
import sys
import os
result = os.listdir("path")
for x in result:
    db = MySQLdb.connect("localhost","root","password","myblog")
    cursor = db.cursor()
    file = os.path.join('path\\'+x)    
    cursor.execute("LOAD DATA LOCAL INFILE file INTO clamp_test(ID, LastName)");   
    file.close()          
db.commit()
db.close

Can you please tell me what am I doing wrong? Is this the right way to insert multiples lines of an unstructhered file into MySql?

Upvotes: 0

Views: 182

Answers (1)

spencer7593
spencer7593

Reputation: 108510

One issue is the string literal containing the SQL text 

 "LOAD DATA LOCAL INFILE file INTO ..."
                         ^^^^

Here, file is just characters that are part of the literal string. It's not a reference to a variable to be evaluated.

The SQL statement we need to send to MySQL would need to look like 

 LOAD DATA LOCAL INFILE '/mydir/myfile.txt' INTO ...
                        ^^^^^^^^^^^^^^^^^^^

In the code, it looks like what we want to happen is to have the variable file evaluated, and the result of the evaluation incorporated that into the SQL text string.

Something like this:

 "LOAD DATA LOCAL INFILE '%s'  INTO ... " % (file)

Upvotes: 1

Related Questions