How to store text file into MySQL database using python

Question

import MySQLdb
import re

def write():
    file = open('/home/fixstream/Desktop/test10.txt', 'r')
    print file.read()
    file.close()
write()

Above code i have, now i want to store the text file into mysql db. I am new to python as well as database. So anyone can help me with this?

Answer 1

I suggest you reading this MySQLdb tutorial. First, you need to store content of the file in a variable. Then it's simply connecting to your database (which is done as you can see in the link) and then executing INSERT query. Prepared statements are done in similar way as common string formatting in python.

You need something like this:

import MySQLdb

db = MySQLdb.connect("localhost","user","password","database")
cursor = db.cursor()

file = open('/home/fixstream/Desktop/test10.txt', 'r')
file_content = file.read()
file.close()

query = "INSERT INTO table VALUES (%s)"

cursor.execute(query, (file_content,))

db.commit()
db.close()

Note the comma after file_content - this ensures the second argument for execute() is a tuple. Also note db.commit() which ensures writing changes.

If you need further explanation, ask.