MySQL Forums
Forum List  »  Connector/Python

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
October 18, 2011 01:14PM


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.