Using variables in Stored procedures with Python and MySQL

Question

How would I setup a stored procedure in MySQL to use external variables. I have written SP before, but not with extenal inputs. With connection string coming from pyODBC.

Then, using python how would I call that Sp and input that variable?

cursor.execute('Call d.MySP' ????)

Answer 1

Assuming you are using MySQLdb, according to the docs, the call would be:

cursor.callproc('d.MySP',args)

A peek under the hood shows how the variables are set and the CALL statement is made:

def callproc(self, procname, args=()):
 from types import UnicodeType
 db = self._get_db()
 charset = db.character_set_name()
 for index, arg in enumerate(args):
 q = "SET @_%s_%d=%s" % (procname, index,
 db.literal(arg))
 if isinstance(q, unicode):
 q = q.encode(charset)
 self._query(q)
 self.nextset()
 q = "CALL %s(%s)" % (procname,
 ','.join(['@_%s_%d' % (procname, i)
 for i in range(len(args))]))
 if type(q) is UnicodeType:
 q = q.encode(charset)
 self._query(q)
 self._executed = q
 if not self._defer_warnings: self._warning_check()
 return args

• unutbu: The stored procedure can be defined with a call to cursor.execute.
• Merlin: t, but first, will your code with pyodbc? Then I always use cursor.execute('call d.MySp'). Have a section of code with multiple hits to Db using select's , insert's, UD. But they all use a " % variable" I am tring to move the sql code into the db but preserve the variable nature of the code. How could I do this....Above looks like it would work for python client side but what about server side. any help with server side SP that would take varibles.
• unutbu: It looks like pyodbc does not implement cursor.callproc. See code.google.com/p/pyodbc/wiki/StoredProcedures, python.org/peps/pep-0249.html.