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' ????)

unutbu · Accepted Answer · 2011-03-02 15:30:42Z

2

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

edited Mar 2, 2011 at 15:30

answered Mar 2, 2011 at 15:23

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

3 Comments

unutbu Over a year ago

The stored procedure can be defined with a call to cursor.execute.

Merlin Over a year ago

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 Over a year ago

It looks like pyodbc does not implement cursor.callproc. See code.google.com/p/pyodbc/wiki/StoredProcedures, python.org/peps/pep-0249.html.

Collectives™ on Stack Overflow

Using variables in Stored procedures with Python and MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related