-1

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))
M. Montoya
  • 13
  • 4
  • 1
    Key is a reserved word - can you change? – P.Salmon Feb 20 '19 at 15:33
  • Also I cannot reproduce your issue - can you add the python code including the connection request (obfuscated) and the cursor execution statement. – P.Salmon Feb 20 '19 at 15:41
  • @P.Salmon I added the python method, the thing is that I am testing a system that uses those specific keys, if I change them I wont be testing the system correctly. – M. Montoya Feb 20 '19 at 15:51
  • Why don't you use a MySQL library for python to connect to MySQL? – Shadow Feb 20 '19 at 15:54
  • @Shadow If I remember correctly, I need to configure the server to access, thing I dont have to do with a SSH connection, and configuring the server is something I cant do right now. – M. Montoya Feb 20 '19 at 16:07
  • I got that, but you can do the SSH tunnelling within python, see for example the following question on SO: https://stackoverflow.com/questions/21903411/enable-python-to-connect-to-mysql-via-ssh-tunnelling – Shadow Feb 20 '19 at 18:29
  • @Shadow we where having some problems connecting using that method but some changes in the code fixed it, I am using SSHtunnelForwarder and pymysql, should I add the solution? As it fixed the whole scenario but not the specific problem. – M. Montoya Feb 20 '19 at 18:46
  • Why would anyone use this roundabout way in python to access mysql? I would simply delete this question. – Shadow Feb 20 '19 at 22:30

1 Answers1

1

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.

M. Montoya
  • 13
  • 4