still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

I think the subject line makes it pritty clear.
I want to know how i can use the expression api to make calls to
postgresql stored procedures written in plpgsql.
For example how to pass input parameters and how to manipulate cursor
objects etc.
happy hacking.
Krishnakant.

[Michael Bayer]

You use the "func" construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT:

from sqlalchemy import func

result = engine.execute(func.name_of_my_pg_function(1, 2, 3))

Manipulation of cursors is not supported by SQLAlchemy beyond calling the basic fetchone()/fetchmany()/fetchall() methods of DBAPI. If you need non-standard cursor control methods like "scroll()", you can no longer use engine.execute() and need to use psycopg2 cursors directly:

http://initd.org/psycopg/docs/cursor.html

To get at a psycopg2 cursor from a SQLAlchemy engine:

connection = engine.raw_connection()
cursor = connection.cursor()

Usage is then that described at http://initd.org/psycopg/docs/cursor.html

cursor.execute("SELECT my_pg_function(%(param1)s, %(param2)s, %(param3)s)", {'param1':1, 'param2':2, 'param3':3})

hope this helps !

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Krishnakant Mane]

On 12/09/11 00:56, Michael Bayer wrote:
> You use the "func" construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT:
>
> from sqlalchemy import func
>
> result = engine.execute(func.name_of_my_pg_function(1, 2, 3))

> So does it mean that name_of_my_pg_function is should be the name of the concerned stored procedure?
And let's say if I am using an ide for Python like pydev with eclipse,
will func. give me list of those procedures which are available for calling?
happy hacking.
Krishnakant.

[Michael Bayer]

On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote:

> On 12/09/11 00:56, Michael Bayer wrote:
>> You use the "func" construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT:
>>
>> from sqlalchemy import func
>>
>> result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
>> So does it mean that name_of_my_pg_function is should be the name of the concerned stored procedure?
> And let's say if I am using an ide for Python like pydev with eclipse, will func. give me list of those procedures which are available for calling?
> happy hacking.
> Krishnakant.

no func. doesn't do any kind of inspection of existing database functions, sorry.

[Krishnakant Mane]

On 12/09/11 03:09, Michael Bayer wrote:
> On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote:
>
>> On 12/09/11 00:56, Michael Bayer wrote:
>>> You use the "func" construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT:
>>>
>>> from sqlalchemy import func
>>>
>>> result = engine.execute(func.name_of_my_pg_function(1, 2, 3))

Can you please give a complete example.
There are two balbonising problems here.
I use an ide called eclipse with pydev.
I don't get any code completion when I write the above code.
Secondly, do I use execute on engine or do i first do engine.connect()
and then execute a func.name(param) through the connection?
When I try to use result as result[1] I get column not found.
Happy hacking.
Krishnakant.

[Michael Bayer]

On Oct 15, 2011, at 7:52 AM, Krishnakant Mane wrote:

>
> On 12/09/11 03:09, Michael Bayer wrote:
>> On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote:
>>
>>> On 12/09/11 00:56, Michael Bayer wrote:
>>>> You use the "func" construct to invoke a function. This can be passed to an execute() method directly where it should embed itself into a SELECT:
>>>>
>>>> from sqlalchemy import func
>>>>
>>>> result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
>
>
>
> Can you please give a complete example.

one concrete example, coming up. this function is a widely used "distance calculation" function that can be found all over google. the last line is the execute.

from sqlalchemy import func, create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
engine.execute(
"""
CREATE OR REPLACE FUNCTION calc_distance(lat_1 float, lon_1 float, lat_2 float, lon_2 float) RETURNS float AS $$
BEGIN
RETURN 3963.0 * acos(sin(lat_1 / 57.2958) * sin(lat_2 / 57.2958) + cos(lat_1 / 57.2958) * cos(lat_2 / 57.2958) * cos((lon_2 / 57.2958) - (lon_1 / 57.2958)));
END;
$$ LANGUAGE plpgsql;
""")

print "Sample distance:", engine.execute(func.calc_distance(32.9697, -96.80322, 29.46786, -98.53506)).scalar()

[Krishnakant Mane]

Thanks michael,
But my problem here is different.

Let me give you the exact stored procedure that we have here for execution.

create or replace function getGroupByCode(group_code
groups.groupcode%type) returns setof groups as $$
declare
res groups;
begin
for res in select * from groups where groupcode = group_code loop
return next res;
end loop;
return;
end;
$$ language plpgsql;

Now I will give you the code I am trying to run.

from sqlalchemy.engine import create_engine
from sqlalchemy import func

engine =
create_engine("postgresql://gnukhata:gnukhata@localhost/K2011101512425529")

print type(engine)

res = engine.execute(func.getGroupByCode(1)).scalar()

print type(res)

for row in res:
print row['groupname']

Note that the stored procedure refers to a group table that contains
groupcode,groupname,groupdesc
So groupname is a valid column.
Yet I get the no such column error.
Can you please explain?

Happy hacking.
Krishnakant.

[Michael Bayer]

On Oct 15, 2011, at 10:23 AM, Krishnakant Mane wrote:

> create or replace function getGroupByCode(group_code groups.groupcode%type) returns setof groups as $$
> declare
> res groups;
> begin
> for res in select * from groups where groupcode = group_code loop
> return next res;
> end loop;
> return;
> end;
> $$ language plpgsql;
>
>
> Now I will give you the code I am trying to run.
>
>
> from sqlalchemy.engine import create_engine
> from sqlalchemy import func
>
> engine = create_engine("postgresql://gnukhata:gnukhata@localhost/K2011101512425529")
>
> print type(engine)
>
>
> res = engine.execute(func.getGroupByCode(1)).scalar()
>
>
> print type(res)
>
> for row in res:
> print row['groupname']
>
> Note that the stored procedure refers to a group table that contains groupcode,groupname,groupdesc
> So groupname is a valid column.
> Yet I get the no such column error.
> Can you please explain?

that sounds like an issue in the procedure itself, such as running it on the wrong database, or the wrong "groups" table otherwise. there could be many schemas/databases that contain a "groups" table. Get the procedure to work with psql first using the identical login information.

[Krishnakant Mane]

On 15/10/11 22:17, Michael Bayer wrote:

> that sounds like an issue in the procedure itself, such as running it on the wrong database, or the wrong "groups" table otherwise. there could be many schemas/databases that contain a "groups" table. Get the procedure to work with psql first using the identical login information.
>
>
>

I got the result but without using func.
when I did res = engine.execute("select * from getGroupByCode(1)) I got
the results.
But func.execute still seems to have a problem.
happy hacking.
Krishnakant.

[Michael Bayer]

should make no difference, but your exact SQL:

from sqlalchemy import func, select
engine.execute(select(['*']).select_from(func.getGroupByCode(1))).scalar()

[naufal....@gmail.com]

What if you wanted to execute a Stored Procedure but from SQL Server instead of PostgreSQL? Will the same logic work?

[Mike Bayer]

SQL server may require the use of cursor.callproc() to execute a stores procedure, see the documentation section on "raw cursor access" for that.  In that case you are better off just executing the textual SQL directly.   Though if the function works inside of a SELECT then sure the same use as with postgresql should work.

On Feb 5, 2018 7:08 AM, <naufal....@gmail.com> wrote:

What if you wanted to execute a Stored Procedure but from SQL Server instead of PostgreSQL? Will the same logic work?

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.