CODEWITHSUNDEEP
pythonmysqlcsv
Ranchoddas shyamaldas chanchad
Ranchoddas shyamaldas chanchad

Reputation: 103

Python Mysql Load data in file

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

Upvotes: 0

Views: 5873

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

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

Upvotes: 1

Related Questions