python - 更新数据库时where子句中的未知列

Question

我正在尝试将两个不同文件中的信息添加到记录中，我尝试通过打开第一个文件并添加到记录中，然后打开第二个文件并更新该记录来实现此目的。

with open('C:/Users/ELITEBOOK/documents/github/chatbot/chatbot/bot/human_text.txt', 'r') as table2, open('C:/Users/ELITEBOOK/documents/github/chatbot/chatbot/bot/robo_text.txt','r') as table3:
        for line in table2.readlines():
            message_text = line
            #for robo_line in table3.readlines():
            message_intent = ''
            message_entities = ''
            test = 'hello'
            #cursor.execute('START TRANSACTION')
            cursor.execute("INSERT INTO conversation (text) VALUES ('%s')" % line)
            cnx.commit()
            #cursor.execute((line))
            for robo_line in table3.readlines():
                #message_reply = robo_line  
                cursor.execute("UPDATE conversation SET reply = '%s' WHERE text = %s " % (robo_line, line)) 
                #cursor.execute(robo_line)
                cnx.commit()

我收到“where 子句”中的未知列“start” 错误，“start”只是第二个文本文件中第一行的字符串。我现在正在使用字符串格式化程序，因为否则我会收到语法错误，此代码仅用于更新数据库一次，而不是在生产中。

Answer 1

您需要在该值两边加上引号，因为它是一个字符串，就像您对要设置 reply 的字符串所做的那样。

cursor.execute("UPDATE conversation SET reply = '%s' WHERE text = '%s' " % (robo_line, line)) 

但是最好使用准备好的语句而不是字符串格式，以防止 SQL 注入(inject)。然后，您无需在占位符周围添加引号，cursor.execute 会安全地替换它们。

cursor.execute("UPDATE conversation SET reply = %s WHERE text = %s ", (robo_line, line)) 

另外，你的循环是错误的。您不想为 table2 中的每一行循环遍历整个 table3，您只想并行读取两个文件。请参阅Read two textfile line by line simultaneously -python

with open('C:/Users/ELITEBOOK/documents/github/chatbot/chatbot/bot/human_text.txt', 'r') as table2, open('C:/Users/ELITEBOOK/documents/github/chatbot/chatbot/bot/robo_text.txt','r') as table3:
    for line, robo_line in zip(table2, table3):
        message_text = line
        message_intent = ''
        message_entities = ''
        test = 'hello'
        #cursor.execute('START TRANSACTION')
        cursor.execute("INSERT INTO conversation (text, reply) VALUES (%s, %s)", (line, robo_line))
        cnx.commit()