CODEWITHSUNDEEP
pythonmysql-pythonmysql-connector
Al-
Al-

Reputation: 27

MySQL Unread Result Found When Reusing Cursor Twice

Greetings

I'm trying to write a python script on Pi for smart lock where it detect RFID and authenticate it with TOTP. When I run the script, it can detect the first card just fine and it complete from #Start to #End, but when I'm trying to tap the second card, it failed with mysql.connector.errors.InternalError: Unread result found in the line cursor.execute("Select id, name, secret From users Where rfid_uid="+str(id)).

What can I do to fix it so I dont need to re run the script just to detect the second card.

Both card uid were in the database.

scripts

try:
    while True:
        
        #Start
        
        print("Place card near the scanner")
        mylcd.lcd_display_string("Place card near",1)
        mylcd.lcd_display_string("the scanner",2)
        id, text = reader.read()
        cursor.execute("Select id, name, secret From users Where rfid_uid="+str(id)) #Traceback said it crash here when Im trying to tap different card (second card)
        result = cursor.fetchone()
        
        if cursor.rowcount >= 1:
            mylcd.lcd_clear()
            print("Welcome, ", result[1])
            mylcd.lcd_display_string("Welcome",1)
            mylcd.lcd_display_string(result[1],2)
            time.sleep(2)
            mylcd.lcd_clear()
            print("Type the code from your authenticator")
            mylcd.lcd_display_string("Type in the code",1)
            
            cursor.execute("Select secret From users Where rfid_uid="+str(id))
            secret = result[2]
            

            totp = pyotp.TOTP(str(secret))
            otp = totp.now()
            
            your_code = input('')
            
            mylcd.lcd_display_string(your_code,2)
            
            if your_code == otp:
                mylcd.lcd_clear()
                print("Code Valid, Opening the door")
                mylcd.lcd_display_string("Code Valid",1)
                mylcd.lcd_display_string("Opening the door",2)
                time.sleep(5)
            else:
                mylcd.lcd_display_string("Invalid Code",1)
                print("Invalid")
                
            time.sleep(5)
            mylcd.lcd_clear()
            
            #End

except KeyboardInterrupt:
    mylcd.lcd_clear()
    print("\nApplication Stopped")

finally:
    GPIO.cleanup()

Thank you!

Upvotes: 1

Views: 221

Answers (1)

Jared Willets
Jared Willets

Reputation: 41

I had the same issue recently if I am understanding the problem correctly. To make the cursor capable of executing multiple times, you can add the buffered attribute to the cursor and set it to true. You can add it like this:

cursorVariable = database.cursor(buffered = True)

I hope this solved your problem and good luck!

Upvotes: 1

Related Questions