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

Question

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.

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:

Python File Read + Write

Try moving the " in your second example so that row is passed to the execute call. c.execute("INSERT INTO a (first, last) VALUES (%s, %s)", row) This link has a couple execute examples. mysql-python.sourceforge.net/MySQLdb.html#connection-objects — kevpie
– kevpie, Commented Oct 14, 2010 at 1:58

Joshua Martell · Accepted Answer · 2010-10-14 02:49:56Z

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!

answered Oct 14, 2010 at 2:49

Joshua Martell

7,2122 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mischa Colley Over a year ago

Thanks. Ok so this really helped. I am learning Python more than SQL so I would like to do it with python even if it is slower. What I now have is cursor.execute("LOAD DATA LOCAL INFILE 'movies.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'") which is sort of working. I get the data in but not without removing Category VARCHAR(25) which I actually need. I need someway to populate Category with the names of the files i.e. movies, tv etc so that this will work. I know it must be obvious what I need to do but I can't get work i out. Thanks for you help.

Joshua Martell Over a year ago

Put your Category column last: LOAD DATA LOCAL INFILE 'movies.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n (PersonNumber, Category, Value) SET Category='Movies'` will set the Category for each row.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related