Search code examples
pythonmysqlbackticks

Cant execute Mysql query on python that is on a string because backtick/grave cause conflict


I have this query:

CREATE TABLE IF NOT EXISTS `configuration` (
  `key` varchar(128) NOT NULL,
  `value` varchar(1024) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It is correctly executed on workbench and on online mysql interpreters, I have a python program that when it executes that same query it launches a syntax error. The query is on a string that is formatted this way:

Query = """
CREATE TABLE IF NOT EXISTS `configuration` (
      `key` varchar(128) NOT NULL,
      `value` varchar(1024) NOT NULL,
      PRIMARY KEY (`key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """

Previously I had the same problem with this query:

CREATE TABLE IF NOT EXISTS `logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_id` int(11) NULL,
  `level` int(11) NOT NULL,
  `source` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `detail` longtext NULL,
  PRIMARY KEY (`id`),
  KEY `idx_logs_timestamp` (`timestamp` DESC),
  KEY `idx_logs_level` (`level`),
  KEY `idx_logs_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After removing the backticks "`" the query worked correctly. But if I remove the backticks from the first query the syntax error stays.

Here is the code that I use when executing the query on python:

def Execute_Query_On_Database(database,query):
     server_database= config.Read_Property("DATABASES","Server_Database")
     user= config.Read_Property("SSH","SSH_Username")
     password= config.Read_Property("SSH","SSH_Password")
     operation=  "mysql -u "+user+" -h "+server_database+" -p "+database+" --password="+password+"  -e \""+query+"\""
     result= ssh.execute_ssh_command(operation)
     return result

print (Execute_Query_On_Database("database_default",Query))

Solution

  • So I ended up using pyMySQL to run a SQL file with the query on it. Couldn't find what was causing the query to fail on the code.