python - 我想要一些建议，为什么这不会将数据插入我的 SQL 表

Question

以下是我的代码:

import MySQLdb
def insert_popularity(PersonNumber, Category, Value):

# make a connection to the dataabse
connection = MySQLdb.connect(host='localhost', user='root', \
                             passwd='password', db='inb104')

# get a cursor on the database
cursor = connection.cursor()

# construct the SQL statement
sql = ("""INSERT INTO popularity (PersonNumber, Category, Value)
        VALUES(%s, %s, %s)""", (number, category, data))

def open_file(filename):        
    txt_file = file(filename, 'r')
    for line in txt_file:
        # Split the line on whitespace
        for value in line.split():                
            return value

            number = value[0]
            data = value[1]

# execute the query
cursor.execute(sql)

# commit the changes to the database\
connection.commit()

# close the cursor and connection
cursor.close()

connection.close()

更新:

根据 Paulo's suggestion 更改我的代码后我现在收到此错误:

query() argument 1 must be string or read-only buffer, not tuple. 

在尝试更改我的代码后，我不确定它是什么:

def insert_popularity(Category, filename, cursor):

    txt_file = file(filename, 'r')
    for line in txt_file:
            # Split the line on whitespace
        number, value = line.split()
            # construct the SQL statement
        sql = ("""INSERT INTO popularity (PersonNumber, Category, Value)
                VALUES(%s, %s, %s)""", (number, Category, value))
            # execute the query
        cursor.execute(sql)

connection = MySQLdb.connect(host='localhost', user='root', \
                                 passwd='password', db='dogs')

cursor = connection.cursor()

Category = 'dogs'
insert_popularity(Category, 'dogs.txt', cursor)
connection.commit()
cursor.close()
connection.close()

Answer 1

只需简单地做，一次做一件事，不要花哨的东西容易出错并且会减慢读者在混淆过程中的速度:

sql = """INSERT INTO popularity (PersonNumber, Category, Value) VALUES (%s, %s, %s)"""
args = (number, Category, value)
cursor.execute(sql, args)

您的评论(执行查询)消失了，因为 (a) 它是错误的(插入 != 查询)和 (b) 考虑到固定代码的清晰度，固定版本(执行插入)将非常多余。

更新 出现新问题(太多值无法解压):

代替这段代码:

for line in txt_file:
        # Split the line on whitespace
    number, value = line.split()

这样做:

for lino, line in enumerate(txt_file, 1):
    pieces = line.split()
    if len(pieces) != 2:
        print "Bad data in line %d: %r" % (lino, pieces)
        continue
    number, value = pieces