Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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, 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
On Oct 15, 2011, at 3:24 PM, Krishnakant Mane wrote:
> 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.
should make no difference, but your exact SQL:
from sqlalchemy import func, select
engine.execute(select(['*']).select_from(func.getGroupByCode(1))).scalar()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
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()
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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.
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 !
On Sep 11, 2011, at 1:48 PM, Krishnakant Mane wrote:
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy
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 !
On Sep 11, 2011, at 1:48 PM, Krishnakant Mane wrote:
> 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.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
