Working with a ResultProxy object

[RVince]

In my controller class, I perform a rather basic, straightforward SQL
query:

connection = engine.connect()
trans = connection.begin()
try:
c.result = connection.execute("select
current_disposition_code,count(*) as num from cms_input_file group by
current_disposition_code;")
connection.close()

thus my c.result is an sqlalchemy.engine.base.ResultProxy object.

When I go to render this in a mako file as:

% for result in c.results:
${result.current_disposition_code}[${result.num}]
% endfor

I get no output. I am quite certain this is becuase I am using a
ResultProxy object. How can I output such an object inthe mako files,
or alternatively, how might i convert a ResultProxy objectsuch that I
can output it in a mako file? Thanks! RVince

[Tamás Bajusz]

Seems you missed fetching some rows from ResultProxy.
http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.base.ResultProxy

Hope this helps.

[RVince]

Tamas,

I'm more confused now -- would I do this in my controller or in the
mako file? If, in my controller, dont I need to create an array for
each field, that is:

i = 0
for row in c.result
c.current_disposition_code[i] = row['current_disposition_code']
c.num[i] = row['num']
i = i + 1

I dont see how I can move, say, c.current_disposition_code and c.num
to the make output? I don't seem to be getting any output still (I'm
still missing something):

% for result in c.current_disposition_code:
${result}
% endfor

Thanks for your help and insight with this. RVince
On Aug 2, 9:26 am, Tamás Bajusz <gbt...@gmail.com> wrote:

> Seems you missed fetching some rows from ResultProxy.http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engin...
>
> Hope this helps.

[Michael Bayer]

On Aug 2, 2011, at 9:07 AM, RVince wrote:

> In my controller class, I perform a rather basic, straightforward SQL
> query:
>
> connection = engine.connect()

connection, OK

> trans = connection.begin()

start a transaction, OK

> try:
> c.result = connection.execute("select
> current_disposition_code,count(*) as num from cms_input_file group by
> current_disposition_code;")

> connection.close()

close the connection ? what happened to trans, wheres the rollback or commit ? what about your c.result that hasn't been iterated yet ? both of these need an active Connection to proceed correctly. Technically the transaction is fine with just the connection.close() at the end but its a strange form that doesn't make the intent clear. The cursor referenced by the ResultProxy definitely should have exclusive access to its parent Connection for its whole lifespan, though, closing it out can have one of several negative effects, depending on the backend and the configuration of the connection pool.

Want to know what would be easy here ?

c.result = engine.execute("select * from my_table")

then you're done. The ResultProxy in this case manages the Connection itself which will be closed when the ResultProxy closes. However this assumes you just need one SQL statement in a transaction, it seems that perhaps the begin() call is because you're doing several things with the one Connection.

Ideally, assuming this is Pylons, you'd have a single Connection declared for all controllers up in your BaseController, with a trans = connection.begin() at the top and trans.rollback() at the bottom. That way the mechanics of providing database context to controllers is handled in application logic and not business logic. Or you'd have the ORM Session in place and just use Session.execute() to get a result.

[RVince]

Michael,

Right, thats a better idea -- I am working with snippets of other
people's code here. However, I still have the original problem of
going from a ResultProxy object to output in a mako file which is a
chasm I cannnot seem to bridge! RVInce

[Michael Bayer]

On Aug 2, 2011, at 11:31 AM, RVince wrote:

> Michael,
>
> Right, thats a better idea -- I am working with snippets of other
> people's code here. However, I still have the original problem of
> going from a ResultProxy object to output in a mako file which is a
> chasm I cannnot seem to bridge! RVInce

there's nothing wrong with assigning a ResultProxy to c.result, then iterating it in a template, as long as the connection context remains open. Your premature closing it is the likely issue here.

>
> On Aug 2, 10:06 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Aug 2, 2011, at 9:07 AM, RVince wrote:
>>
>>> In my controller class, I perform a rather basic, straightforward SQL
>>> query:
>>
>>> connection = engine.connect()
>>
>> connection, OK
>>
>>> trans = connection.begin()
>>
>> start a transaction, OK
>>
>>> try:
>>> c.result = connection.execute("select
>>> current_disposition_code,count(*) as num from cms_input_file group by
>>> current_disposition_code;")
>>> connection.close()
>>
>> close the connection ? what happened to trans, wheres the rollback or commit ? what about your c.result that hasn't been iterated yet ? both of these need an active Connection to proceed correctly. Technically the transaction is fine with just the connection.close() at the end but its a strange form that doesn't make the intent clear. The cursor referenced by the ResultProxy definitely should have exclusive access to its parent Connection for its whole lifespan, though, closing it out can have one of several negative effects, depending on the backend and the configuration of the connection pool.
>>
>> Want to know what would be easy here ?
>>
>> c.result = engine.execute("select * from my_table")
>>
>> then you're done. The ResultProxy in this case manages the Connection itself which will be closed when the ResultProxy closes. However this assumes you just need one SQL statement in a transaction, it seems that perhaps the begin() call is because you're doing several things with the one Connection.
>>
>> Ideally, assuming this is Pylons, you'd have a single Connection declared for all controllers up in your BaseController, with a trans = connection.begin() at the top and trans.rollback() at the bottom. That way the mechanics of providing database context to controllers is handled in application logic and not business logic. Or you'd have the ORM Session in place and just use Session.execute() to get a result.
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[RVince]

Michael,

But if I do the following, and do NOT close it (at least, I dont
believe I am now):

result = engine.execute("select

current_disposition_code,count(*) as num from cms_input_file group by
current_disposition_code;")

c.curent_disposition_codes = []
c.num = []
for row in result:

c.curent_disposition_codes.append(str(row['current_disposition_code']))
c.num.append(str(row['num'] ))

And I look in at c.curent_disposition_codes and c.num, and I see 4
values for each, then, when I go to render it with:

<%
i = 0
%>
% for result in c.current_disposition_codes:
${result}[${c.num.index(i)}]
<%

i = i + 1

%>
% endfor

I get no output at all. The last bit of code, the mako file, is
executing, and properly, but it is not outputting values -- is there
something obvious that I am missing here? Thanks so much for your help
here. RVince

[Michael Bayer]

On Aug 2, 2011, at 12:09 PM, RVince wrote:

> Michael,
>
> But if I do the following, and do NOT close it (at least, I dont
> believe I am now):
>
> result = engine.execute("select
> current_disposition_code,count(*) as num from cms_input_file group by
> current_disposition_code;")
> c.curent_disposition_codes = []
> c.num = []
> for row in result:
>
> c.curent_disposition_codes.append(str(row['current_disposition_code']))
> c.num.append(str(row['num'] ))
>
> And I look in at c.curent_disposition_codes and c.num, and I see 4
> values for each, then, when I go to render it with:
>
> <%
> i = 0
> %>
> % for result in c.current_disposition_codes:
> ${result}[${c.num.index(i)}]
> <%
> i = i + 1
> %>
> % endfor

In this case you are running through the result proxy in your controller, assigning to a collection, and its done. That is perfectly fine. Feel free to close out the connection after iterating the result set.

Then, it is unusual that you're indexing what is basically a string, i.e. each value of "result" with the [] operator though.

[RVince]

LOL,I'm going backwards here, and taking far too long to get something
so very simple done (there's a genuine lack of documentation here I
think -- the only way to manage to learn much of this is by trial and
error). Rather than trying to do this with straight SQL statements I
will go plan B here and try to rewrite their SQL library to be
consistent with the SQL alchemy idiom for selecting. This way, I will
get the output into these mako files.