Question

这里向你们提出一个问题,即我和我一起来到这里。

为什么这部神秘的指挥系统在我穿过沙里时不会奏效,而是在我通过我方ql CLI执行的时候?


#!/usr/bin/env python

import oursql as mysql
import sys, traceback as tb
import logging

# some other stuff...

class MySqlAuth(object):
    def __init__(self, host = None, db = None, user = None, pw = None, port = None):
        self.host =  localhost  if host is None else host
        self.db =  mysql  if db is None else db
        self.user =  root  if user is None else user
        self.pw = pw
        self.port = 3306 if port is None else port

    @property
    def cursor(self):
        auth_dict = dict()
        auth_dict[ host ] = self.host
        auth_dict[ user ] = self.user
        auth_dict[ passwd ] = self.pw
        auth_dict[ db ] = self.db
        auth_dict[ port ] = self.port
        conn = mysql.connect(**auth_dict)
        cur = conn.cursor(mysql.DictCursor)
        return cur

def ExecuteNonQuery(auth, sql):
    try:
        cur = auth.cursor
        log.debug( SQL:    + sql)
        cur.execute(sql)
        cur.connection.commit()
        return cur.rowcount
    except:
        cur.connection.rollback()
        log.error("".join(tb.format_exception(*sys.exc_info())))
    finally:
        cur.connection.close()

def CreateTable(auth, table_name):

    CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS %(table)s ( 
              uid VARCHAR(120) PRIMARY KEY
            , k VARCHAR(1000) NOT NULL
            , v BLOB
            , create_ts TIMESTAMP NOT NULL
            , mod_ts TIMESTAMP NOT NULL
            , UNIQUE(k)
            , INDEX USING BTREE(k)
            , INDEX USING BTREE(mod_ts) );
    """
    ExecuteNonQuery(auth, CREATE_TABLE % {  table  : table_name })

    CREATE_BEFORE_INSERT_TRIGGER = """
    DELIMITER //
    CREATE TRIGGER %(table)s_before_insert BEFORE INSERT ON %(table)s
    FOR EACH ROW
    BEGIN
        SET NEW.create_ts = NOW();
        SET NEW.mod_ts = NOW();
        SET NEW.uid = UUID();
    END;// DELIMIETER ;
    """
    ExecuteNonQuery(auth, CREATE_BEFORE_INSERT_TRIGGER % {  table  : table_name })

    CREATE_BEFORE_INSERT_TRIGGER = """
    DELIMITER //
    CREATE TRIGGER %(table)s_before_update BEFORE UPDATE ON %(table)s
    FOR EACH ROW
    BEGIN
        SET NEW.mod_ts = NOW();
    END;// DELIMIETER ;
    """
    ExecuteNonQuery(auth, CREATE_BEFORE_UPDATE_TRIGGER % {  table  : table_name })

# some other stuff

我在座时的错误是:


2012-01-15 11:53:00,138 [4214 MainThread mynosql.py] DEBUG SQL:  
    DELIMITER //
    CREATE TRIGGER nosql_before_insert BEFORE INSERT ON nosql
    FOR EACH ROW
    BEGIN
        SET NEW.create_ts = NOW();
        SET NEW.mod_ts = NOW();
        SET NEW.uid = UUID();
    END;// DELIMIETER ;

2012-01-15 11:53:00,140 [4214 MainThread mynosql.py] ERROR Traceback (most recent call last):
  File "./mynosql.py", line 39, in ExecuteNonQuery
    cur.execute(sql)
  File "cursor.pyx", line 120, in oursql.Cursor.execute (oursqlx/oursql.c:15856)
  File "cursor.pyx", line 111, in oursql.execute (oursqlx/oursql.c:15728)
  File "statement.pyx", line 157, in oursql._Statement.prepare (oursqlx/oursql.c:7750)
  File "statement.pyx", line 127, in oursql._Statement._raise_error (oursqlx/oursql.c:7360)
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 //
    CREATE TRIGGER nosql_before_insert BEFORE INSERT ON nosql
    F  at line 1", None)

Answer 1

虽然你发现的错误似乎是由第1条<编码>DELIMITER /说明生成的,但你在最后提到<代码>时有了一个打字。 DELIMITER - 您写道:; - 试图改变这一状况,看这是否解决了你的问题。

<>Update>

您有2个“DELIMIETER”;——我认为,在口译员发现第1个错误之后,你正在发现错误:

 DELIMITER //
    CREATE TRIGGER %(table)s_before_insert BEFORE INSERT ON %(table)s
    FOR EACH ROW
    BEGIN
        SET NEW.create_ts = NOW();
        SET NEW.mod_ts = NOW();
        SET NEW.uid = UUID();
    END;// DELIMIETER ; <-- this one is wrong, it should be DELIMITER

Answer 2

你只能在某个时候向我的提问;问询要由客户来确保问询文本只是一份有效的陈述。

The MySQL client does this by tokenizing the entered query and looking for statement separators. In the case of a trigger definition, this doesn t work because the definition can contain semicolons (the default statement separator), and so you have to tell the cli to separate statements in another way, using the DELIMITER command.

MySQLdb (和其他) python apis 不需要这种发言分离;方案管理员有义务在某一时间通过发言以query/code>。

删除<代码>DELIMITER的发言,完全摘自您的询问(通过皮页)。

友情链接