DELIMITER / Create a trigger in SQLAlchemy

Question

I need to create a BEFORE INSERT trigger in SQLAlchemy:

DELIMITER | CREATE TRIGGER set_rank BEFORE INSERT ON authors FOR EACH ROW BEGIN IF NEW.rank = 0 THEN SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1 FROM authors AS a WHERE a.id = NEW.pub_id); END IF; END | DELIMITER ; 

Code execution in mysql-workbench works fine, but when executed in my actual code, it throws the exception "You have an error in the SQL syntax".

 from sqlalchemy.sql.expression import text connection = db.session.connection() text(..., connection).execute(); 

Running SELECT SLEEP(1) , CREATE TABLE test (id INT) or even USE some_db works fine.

This is the mysql error message I get:

(1064, "You have an error in the SQL syntax ... next to" DELIMITER | \ nCREATE TRIGGER set_rank BE ... ")

I am sure that I am using DELIMITER incorrectly. What is the correct syntax in this context / How to fix my problem?

Answer 1

You really honestly don't need a DELIMITER . This is for the command line client only. You as a programmer will share statements, so delimiters are otherwise ignored.

 >>> from sqlalchemy import * >>> >>> trigger_text = """ ... CREATE TRIGGER set_rank BEFORE INSERT ON authors ... FOR EACH ROW BEGIN ... IF NEW.rank = 0 THEN ... SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1 ... FROM authors AS a ... WHERE a.id = NEW.pub_id); ... END IF; ... END ... """ >>> >>> metadata = MetaData() >>> authors = Table("authors", metadata, ... Column("id", Integer, primary_key=True), ... Column("rank", Integer), ... Column("pub_id", Integer)) >>> >>> engine = create_engine("mysql:// root@127.0.0.1 /test", echo=True) >>> authors.create(engine) 2012-05-04 17:11:41,093 INFO sqlalchemy.engine.base.Engine CREATE TABLE authors ( id INTEGER NOT NULL AUTO_INCREMENT, rank INTEGER, pub_id INTEGER, PRIMARY KEY (id) ) 2012-05-04 17:11:41,093 INFO sqlalchemy.engine.base.Engine () 2012-05-04 17:10:51,376 INFO sqlalchemy.engine.base.Engine COMMIT >>> engine.execute(trigger_text) 2012-05-04 17:11:41,159 INFO sqlalchemy.engine.base.Engine CREATE TRIGGER set_rank BEFORE INSERT ON authors FOR EACH ROW BEGIN IF NEW.rank = 0 THEN SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1 FROM authors AS a WHERE a.id = NEW.pub_id); END IF; END 2012-05-04 17:11:41,159 INFO sqlalchemy.engine.base.Engine () 2012-05-04 17:11:41,312 INFO sqlalchemy.engine.base.Engine COMMIT <sqlalchemy.engine.base.ResultProxy object at 0x2be1ed0> >>>