Issues

Issue #2972 new

Support for calling Stored Procedures (not functions)

Ronny Pfannschmidt
created an issue

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

Comments (13)

  1. Mike Bayer repo owner

    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().

  2. Mike Bayer repo owner

    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.

  3. Charlie Clark

    Mike Bayer 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:
       
    
  4. Mike Bayer repo owner

    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.

  5. Charlie Clark

    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.

  6. Charlie Clark

    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.

  7. Log in to comment