Unknown column in where clause when updating database

Question

I'm trying to add info into a record from two different files, I'm trying to achieve this by opening the first file and adding to a record, then opening the second and updating that record.

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

I am receiving a Unknown column 'start' in 'where clause' error, "start" is just the string from the first line in my second text file. I'm using string formatters right now because otherwise I get a syntax error, this code is only being used to update the DB once, not in production.

Your `for` loop keeps updating the same rows of the table with different replies. Is that really what you want? — Barmar, Apr 13 '18 at 02:06
No I dont want that lol, I want to update different rows, the rows that have the proper text to correspond with the proper reply. I've been struggling with this the whole day Doe each record have an id automatically or do you mean I should make one? — Amon, Apr 13 '18 at 02:09

Barmar · Accepted Answer · 2018-04-13T02:13:58.617

1

You need to put quotes around the value, since it's a string, just like you did for the string you're setting reply to.

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

But it would be better to use a prepared statement rather than string formatting, to prevent SQL injection. Then you don't put quotes around placeholders, cursor.execute replaces them safely.

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

Also, your looping is wrong. You don't want to loop through the entire table3 for every line in table2, you just want to read both files in parallel. See 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()

edited Apr 13 '18 at 02:13

answered Apr 13 '18 at 01:51

Barmar

741,623
53
500
612

Okay, do I not need to use quotes around the value in the second example? – Amon Apr 13 '18 at 01:57
Correct, read the documentation of `cursor.execute()` to see how it does parameter replacements. – Barmar Apr 13 '18 at 02:00
Okay thanks, but this is giving me another problem now. I'm literally getting `%s` inserted into my database under my "text" column – Amon Apr 13 '18 at 02:01
The `text` column is just in the `WHERE` clause, this code can't modify it. It only modifies the `reply` column. – Barmar Apr 13 '18 at 02:03
Did you also change the `INSERT` line? If you got rid of the format operator, you have to remove the quotes. – Barmar Apr 13 '18 at 02:04
I've updated the answer to show the proper way to loop through the two files. – Barmar Apr 13 '18 at 02:14
You probably also want to do `line = line.strip()` and `robo_line = robo_line.strip()` to remove the newlines before you put them into the DB. – Barmar Apr 13 '18 at 02:18
ahh thank you for this, it was one of those problems where i didn't even know the question I was asking. The best I could come up with was "How do I insert into a record from multiple files?" – Amon Apr 13 '18 at 02:26

Unknown column in where clause when updating database

1 Answers1