Hi Michael,
Excelent work, you are a real artist.
Just wished to know what is the trans object, and do i need to use a
connection object for the thing to work?
I mean, do I need the conn = engine.connect() line?
I already have a set of live engines so I never make an extra connection
in my execProc method, as you must have seen in my very first email with
explanations.
Thanks a million for the splendid work and your most valued help.
happy hacking.
Krishnakant.
On 13/02/12 00:21, Michael Bayer wrote:
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
