Insert multiple tab-delimited text files into MySQL with Python?

Question

Hi everyone,

I am pretty new to Python programing and programing in general so bare with me.

I am trying to create a program that takes a number of tab delaminated text files, and works through them one at a time entering the data they hold into a MySQL database. There are several text files, like movies.txt which looks like this:

1   Avatar
3   Iron Man
3   Star Trek

and actors.txt that looks the same etc. Each text file has upwards of one hundred entries each with an id and corresponding value as seen above. I have found a number of code examples on this site and others but I can't quite get my head around how to implement them in this situation.

So far my code looks something like this ...

import MySQLdb

database_connection = MySQLdb.connect(host='localhost', user='root', passwd='')

cursor = database_connection.cursor()

cursor.execute('CREATE DATABASE library')

cursor.execute('USE library')

cursor.execute('''CREATE TABLE popularity (
                    PersonNumber INT,
                    Category VARCHAR(25),
                    Value VARCHAR(60),
                    )
                    ''')

def data_entry(categories):

Everytime i try to get the other code I have found working with this I just get lost completely. Hopeing someone can help me out by either showing me what I need to do or pointing me in the direction of some more information.

Any help would be greatly appreciated.

Thanks

Examples of the code I have been trying to adapt to my situation are:

import MySQLdb, csv, sys
conn = MySQLdb.connect (host = "localhost",user = "usr", passwd = "pass",db = "databasename")
c = conn.cursor()
csv_data=csv.reader(file("a.txt"))
for row in csv_data:
print row
c.execute("INSERT INTO a (first, last) VALUES (%s, %s), row")
c.commit()
c.close()

and:

http://stackoverflow.com/questions/928918/python-file-read-write

Answer 1

MySQL can read TSV files directly using the mysqlimport utility or by executing the LOAD DATA INFILE SQL command. This will be faster than processing the file in python and inserting it, but you may want to learn how to do both. Good luck!