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