Here's another one that is without the SQL expression stuff, perhaps it is
easier to understand:
def execproc(procname, engine, queryParams=[]):
conn = engine.connect()
try:
trans = conn.begin()
result = conn.execute(
"SELECT * FROM %s(%s)" % (
procname,
", ".join("%s" for arg in queryParams),
), queryParams
)
trans.commit()
return list(result)
finally:
conn.close()
still another, using the DBAPI directly. This uses only psycopg2 and the
Python standard library:
import psycopg2
import collections
def execproc(procname, queryParams=[]):
conn = psycopg2.connect(user="scott", password="tiger",
host="localhost", database="test")
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM %s(%s)" % (
procname,
", ".join("%s" for arg in queryParams),
), queryParams
)
conn.commit()
result = list(cursor)
conn.close()
tup = collections.namedtuple("row", [d[0] for d in cursor.description])
return [tup(*row) for row in result]
All three versions of this will execute any psycopg2 stored procedure,
including ones that INSERT, UPDATE, or DELETE and return the results as a named
result set. The result rows here are not quite the same thing as "out"
parameters which are an Oracle concept but fortunately this is much easier to
do than Oracle "out" parameters.
On Feb 12, 2012, at 12:31 PM, Krishnakant Mane wrote:
> Hi again,
> Mikeal, I am sorry, I was half asleep when I went through the code,
> This week long sprint on our project has tired me out.
> I think its pritty clear now.
> (unless you would love to explain the last 2 llines for clearity sake ).
> Happy hacking.
> Krishnakant.
>
> On 12/02/12 22:01, Michael Bayer wrote:
>> On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:
>>
>>> On 11/02/12 21:10, Michael Bayer wrote:
>>>> def execproc(procname, engine, queryParams=[]):
>>>> function = getattr(func, procname)
>>>> function_with_params = function(*queryParams)
>>>> return
>>>> engine.execute(function_with_params.execution_options(autocommit=True))
>>>>
>>>
>>> Hi Michael,
>>> I tryed this code with postgresql (psycopg2 ).
>>>
>>> I have a group table with the fields.
>>> groupcode integer, groupname text and groupdesc text.
>>>
>>> When I pass in a procedure name called get groups with all the above listed
>>> columns as output parameters (actually the procedure returns setof rows), I
>>> get the could not locate column on the groupcode field.
>>
>> OK sorry, you want individual columns from the function which means "select
>> *" from it. Here is that, using a function from postgresql's website:
>>
>> from sqlalchemy import create_engine, func, select, literal_column
>> engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
>>
>> engine.execute("""
>> CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
>> AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
>> LANGUAGE SQL;
>> """)
>> def execproc(procname, engine, queryParams=[]):
>> function = getattr(func, procname)
>> function_with_params = function(*queryParams)
>> return engine.execute(
>> select([literal_column('*')]).\
>> select_from(function_with_params).\
>> execution_options(autocommit=True)
>> )
>>
>> for row in execproc("dup", engine, [42]):
>> print row.f1, row.f2
>>
>>
>>
>>
>
> --
> 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.
