On 13/02/12 02:19, Michael Bayer wrote:
Transaction is a SQLAlchemy object that represents the scope of the
"transaction" within the DBAPI. The DBAPI always presents a transaction,
that's why the pure DBAPI version calls conn.commit() at the end.
So does that mean I will have to use a connection object and not do the
execution with just engine?
Note that the engine parameter I pass to the execProc is already
connected to a database.
So do I need to create an additional connection object?
I thought that the engine had its own implesit connection which it uses
for executing the functions or any sql for that matter.
You could also say conn = engine.connect().execution_options(autocommit=True).
Ah, meaning the connection object is just a formal requirement is it?
Happy hacking.
Krishnakant.
On Feb 12, 2012, at 2:18 PM, Krishnakant Mane wrote:
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 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.
--
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.
