DELIMITER /在SQLAlchemy中创建一个触发器 [英] DELIMITER / Creating a trigger in SQLAlchemy

问题描述

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

  DELIMITER | 
 CREATE TRIGGER set_rank BEFORE INSERT ON author 
 FOR EACH 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; 
  

在mysql-workbench中执行代码工作得很好，但会呈现出您的错误SQL语法异常，当我在我的实际代码中执行：
$ b

from sqlalchemy.sql.expression import text

connection = db.session.connection（）
text（...，connection）.execute（）;


运行 SELECT SLEEP（1）， CREATE TABLE test（id INT）甚至 USE some_db 工作得很好

这是我得到的mysql错误消息：

lockquote

（1064，你的SQL语法错误。 ...'DELIMITER | \ nCREATE TRIGGER set_rank BE ...）

我很确定我错误地使用了DELIMITER 。如何解决我的问题？

解决方案

你真的不需要 DELIMITER 。那只适用于命令行客户端。你作为一个程序员将​​被分开的语句，所以分隔符被忽略。

 >>> from sqlalchemy import * 
>>> 
>>> trigger_text =
 ... CREATE TRIGGER set_rank之前插入作者
 ...为每行开始
 ...如果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（）
>>> （id，Integer，primary_key = True），
 ...列（rank，Integer），
 ...列（pub_id，Integer））
>>> 
>>> engine = create_engine（mysql：//root@127.0.0.1/test，echo = True）
>>> authors.create（引擎）
 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信息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作者
 FOR EACH ROW BEGIN 
 IF NEW.rank = 0 THEN 
 SET NEW.rank =（SELECT IFNULL（MAX（a.rank），0）+ 1 
 FROM作者AS 
 WHERE a.id = NEW .pub_id）; 
 END IF; 
 END 
 
 2012-05-04 17：11：41,159信息sqlalchemy.engine.base.Engine（）
 2012-05-04 17：11：41,312 INFO sqlalchemy。 engine.base.Engine COMMIT 
< sqlalchemy.engine.base.ResultProxy object at 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 |\nCREATE 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>
>>> 

这篇关于DELIMITER /在SQLAlchemy中创建一个触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！