Python pymySQL 发送带引号的字符串

Question

我正在尝试像这样向 mySQL 数据库中插入一个字符串:

cur.execute('UPDATE connections SET cmd = "' + command + '", client_new = 1 where ip = "' + ip + '"')

当我将 command 设置为 echo "hello" 时，出现此错误:

Traceback (most recent call last):
  File "C:/Users/Owner/Desktop/master.py", line 111, in <module>
    main ()
  File "C:/Users/Owner/Desktop/master.py", line 80, in main
    print(""); command(); print("")
  File "C:/Users/Owner/Desktop/master.py", line 29, in command
    cur.execute('UPDATE connections SET cmd = "' + command + '", client_new = 1 where ip = "' + ip + '"')
  File "M:\Python34\lib\site-packages\pymysql\cursors.py", line 146, in execute
    result = self._query(query)
  File "M:\Python34\lib\site-packages\pymysql\cursors.py", line 296, in _query
    conn.query(q)
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 781, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 942, in _read_query_result
    result.read()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 1138, in read
    first_packet = self.connection._read_packet()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 906, in _read_packet
    packet.check_error()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 367, in check_error
    err.raise_mysql_exception(self._data)
  File "M:\Python34\lib\site-packages\pymysql\err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "M:\Python34\lib\site-packages\pymysql\err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.ProgrammingError: (1064, '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 \'hello"", client_new = 1 where ip = "123.456.789.012"\' at line 1')

我知道我将数据传递到数据库的方式存在问题，但我该如何正确地进行传递？

Answer 1

你可以使用占位符，在这种情况下 mysql 驱动程序会转义你的查询:

cur.execute('UPDATE connections SET cmd=%s, client_new=1 where ip=%s', (command, ip))