Creating Trigger Using Connector/Python - MySQL

Question

This Code work Properly in MYSQLWorkbench .

use MYDB
drop trigger if exists mytrigger
delimiter //
CREATE TRIGGER mytrigger BEFORE INSERT ON  MYTABLE FOR EACH ROW 
BEGIN
  IF (select COUNT(*) from MYTABLE) = 12 THEN 
    SET NEW.COL2 = 10;
  END IF;
END;//
delimiter ;

MYTABLE has two column as (COL1,COL2)

I Want to create this trigger via python so i write this code in python:

import mysql.connector

cnx = mysql.connector.connect(user='root', password='root', host='127.0.0.1', database='mysql')
cursor = cnx.cursor()
DB_NAME = 'MYDB'
Table_Name= 'MYTABLE'
cursor.execute("CREATE DATABASE IF NOT EXISTS " + str(DB_NAME))
cnx.commit()
cursor.execute("USE " + str(DB_NAME))
cnx.commit()
cursor.execute("CREATE TABLE IF NOT EXISTS " + str(Table_Name) + " ("
               "  `COL1` BIGINT(20) NOT NULL ,"
               "  `COL2` varchar(20) NOT NULL,"
               "  PRIMARY KEY (`COL1`)"
                       ") ENGINE=InnoDB")
cnx.commit()
cursor.execute(" drop trigger if exists mytrigger")
qrystr = ("  delimiter // \n"
          "  CREATE TRIGGER mytrigger BEFORE INSERT ON  MYTABLE FOR EACH ROW \n"
          "  BEGIN \n"
          "   IF (select COUNT(*) from MYTABLE) = 12 THEN \n"
          "      SET NEW.COL2 = 10;\n"
          "   END IF;\n"
          "  END;//\n"
          "  delimiter ; \n")
cursor.execute(qrystr)
cnx.commit()
cnx.close()

BUT This error occur :(((((((((((((((((((((((((((((((

Error: 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 mytrigger BEFORE INSERT ON MYTABLE FOR EACH ROW ' at line 1

please help

jshrimp29 · Accepted Answer · 2019-04-18 19:36:51Z

5

For anyone else coming along with this problem (or on the off-chance that this is still an issue for the asker nearly 11 months later):

DELIMITER is a feature only of the CLI (not the server itself), so the various connectors won't know what to do with it. I ended up solving my same problem by removing the DELIMITER statement and anything that relied on it and moving the entire trigger statement onto one line to execute at once. Because it's all in one execution, the connector determines that the semicolons don't end the statement.

cursor.execute(" drop trigger if exists mytrigger")

## NEW CODE ##
qrystr = "CREATE TRIGGER mytrigger BEFORE INSERT ON MYTABLE FOR EACH ROW BEGIN IF (select COUNT(*) from MYTABLE) = 12 THEN SET NEW.COL2 = 10; END IF; END"
##############

cursor.execute(qrystr)
cnx.commit()
cnx.close()

answered Apr 18, 2019 at 19:36

jshrimp29

6537 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating Trigger Using Connector/Python - MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related