CODEWITHSUNDEEP
pythonmysqlstored-procedures
user3525290
user3525290

Reputation: 1607

Python Stored Procedure Error

I am getting an error when I try to execute a stored procedure in python. I am using python 2.7 and mysql. The stored procedure works when I call it from mysql workbench or if i call from the command line.
Here is the code.

 class db_conn:
     def __init__(self,db_name):
        self.conn = mysql.connector.connect(user = 'test', password = 'test', host = 'localhost', database = 'test')
        self.cursor = self.conn.cursor()
        self.return_data = []


def select_procedure(self, proc):
    print(proc)
    self.cursor.callproc(proc,'')
    self.conn.commit()
    return cursor.stored_results()

I get the following error.

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 '' at line 1

Here is the stored procedure. Nothing much to it.

DELIMITER $$
CREATE DEFINER=`test`@`localhost` PROCEDURE `get_info`()
BEGIN
    Select  id, fname,lname
    From    name_table
    where   display = 0 limit 1;
END

Upvotes: 1

Views: 887

Answers (2)

user3525290
user3525290

Reputation: 1607

I figured it out. The issue is python's callproc is expecting a tuple. So I added a variable to my stored procedure. 

CREATE DEFINER=`test`@`localhost` PROCEDURE `get_info`(id int)

Then added a tuple of nothing to.

self.cursor.callproc(proc,'')

Upvotes: 0

Jacques Amar
Jacques Amar

Reputation: 1833

You are missing closing info for the SP:

DELIMITER $$
CREATE DEFINER=`test`@`localhost` PROCEDURE `get_info`()
BEGIN
    Select  id, fname,lname
    From    name_table
    where   display = 0 limit 1;
END$$

DELIMITER ;

Upvotes: 1

Related Questions