python 使用 REGEXP 和 mysql 连接器从 mysql 中删除行

Question

根据此处找到的示例进行代码设置:

 for ICAO_COUNTRY in ['GM','DA','DT']:
  table='aerodrome'
  query = 'delete from %s where code_icao regexp "%s[A-Z][A-Z]"'
  cursor.execute(query,(table,ICAO_COUNTRY))

给出答案

Traceback (most recent call last):
  File "bin/cleanup2", line 22, in <module>
    cursor.execute(query,(table,ICAO_COUNTRY))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 491, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 635, in cmd_query statement))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 553, 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 ''aerodrome' where code_icao regexp "'GM'[A-Z][A-Z]"' at line 1

在我看来，单引号被转移到 MySql 引擎，这不是我想要的。

Answer 1

自动添加单引号，因为您绑定(bind)的参数应该是一个字段、一个表名、一个值。但是你不能使用这种语法来进行字符串替换，它不同于带有占位符的基本格式化字符串

您可以通过以下方式解决问题:

for ICAO_COUNTRY in ['GM','DA','DT']:
  table='aerodrome'
  query = 'delete from %s where code_icao regexp \'' + ICAO_COUNTRY + '[A-Z][A-Z]\'' 
  cursor.execute(query, table)

或者您可以将查询更改为

query = 'delete from %s where code_icao regexp concat(%s, \'[A-Z][A-Z]\')'
cursor.execute(query, (table,ICAO_COUNTRY))