Python Mysql Load data in file

Question

I have a database with columns like this

Col1 Col2 Col3 Col4 Col5

And i have two csv files which has data like this

File-1
Column1, Column2, Column4, Column5
1,2,4,5
1,2,4,5
1,2,4,5
1,2,4,5
1,2,4,5
File-2
Column1, Column3, Column5
1,3,5
1,3,5
1,3,5
1,3,5
1,3,5
1,3,5

Please tell me the Load data with infile command to load file1 and file2 data in respective columns in table. i.e. from file1 the column4 from .csv shouldo go into the COl4 of the table.

With this command the Column4 goes in Col3 and Column5 into Col4.

import MySQLdb
import os
import string
import warnings
print "File Loader Started : QT"
output_path="F:/TestData_SD/QT_Files/"
# Open database connection
db = MySQLdb.connect (host="localhost",port=3307,user="root",\
 passwd="gamma123",db="db_schema")
sql = """LOAD DATA LOCAL INFILE '{}'
INTO TABLE struct_pqrst
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\r\\n'
IGNORE 1 LINES;;"""
l = os.listdir(output_path)
for file_name in l:
 if file_name.endswith('.csv'):
 try:
 cursor = db.cursor()
 cursor.execute(sql.format(output_path+file_name))
 db.commit()
 print "Loading file:"+file_name
 except Exception:
 print "Exception"
 # Rollback in case there is any error
 db.rollback()
# disconnect from server
db.close()
print "File Loader Ended : QT"

please help

• Mad Dog Tannen: I think you need a solution like this, stackoverflow.com/questions/4202564/…
• Ranchoddas shyamaldas chanchad: Dear Kay, i need some more help with this i have a field called "Inter-Mast_Height_Diff._TLV" and when i load this field i get an exception "ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-Mast_Height_Diff._TLV, RunDate, SourceFileName)' at line 7")". Could you please tell me how to resolve this?
• Mad Dog Tannen: Wrap the column with ` or replace the - with a _. Let me know!
• Ranchoddas shyamaldas chanchad: yes the ` trick did the work. Thanks a lot

Answer 1

In the second file you could define the columns.

LOAD DATA LOCAL INFILE '{}' 
INTO TABLE struct_pqrst (Column1, Column3, Column5)
FIELDS TERMINTED BY ',' 
LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Have a look in the link i posted.

Ref: How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE