How Python Pymysql.cursors Get Inout Return Result From Mysql Stored Procedure

I have mysql proc: CREATE DEFINER=`user`@`localhost` PROCEDURE `mysproc`(INOUT par_a INT(10), IN par_b VARCHAR(255) , IN par_c VARCHAR(255), IN par_etc VARCHAR(255)) BEGIN

Solution 1:

I used this class and I got response.

import pymysql.cursors

class connMySql:
        def __init__(self, User, Pass, DB, Host='localhost', connShowErr=False, connAutoClose=True):
                self.ShowErr = connShowErr
                self.AutoClose = connAutoClose
                self.DBName = DB
                try:
                        self.connection = pymysql.connect(host=Host,
                             user=User,
                             password=Pass,
                             db=DB, #charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False

        def Fetch(self, Query):
                try:
                        with self.connection.cursor() as cursor:
                                # Read a single record
                                cursor.execute(Query)
                                result = cursor.fetchall()
                        return result
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False
                finally:
                        if self.AutoClose == True: self.connection.close()

        def Insert(self, Query):
                try:
                        with self.connection.cursor() as cursor:
                                # Create a new record
                                cursor.execute(Query)
                        # connection is not autocommit by default. So you must commit to save
                        # your changes.
                        self.connection.commit()
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False
                finally:
                        if self.AutoClose == True: self.connection.close()

        def ProcedureExist(self, ProcedureName):
                try:
                        result = self.Fetch("SELECT * FROM mysql.proc WHERE db = \"" + str(self.DBName) + "\";")
                        Result = []
                        for item in result:
                                Result.append(item['name'])
                        if ProcedureName in Result:
                                return True
                        else:
                                return False
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False

        def CallProcedure(ProcedureName, Arguments=""):
                try:
            # Set arguments as a string value
                        result = self.Fetch('CALL ' + ProcedureName + '(' + Arguments + ')')
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False
                finally:
                        if self.AutoClose == True: self.connection.close()

        def CloseConnection(self):
                try:
                        self.connection.close()
                        return True
                except ValueError as ValErr:
                        if self.ShowErr == True: print(ValErr)
                        return False

def main():
    objMysqlConn = connMySql('user', '1234', 'myDB', connShowErr=True, connAutoClose=False)
    ProcedureName= "mysproc"
    if objMysqlConn.ProcedureExist(ProcedureName):
            result = objMysqlConn.Fetch('CALL ' + ProcedureName + '()')
            if result != False:
                    result = result[0]
                    print(result)
    else:
            print("The procecure does not exist!")

if __name__ == '__main__':
    main()