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
Hi Michael,
Firstly at the outset, let me say that SQL Alchemy is one of the best
and most impressive library I ever saw in my career.
I lead a project called GNUKhata, www.gnukhata.org which is a free and
open source software aimed at chartered accountants, small to medium
business enterprises and retail shops.
The project is government funded and we have enough desire and funds to
make it the most popular and ubiquitous software for accounting as
firefox is to internet.
I wish to tell you that SA is the power house behind our core engine
that uses postgresql as the database server.
SA has made our work so easy and sql has become bliss with it.
I infact wish to add this projject as a testimony for your great work.
We are soon to launch the software and it is going to be a great success
given the marketing we are doing and the social projects we wish to
undertake with it.
Now coming back to your function.e
You see, I wish to use this execProc function for executing all the
stored procedureshen including inserts, updates, deletes and selects.
With regard to the latest revision you sent for the function,
I wish to know if I can use it for all the mentioned purposes?
For example if I have a table already in the database called groups with
groupcode, groupname and groupdesc as my fields, can I use this version
of execProc to access a stored procedure related to the table?
Suppose the stored procedure has 2 out parameters, groupname and groupdesc.
can I execute this procedure, say getGroups() through this execProc
function.
Then, can I do a fetchall and then,
for row in rows:
print row["groupname"] ...
and so on?
I think this might be pritty obvious, but the example you posted talked
about creating the table etc. So just wished to get it cleared.
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.