CODEWITHSUNDEEP
pythonmysqlstored-procedurespymysqlstored-functions
pulkit
pulkit

Reputation: 87

How can i create Stored procedure in mysql from python?

I am trying to use pymysql to create a stored procedure in mysql database. I have a file which contains the statements to create stored procedure. I am running the mysql queries line by line using cursor.execure(line). The Queries to create stored procedure runs fine on the db. But on python they are giving syntax error.

DELIMITER $$

USE `ipaybridge2`$$

DROP PROCEDURE IF EXISTS `masking`$$

CREATE DEFINER=`root`@`%` PROCEDURE `masking`()
BEGIN
 
 query statements;

END$$

DELIMITER ;

the error i get is this: You have an error in your SQL syntax; it seems the error is around: 'DELIMITER $ $ USE `ipaybridge2` $ $ DROP PROCEDURE IF EXISTS `masking` $ $ CREAT' at line 1

Upvotes: 3

Views: 2946

Answers (1)

Booboo
Booboo

Reputation: 44108

pymsysql does not want the DELIMITER statements. So you should already be connected to a specific database and thus the USE statement should not be required. Just pass the following to cursor.execute as a separate command:

DROP PROCEDURE IF EXISTS `masking`

And then pass to cursor.execute as the next command:

CREATE DEFINER=`root`@`%` PROCEDURE `masking`()
BEGIN
 
 query statements;

END

See Error when trying to create stored procedure

Upvotes: 3

Related Questions