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?
I have no idea how your text file is formatted, but file.read() gives you the whole file as a single string and it seems like you have six fields to fill. So maybe the file consists of 6 tab- or space-separated fields?
First, split the file into lines with file.readlines() instead of file.read(). Next, build a list of rows that you can feed into executemany:
values = [line.split() for line in file_content]
cursor.executemany(query, values)
The split method splits the lines in the file on whitespace into a tuple, e.g., the string a b c is turned into the tuple ('a', 'b', 'c'), so that the list comprehension produces a list of tuples that can be fed into cursor.executemany to perform a bulk insert.
As @Evan points out, you also have to specify the columns the values are associated to in your SQL query, e.g.,
INSERT INTO EM (field, spam, ham, eggs, price, ni) VALUES (%s, %s, %s, %s, %s, %s)
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"
i think your problem is readlines() and values = [line.split() for line in file_content]
To start off in order for python to communicate with your SQl database you have to use an adapter such as psycopg2. Once you have installed psycopg2 you have to import it. Another segment that would also improve the simplicity and functionality of your code, is by using a csv reader. This can be done by simply importing csv. SQL databases are stored as a csv file so using this will help you read csv files while using python.
You can find more about using csv reader here: https://docs.python.org/2/library/csv.html
In terms of your code try this:
import MySQLdb
import psycopg2
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()
By installing and importing psycopg2 will allow python to understand the SQL quires you are trying to execute.
