Farnaz Soheili Farnaz Soheili - 1 year ago 121
MySQL Question

insert into mysql table using python from text file

import MySQLdb

db = MySQLdb.connect(host="?",
user="root",
passwd="?",
db="test")
cursor = db.cursor()

file = open('...../EM.txt', 'r')
file_content = file.read()
file.close()

query = "INSERT INTO EM VALUES (%s,%s,%s,%s,%s,%s)"

cursor.execute(query, (file_content,))

db.commit()
db.close()


I have try this code to read from a text file and insert into EM table ...can any one help me make this work?

Answer Source

Are you trying to insert the entire text file into a string column in the database, or do you want to import tabular data into the database? It seems like you want to import tabular data based on the way you specified the values, so I'll use that as my assumption.

To do this, you need to read each row from your file and insert it into the database one row at a time. You also need to update your query syntax to specify the column names you are inserting into.

import csv
import MySQLdb

db = MySQLdb.connect(host='localhost',
    user='root',
    passwd='?',
    db='test')
cursor = mydb.cursor()

with open('...../EM.txt') as f:
    reader = csv.reader(f)
    for row in reader:
        cursor.execute('''INSERT INTO testcsv(col1, col2, col3, col4, col5, col6)
                          VALUES("%s", "%s", "%s", "%s", "%s", "%s")
                       ''', row)

#close the connection to the database.
mydb.commit()
cursor.close()
print "Done"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download