How to translate this PHP MYSQL function to Python

Question

I need to replicate the below function in Python. I included the working PHP code and need help on the Python side as I'm now totally lost.

A RFID card is read and tag transferred to Python over serial. This portion of the code works. WHat I am having trouble with is inserting this string of information as the string of information to be looked up in mySQL

<?php
            require 'database.php';
    
            $UIDresult=$_POST["UIDresult"];
            $Write="<?php $" . "UIDresult='" . $UIDresult . "'; " . "echo $" . "UIDresult;" . "?>"; 
            file_put_contents('UIDContainer.php',$Write);
    
        $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "SELECT * FROM table_mkaccess  where id = ?";
            $q = $pdo->prepare($sql);
            $q->execute(array($UIDresult));
            $data = $q->fetch(PDO::FETCH_ASSOC);
            Database::disconnect();
            $msg = null;
            if (null==$data['name']) {
                    $msg = "0";
                    $data['id']=$UIDresult;
            } else {
                    $msg = "1";
            }
            echo $msg;
            
    
    
    ?>

Python Code I have tried so far, what am I missing.

import serial
import mysql.connector

rfid = serial.Serial(port = "/dev/ttyUSB0", baudrate=9600) 

while True:
    if (rfid.in_waiting > 0):
            UID = rfid.readline()
            UID = UID.decode('Ascii')
            mydb = mysql.connector.connect(
            host = "localhost",
            user = "****",
            password = "****",
            database = "****")
    

           mycursor = mydb.cursor(buffered=True)
           sql = "SELECT * FROM table_mkaccess WHERE id = '%s'"
           mycursor.execute(sql,(UIDresult,))
           data = mycursor.fetchall()
    
           if data ==0:
               print('0')
           else:
               print('1')

UPDATE: Now receiving the following error

Traceback (most recent call last): File "/home/base/testing3.py", line 17, in mycursor.execute(sql,(UIDresult,))

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '47D0D393\r\n''' at line 1

Booboo · Accepted Answer · 2020-11-22 22:13:33Z

1

A few changes:

UID = UID.rstrip() to get rid of the trailing white space (carriage return and newline characters, unless you actually want those characters stored in the database)
sql = "SELECT * FROM table_mkaccess WHERE id = %s" (For prepared statements, you do not want quotes around the %s placeholder. If the supplied actual value is a string, the SQL driver will do "the right thing" with the value.)
mycursor.execute(sql, (UID,)) (Use the correct variable)

answered Nov 22, 2020 at 22:13

Booboo

45.6k4 gold badges46 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2020-11-22 22:30:13Z

1

You have to add the UID to the query polacholder

mycursor = mydb.cursor(buffered=True)
sql = "SELECT * FROM table_mkaccess WHERE id = %s"
mycursor.execute(sql,(UID,))
data = mycursor.fetchall()

edited Nov 22, 2020 at 22:30

answered Nov 22, 2020 at 20:59

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

m_krsic Over a year ago

Thank you, that worked and got me a step further but now I get a different error.

nbk Over a year ago

UIDresult is not defined

Collectives™ on Stack Overflow

How to translate this PHP MYSQL function to Python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related