Python MySQL Connector: Load Data Infile, best way to add fields

  bigdata, mysql, mysql-connector, python

I have a MySQL database containing tens of millions of rows of data. I am uploading thousands of text files using "LOAD DATA LOCAL INFILE".

Right now I have two columns: "username" and "password". I want to create a third column called "source". This would be the filename of the document being uploaded.

Is it possible to amend the query below to make that happen?

for cFile in cFiles:
    query = "LOAD DATA LOCAL INFILE '"+cFile+"' INTO TABLE combo FIELDS TERMINATED BY ':' LINES TERMINATED BY 'n' IGNORE 1 ROWS;"
    cursor = mydb.cursor()
    cursor.execute(query)
    mydb.commit()

In other words, upload the data from "cFile" AND add a third column called "cFile".

Source: Python Questions

LEAVE A COMMENT