删除不适用于mysqlpython连接

Question

我想使用mysql-python连接删除表中的一条记录。下面是我在python中尝试的代码，但它给出了错误。同一命令单独在mysql中运行良好，但在使用python连接时不起作用

def deleterecord():

     conn=py.connect(host='localhost',user='root',passwd='richajain',db='mysql')
     cur=conn.cursor()

     print('\nFor User''s Convenience View Entire Records and Decide Whose Details You Want To Delete')

     displayall()

     sno=int(input('\nEnter The Serial Number Of Medicine Of Which You Want To Delete The Details'))

     cur.execute('delete from Pharmaceuticals where sno= % s',sno)

     conn.commit()
     print('\nYour Record Has Been Deleted ')


     print('\nNow Existing Records Are :')
     displayall()

     cur.close()
     conn.close()

正在出现的错误

    For Users Convenience View Entire Records and Decide Whose Details You Want To Delete
    +-----+------------+------------------+------------+------------+-------+------+-------+
    | sno |  medname   |     saltname     |  mfgdate   |  expdate   | price | cost | stock |
    +-----+------------+------------------+------------+------------+-------+------+-------+
    |  1  | Meconeuron | Methylcobalamine | 2019-03-12 | 2021-08-18 |  250  | 275  |   7   |
    |  2  |  Crocine   |   Paracetamol    | 2019-08-11 | 2021-01-15 |  100  | 110  |   9   |
    |  3  |    Ocid    |    Omeprazole    | 2019-04-10 | 2020-07-23 |   50  |  55  |   10  |
    |  4  | citrizine  |  Levo-citrizine  | 2020-01-02 | 2022-06-09 |  120  | 132  |   4   |
    |  5  | Gluconorm  |    Metformine    | 2019-07-08 | 2022-03-29 |  375  | 413  |   10  |
    +-----+------------+------------------+------------+------------+-------+------+-------+

    Enter The Serial Number Of Medicine Of Which You Want To Delete The Details 5

    Traceback (most recent call last):
    File "C:\Users\admin\OneDrive\Desktop\medicine details(Anika-12th).py", line 245, in <module>
    deleterecord()      # Function calling to delete the record(s) from the tabl File 
    "C:\Users\admin\OneDrive\Desktop\medicine details(Anika-12th).py", line 131, in deleterecord
    cur.execute('delete from Pharmaceuticals where sno= % s',sno)
    File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-  
    packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
    File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site- 
    packages\mysql\connector\connection.py", line 599, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
    File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site- 
    packages\mysql\connector\connection.py", line 487, in _handle_result
    raise errors.get_exception(packet)
    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 '% s' at line 1

Answer 1

我成功地执行了删除操作，将您的查询从以下位置更改：

cur.execute('delete from Pharmaceuticals where sno= % s',sno)

改为：

cur.execute('delete from Pharmaceuticals where sno={}'.format(sno))

注意，在我的示例中，我使用字符串类型的format方法。以及插值值sno

如果希望保留语法，则必须从查询中删除%和s字符之间的whitespace，并且作为execute方法的参数，必须使用元组

cur.execute('delete from Pharmaceuticals where sno= %s',(sno,))

这里是带有some examples的文档链接

Answer 2

您可以使用?代替%s。这是将变量分配给SQL查询时最有用的方法

cur.execute('DELETE FROM Pharmaceuticals WHERE sno=?', (sno,))