使用Python将多个制表符分隔的文本文件插入MySQL吗? [英] Insert multiple tab-delimited text files into MySQL with Python?

问题描述

我正在尝试创建一个程序，该程序接受多个制表符分层的文本文件，并一次将它们保存的数据输入到MySQL数据库中.有几个文本文件，例如movie.txt，如下所示:

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

和外观相同的actors.txt等.每个文本文件都有一百多个条目，每个条目都有一个id和相应的值，如上所示.我在此站点和其他站点上找到了许多代码示例，但是我不太了解如何在这种情况下实现它们.

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()

和:

Python文件读+写

推荐答案

MySQL可以使用mysqlimport实用程序或通过执行LOAD DATA INFILE SQL命令直接读取TSV文件.这将比在python中处理文件并将其插入要快，但是您可能想学习如何同时执行这两个操作.祝你好运！

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!

这篇关于使用Python将多个制表符分隔的文本文件插入MySQL吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！