如何将此PHP MYSQL函数转换为Python

Question

我需要在Python中复制下面的函数。我包括了正在工作的PHP代码，需要Python方面的帮助，因为我现在完全迷路了

读取RFID卡并通过串行总线将标签传输到Python。这部分代码是有效的。我遇到的问题是插入这个信息字符串作为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代码，我缺少了什么

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

更新： 现在接收到以下错误

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

Answer 1

您必须将UID添加到查询polachlder

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

Answer 2

一些变化：

1. UID = UID.rstrip()以消除尾随空格（回车符和换行符，除非您确实希望将这些字符存储在数据库中）
2. sql = "SELECT * FROM table_mkaccess WHERE id = %s"（对于准备好的语句，您不希望在%s占位符周围加引号。如果提供的实际值是字符串，SQL驱动程序将对该值执行“正确的操作”。）
3. mycursor.execute(sql, (UID,))（使用正确的变量）