current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Code setup according to examples found here:

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

gives for answer

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

It seems to me that the single quotes get transferred to the MySql engine, this is not what I want.

share|improve this question

1 Answer 1

up vote 1 down vote

Single quotes are automatically added since you are binding a parameter that is supposed to be a field, a table name, a value. But you can't use this syntax to make a string replacement, it is different from a basic formatted string with placeholders

You can solve the problem with:

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)

Or you can change your query to 

query = 'delete from %s where code_icao regexp concat(%s, \'[A-Z][A-Z]\')'
cursor.execute(query, (table,ICAO_COUNTRY))
share|improve this answer
    
Well, I really believed Python would not impose such inelegant coding. Better this than nothing, though. That is, if it would work, but alas it doesn't. –  Karlchen9 18 hours ago
    
@Karlchen9: an other way is to use CONCAT in your query. –  Casimir et Hippolyte 18 hours ago
    
@Karlchen9: An other problem, if you use double quotes in your query, you must have ansi_quotes enable in MySQL. If it isn't the case, try to replace them with single quotes. –  Casimir et Hippolyte 18 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.