我想要一些建议，为什么这不会将数据插入到我的 SQL 表中 [英] I would like some advice why this would not insert data into my SQL table

问题描述

以下是我的代码:

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

更新:

按照保罗的建议 我现在收到这个错误:

After changing my code as per Paulo's suggestion I now get this error:

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

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

I am not sure what it is after trying to change my code:

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

推荐答案

您已经创建了作为元组执行的查询.解决这个问题有两种可能:

You've created the query to execute as a tuple. There two possibilities to solve this:

1. 使用创建的查询 (sql) 作为参数列表:

1. Use the created query (sql) as a list of arguments:

sql = ("""INSERT INTO 流行度(PersonNumber, Category, Value)VALUES(%s, %s, %s)""", (number, Category, value))# 执行查询cursor.execute(*sql)

直接在execute方法中添加查询:

Directly add the query to the execute method:

cursor.execute("""INSERT INTO 流行度(PersonNumber, Category, Value)VALUES(%s, %s, %s)""", (number, Category, value))

第二个绝对是比第一个更好的选择.感谢所有评论！

Number 2 is definitely a better option than the first one. Thanks to all comments!

这篇关于我想要一些建议，为什么这不会将数据插入到我的 SQL 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！