Support for calling Stored Procedures (not functions)

[Ronny Pfannschmidt]

oracle stored procedures cant be invoked by a select, instead one must call them in a anonymous block

a way to either access those via the dbapi.callproc method or generating the fit sql would be nice

currently i work around by compiling to a block on oracle and a select on postgresql i didnt examine other databases

[Mike Bayer]

I might want to start at a "recipe" level for this based on @compiles as there's a lot of unknowns to how a full API would look. Oracle and DB2 in particular have OUT params, which we support, but what do we want to do with those here, there's additionally other things that have to occur on say SQL Server, there may or may not be the requirement that cursor.callproc() is used instead of cursor.execute(), etc.

so for the time being a recipe in examples/ might be best. that way, users take responsibility for hosting that code and they can tailor it as they see fit.

Of course, if we want to add some extra structures to make it easier to call cursor.callproc(), those are easy to add in order to support the recipes we're putting out (though I don't have an idea at the moment where the best place to hook into callproc() might be).

examples that become very widely used and start to become more complicated than they're worth ultimately get rolled into features once we understand them well.

right now you can get to cursor.callproc() by getting the cursor off of connection.connection.cursor().

[Mike Bayer]

OK so first here's a patch that is the absolute minimal amount of hooks to allow a compiled element to call into cursor.callproc():

diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index 888a15f..7efbb1a 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -901,7 +901,14 @@ class Connection(Connectable):
             self.engine.logger.info("%r",
                     sql_util._repr_params(parameters, batches=10))
         try:
-            if context.executemany:
+            if context.callproc:
+                self.dialect.do_callproc(
+                                    cursor,
+                                    statement,
+                                    parameters,
+                                    context
+                                )
+            elif context.executemany:
                 self.dialect.do_executemany(
                                     cursor,
                                     statement,
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index ed975b8..af124ef 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -424,6 +424,9 @@ class DefaultDialect(interfaces.Dialect):
     def do_execute(self, cursor, statement, parameters, context=None):
         cursor.execute(statement, parameters)

+    def do_callproc(self, cursor, statement, parameters, context=None):
+        cursor.callproc(statement, parameters)
+
     def do_execute_no_params(self, cursor, statement, context=None):
         cursor.execute(statement)

@@ -443,6 +446,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
     isdelete = False
     isddl = False
     executemany = False
+    callproc = False
     result_map = None
     compiled = None
     statement = None
@@ -526,6 +530,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
         self.isinsert = compiled.isinsert
         self.isupdate = compiled.isupdate
         self.isdelete = compiled.isdelete
+        self.callproc = compiled.iscallproc

         if self.isinsert or self.isupdate or self.isdelete:
             self._is_explicit_returning = bool(compiled.statement._returning)
@@ -554,7 +559,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
         # into a dict or list to be sent to the DBAPI's
         # execute() or executemany() method.
         parameters = []
-        if dialect.positional:
+        if self.callproc or dialect.positional:
             for compiled_params in self.compiled_parameters:
                 param = []
                 for key in self.compiled.positiontup:
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 5165ee7..6e071a0 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -305,6 +305,14 @@ class SQLCompiler(Compiled):
     INSERT/UPDATE/DELETE
     """

+    iscallproc = False
+    """A flag which indicates to the dialect that the
+    DBAPI callproc() method may be appropriate for this statement.
+
+    .. versionadded:: 0.9.4
+
+    """
+
     returning = None
     """holds the "returning" collection of columns if
     the statement is CRUD and defines returning columns

next, here is a Postgresql test case with a new element StoredProc. This is a pretty quick and dirty element, and for inclusion we'd have to enhance it to support out parameters (note that we have this, using the outparam() construct), as well as figure out how it will work for a stored procedure that returns a result set (I couldn't figure out how to do that). Also, stored procs often return multiple result sets - so the feature isn't complete until we also do #1635, which is a big deal (because while its easy to just access nextset(), SQLAlchemy Core includes Python-side type processing, and that's the hard part). There'd need to be tests for all of it.

from sqlalchemy import create_engine, Integer, bindparam
from sqlalchemy.ext.compiler import compiles
from sqlalchemy import types as sqltypes
from sqlalchemy.sql.expression import Executable, ClauseElement

class StoredProc(Executable, ClauseElement):
    def __init__(self, name, parameters, returntype):
        self.name = name
        self.parameters = [
            bindparam('p%d' % i, value)
            for i, value in enumerate(parameters)
        ]
        self.returntype = sqltypes.to_instance(returntype)


@compiles(StoredProc, "postgresql")
def stored_proc(element, compiler, **kw):
    compiler.iscallproc = True
    compiler.positional = True
    compiler.positiontup = []
    for param in element.parameters:
        compiler.process(param, **kw)
    compiler.result_map[element.name] = (element.name, (), element.returntype)
    return element.name

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
conn = engine.connect()
trans = conn.begin()

conn.execute("""
CREATE FUNCTION add(x integer, y integer) RETURNS integer AS $$
BEGIN
    RETURN x + y;
END;
$$ LANGUAGE plpgsql;

""")

result = conn.execute(StoredProc("add", (3, 4), Integer))
print(result.first()[0])

now here's the good news. I can easily commit just the patch that's above, add a few tests for it, and that part is done. The example that follows, I can add to examples/, with caveats that the feature is not fleshed out yet, here's a recipe to get you started.

Or, we could forego all of this for now, and instead, if you really need to call callproc(), just do this:

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
conn = engine.connect()
trans = conn.begin()

conn.execute("""
CREATE FUNCTION add(x integer, y integer) RETURNS integer AS $$
BEGIN
    RETURN x + y;
END;
$$ LANGUAGE plpgsql;

""")

cursor = conn.connection.cursor()
cursor.callproc("add", (3, 4))
print(cursor.fetchall())

similarly to #1635, this kind of feature is hard to do because it isn't that useful vs. just a raw cursor call, unless you really flesh out everything, the parameter handling, the result handling, multiple result sets, etc., so that it's a super-nice and foolproof feature. Otherwise just calling cursor.callproc() on those occasions you need to call a proc is usually easier than working around an only partial API.

[CharlieC]

@zzzeek re. returning a result set in Postgres, I've just learned about this (returns setof) and am, therefore, keen on using it in SQLa. In certain situations, such as where you have an unknown list of fields, you can't return a result set, instead you have to return a cursor which you can then iterate over. NB. in this case you have to create another cursor using the name returned. FWIW here's an example. I agree that this can be largely done with the underlying driver, but automating some of the conversion to SQLa objects would be nice.

You can see a pretty hamfisted attempt to do this for a stored procedure I cooked up in MySQL and which I'm currently porting to Postgres, see below.

CREATE or REPLACE FUNCTION somefunc(refcursor) RETURNS refcursor AS $$
    DECLARE
        rowid text := 'labeldate date, ';
        stmt text;

    BEGIN
    SELECT string_agg(DISTINCT quote_ident(cdn), ' int, ' ORDER BY quote_ident(cdn)) INTO stmt FROM cdns;
    stmt := rowid ||  stmt || ' int';

    stmt := format(
        'SELECT * FROM crosstab(%L, %L) AS (%s);',
        format('select labeldate, cdn, sites
from cdns
where slice = %L', 
'Top1000')
,
'SELECT DISTINCT cdn FROM cdns ORDER by cdn',
stmt
            );
        open $1 for execute stmt;
        RETURN $1;
    END;

$$ LANGUAGE plpgsql;

from psycop2 import connect
db = connect(database=…, user=…)
c1 = db.cursor()
c1.callproc('somefunc', ('my_cursor',))
c2 = db.cursor('my_cursor')
for row in c2:
   …

[Mike Bayer]

OK, yeah I see you have a nested cursor there of sorts. All good stuff but this is way off my radar as far as coming up with an API for it :). someone would have to pull the weight here.

[CharlieC]

Fair enough. I thought it my be useful for generic pivot functions. Nearly there in SQLa + psycopg2, just can't work out how to get the field names c2.description is returning None but the information must be around as it's there in psql. `c1.execute("SELECT somefunc('mycursor')") would be the more generic approach.

[CharlieC]

For posterity I've updated my app to be able to use either Postgres or MySQL. Postgres only has functions which can be called from a session's .execute method. With MySQL you've got to get the cursor first in order to be able to work with the result set (you'll get an error asking for multi=True to be passed in but that won't help you here anyway). One more note, with Postgres you don't get the description until you've got your first row.

But seeing as how fiddly this can be, it's no wonder people use Pandas for this kind of thing.

[Mike Bayer]

Issue #3103 was marked as a duplicate of this issue.

[Mike Bayer]

this one is lots and lots of work and complexity for really little gain, as we rarely have this request and also those who really need to use callproc() in a specific way to get something done can just use the documented approach for direct DBAPI access. it's not like i don't want to have this, but it's hard to see the work being done here.

[CharlieC]

@zzzeek Thanks for the update. My solution does currently use direct DBAPI access which works fine even if it feels a little dirty. May also look at Hannu's solution for proxying server code.

[Boris Z]

Same problem with MSSQL. The underlying pyodbc engine has no problem calling them. Wrapping calls in "anonymous block" does not help. Sample procedure:

CREATE PROCEDURE list_lock_set @name varchar (5), @requester varchar(30)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO list_lock (name, requester, acquired) values (@name, @requester, GETDATE())
    RETURN 0    
END
GO

This works:

import pyodbc
dbh = pyodbc.connect(driver=''{SQL Server}'', server=srv, database=db, uid=uid, pwd=pwd)
dbc = dbh.cursor()
dbc.execute("list_lock_set ?, ?", ['bbc', 'pyodbc'])
dbc.commit()

This does not produce an error but also but does not work:

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQL Server', echo=True)
engine.execute("list_lock_set ?, ?", ['bbc', 'sqlalchemy'])

Output: 2016-11-04 16:11:13,445 INFO sqlalchemy.engine.base.Engine list_lock_set ?, ? 2016-11-04 16:11:13,446 INFO sqlalchemy.engine.base.Engine ('bbc', 'sqlalchemy')

I will have to open a separate pyodbc connection to call stored procedures for now. If it is possible to obtain pyodbc cursor from engine it would make things easier.

[Mike Bayer]

I will have to open a separate pyodbc connection to call stored procedures for now. If it is possible to obtain pyodbc cursor from engine it would make things easier.

as documented in the previous comment, cursor access is readily available, see http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#calling-stored-procedures