Skip navigation links

MySQL Forums :: Connector/Python :: defining triggers through python/MySQLdb not working


Advanced Search

defining triggers through python/MySQLdb not working
Posted by: Arie Kachler ()
Date: October 18, 2011 01:14PM

Hello,
I have the following string:

>>> sql
"DELIMITER $$\nCREATE\nDEFINER='root'@'localhost'\nTRIGGER `Starling`.`request_node-beforeinsert`\nBEFORE INSERT ON `Starling`.`request_node`\nFOR EACH ROW\nBEGIN\n DECLARE `var-request_node_id` int(10) unsigned;\nDECLARE `var-is_active` enum('true','false');\nDECLARE `var-sequence` int(10) unsigned;\nDECLARE `var-parent_node_id` int(10) unsigned;\nDECLARE `var-type` enum('folder','template');\nDECLARE `var-content_manager_id` int(10) unsigned;\nDECLARE `var-created` datetime;\nDECLARE `var-modified` datetime;\nDECLARE `var-request_template_id` int(11) unsigned;\nDECLARE `var-request_folder_id` int(11) unsigned;\n DECLARE `var-_revision` BIGINT UNSIGNED;\n DECLARE revisionCursor CURSOR FOR SELECT `request_node_id`, `is_active`, `sequence`, `parent_node_id`, `type`, `content_manager_id`, `created`, `modified`, `request_template_id`, `request_folder_id` FROM `_revision_request_node` WHERE `_revision`=`var-_revision` LIMIT 1;\n \n IF NEW.`_revision` IS NULL THEN\n INSERT INTO `_revision_request_node` (`_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (NEW.`_revision_comment`, @auth_uid, NOW());\n\t SET NEW.`_revision` = LAST_INSERT_ID(); \n ELSE\n SET `var-_revision`=NEW.`_revision`;\n OPEN revisionCursor;\n FETCH revisionCursor INTO `var-request_node_id`, `var-is_active`, `var-sequence`, `var-parent_node_id`, `var-type`, `var-content_manager_id`, `var-created`, `var-modified`, `var-request_template_id`, `var-request_folder_id`;\n CLOSE revisionCursor;\n \n SET NEW.`request_node_id` = `var-request_node_id`, NEW.`is_active` = `var-is_active`, NEW.`sequence` = `var-sequence`, NEW.`parent_node_id` = `var-parent_node_id`, NEW.`type` = `var-type`, NEW.`content_manager_id` = `var-content_manager_id`, NEW.`created` = `var-created`, NEW.`modified` = `var-modified`, NEW.`request_template_id` = `var-request_template_id`, NEW.`request_folder_id` = `var-request_folder_id`;\n END IF;\n \n SET NEW.`_revision_comment` = NULL;\n END;$$\n"

if I print the string, of course, newlines are shown as newlines:

>>> print sql
DELIMITER $$
CREATE
DEFINER='root'@'localhost'
TRIGGER `Starling`.`request_node-beforeinsert`
BEFORE INSERT ON `Starling`.`request_node`
FOR EACH ROW
BEGIN
DECLARE `var-request_node_id` int(10) unsigned;
DECLARE `var-is_active` enum('true','false');
DECLARE `var-sequence` int(10) unsigned;
DECLARE `var-parent_node_id` int(10) unsigned;
DECLARE `var-type` enum('folder','template');
DECLARE `var-content_manager_id` int(10) unsigned;
DECLARE `var-created` datetime;
DECLARE `var-modified` datetime;
DECLARE `var-request_template_id` int(11) unsigned;
DECLARE `var-request_folder_id` int(11) unsigned;
DECLARE `var-_revision` BIGINT UNSIGNED;
DECLARE revisionCursor CURSOR FOR SELECT `request_node_id`, `is_active`, `sequence`, `parent_node_id`, `type`, `content_manager_id`, `created`, `modified`, `request_template_id`, `request_folder_id` FROM `_revision_request_node` WHERE `_revision`=`var-_revision` LIMIT 1;

IF NEW.`_revision` IS NULL THEN
INSERT INTO `_revision_request_node` (`_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (NEW.`_revision_comment`, @auth_uid, NOW());
SET NEW.`_revision` = LAST_INSERT_ID();
ELSE
SET `var-_revision`=NEW.`_revision`;
OPEN revisionCursor;
FETCH revisionCursor INTO `var-request_node_id`, `var-is_active`, `var-sequence`, `var-parent_node_id`, `var-type`, `var-content_manager_id`, `var-created`, `var-modified`, `var-request_template_id`, `var-request_folder_id`;
CLOSE revisionCursor;

SET NEW.`request_node_id` = `var-request_node_id`, NEW.`is_active` = `var-is_active`, NEW.`sequence` = `var-sequence`, NEW.`parent_node_id` = `var-parent_node_id`, NEW.`type` = `var-type`, NEW.`content_manager_id` = `var-content_manager_id`, NEW.`created` = `var-created`, NEW.`modified` = `var-modified`, NEW.`request_template_id` = `var-request_template_id`, NEW.`request_folder_id` = `var-request_folder_id`;
END IF;

SET NEW.`_revision_comment` = NULL;
END;$$

But when I execute this, it's not working. The error is very generic but I suspect is being caused by the embedded newlines:

>>> cursor.execute(sql)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "build/bdist.macosx-10.6-universal/egg/MySQLdb/cursors.py", line 174, in execute
File "build/bdist.macosx-10.6-universal/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_mysql_exceptions.ProgrammingError: (1064, "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 'DELIMITER $$\nCREATE\nDEFINER='root'@'localhost'\nTRIGGER `Starling`.`request_node-' at line 1")

I know the syntax of the trigger definition is correct.

Any ideas how to pass the string to cursor.execute() so that newlines are expanded as newlines and not as \n inside the string?

Thanks for any insight you can give me.

Arie Kachler

Options: ReplyQuote


Subject Written By Posted
defining triggers through python/MySQLdb not working Arie Kachler 10/18/2011 01:14PM
Re: defining triggers through python/MySQLdb not working kao liki 12/31/2011 03:18AM
Re: defining triggers through python/MySQLdb not working kao liki 12/31/2011 03:21AM
Re: defining triggers through python/MySQLdb not working thang van 01/28/2012 08:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.