我有一个tab-delimited txt file,其中的行由制表符分隔,行由换行符分隔。实际上是这样的:

476502291\t\tLF3139812164\t\tTitle 1\tKids & Family\nGRAV_2011\t\tThe Full Picture\tIndependent\n [...etc...]

请注意,有时值由两个选项卡而不是一个选项卡分隔。

我需要将此插入到mysql表中,这将导致以下结果:

ID             title               genre
476502291      Title 1             Kids & Family
GRAV_2011      The Full Picture    Independent


我如何读取制表符分隔的txt文件并运行for循环,以便使用MySQLdb将值插入名为vendor的表中?

>>> import MySQLdb
>>> conn = MySQLdb.connect (host = "localhost",
                             user = "me",
                             passwd = "password",
                             db = "my-db")
>>> cursor = conn.cursor ()
>>> # for loop  # how to read from the txt file to insert it as required?
>>>     # cursor.execute (INSERT...)
>>> conn.commit()
>>> conn.close()

最佳答案

步骤1.阅读csv模块。 http://docs.python.org/library/csv.html。这就是您想要的。

with open('your_data_file.dat','r') as source:
    rdr= csv.reader( source, delimiter='\t', quotechar='')
    for row in rdr:
        # you have your columns with which to do your insert.
conn.commit()


步骤2,也请阅读上下文管理器。

from contextlib import closing

with open('your_data_file.dat','r') as source:
    rdr= csv.reader( source, delimiter='\t', quotechar='')
    with closing(conn.cursor()) as cursor:
        for row in rdr:
            # you have your columns with which to do your insert.
conn.commit()


这将确保正确关闭游标和文件。

10-08 13:44
查看更多