PHP Versions Question posted in
You can find a list of all versions here.

Php versions – How to translate this PHP MYSQL function to Python

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 ’47D0D393rn”’
at line 1

Tags:

2

Answers


  1. 0

    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()
    Login or Signup to reply.
  2. 0

    A few changes:

    1. 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)
    2. 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.)
    3. mycursor.execute(sql, (UID,)) (Use the correct variable)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search