DELIMITER/在 SQLAlchemy 中创建触发器

我需要创建一个 BEFORE INSERT 触发器在 SQLAlchemy 中:

DELIMITER |CREATE TRIGGER set_rank BEFORE INSERT ON 作者对于每一行开始如果 NEW.rank = 0 那么SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1来自作者作为WHERE a.id = NEW.pub_id);万一;完 |分隔符；

在 mysql-workbench 中执行代码工作正常，但在我的实际代码中执行时呈现您的 SQL 语法有错误"异常:

from sqlalchemy.sql.expression import text连接 = db.session.connection()文本(...，连接).执行()；

运行 SELECT SLEEP(1)、CREATE TABLE test (id INT) 甚至 USE some_db 都可以正常工作.

这是我得到的 mysql 错误信息:

<块引用>

(1064, "你的 SQL 语法有错误；...靠近 'DELIMITER | CREATE TRIGGER set_rank BE...")

我很确定我错误地使用了 DELIMITER.在这种情况下，正确的语法是什么/如何解决我的问题?

解决方案

老实说，您真的不需要 DELIMITER.那仅适用于命令行客户端.作为程序员，您将分割语句，因此分隔符将被忽略.

<预><代码>>>>从 sqlalchemy 导入 *>>>>>>trigger_text = """... 在插入作者之前创建触发器 set_rank... 对于每一行开始...如果 NEW.rank = 0 那么... SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1...来自作者作为... WHERE a.id = NEW.pub_id);... 万一;... 结尾……">>>>>>元数据 = 元数据()>>>作者 = 表(作者"，元数据，... Column("id", Integer, primary_key=True),...列(排名"，整数)，...列(pub_id"，整数))>>>>>>engine = create_engine("mysql://root@127.0.0.1/test", echo=True)>>>author.create(引擎)2012-05-04 17:11:41,093 信息 sqlalchemy.engine.base.Engine创建表作者(id INTEGER NOT NULL AUTO_INCREMENT,排名整数，pub_id 整数，主键 (id))2012-05-04 17:11:41,093 信息 sqlalchemy.engine.base.Engine ()2012-05-04 17:10:51,376 信息 sqlalchemy.engine.base.Engine COMMIT>>>engine.execute(trigger_text)2012-05-04 17:11:41,159 信息 sqlalchemy.engine.base.EngineCREATE TRIGGER set_rank BEFORE INSERT ON 作者对于每一行开始如果 NEW.rank = 0 那么SET NEW.rank = (SELECT IFNULL(MAX(a.rank),0) + 1来自作者作为WHERE a.id = NEW.pub_id);万一;结尾2012-05-04 17:11:41,159 信息 sqlalchemy.engine.base.Engine ()2012-05-04 17:11:41,312 信息 sqlalchemy.engine.base.Engine COMMIT<sqlalchemy.engine.base.ResultProxy 对象在 0x2be1ed0>>>>

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 ;

Executing the code in mysql-workbench works just fine, but renders a "You have an error in your SQL syntax" exception when executed in my actual code:

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 just fine.

This the the mysql error message I get:

(1064, "You have an error in your SQL syntax; ... near 'DELIMITER | CREATE TRIGGER set_rank BE...")

I am quite sure that I use the DELIMITER incorrectly. What is the right syntax in this very context / How to fix my problem?

解决方案

You really honestly don't need the DELIMITER. Thats only for the command line client. You as a programmer will be dividing up statements, so the 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>
>>>