On Fri, Oct 23, 2009 at 12:45 PM, jeff <jeff.sie...@seagate.com> wrote:
> > I would like to save a number of these in a database so size is > important (the serialized select() was somewhat large...) so I would > like to get the string representation of the raw SQL directly useable > by sqlalchemy if possible. As I have in my examples, the str(select) > doesn't seem directly useable as it is missing the parameters -> > upper(host_status.site) = %(upper_1)s instead of upper > (host_status.site) = 'LCO' for example. Is there a way to get the > raw SQL text just as it is sent to the database and ready for reuse by > sqlalchemy (execute(text(SQLtext) )? Or do I have to construct my > own by doing string replaces on the parameters with the parameters as > found in .context? Seems like the raw SQL has to be available at some > point but don't know if the user has access to it. > > This might be part of your answer: Here is an arbitrary query in my database (I use ORM, but I'm sure you can do equivalent with SQL expression language): qry = sess.query(L.ListName,L.Description,LI.Item).\ join(LI).order_by(L.ListName,LI.Item).\ filter(L.ListName.startswith('SP')) I can get the SQL as: sql=qry.statement.compile() string_sql = str(sql) print string_sql SELECT "Lists"."ListName", "Lists"."Description", "ListItems"."Item" FROM "Lists" JOIN "ListItems" ON "Lists"."ListName" = "ListItems"."ListName" WHERE "Lists"."ListName" LIKE :ListName_1 || '%%' ORDER BY "Lists"."ListName", "ListItems"."Item" parameters are available as: params = sql.params print params {u'ListName_1': 'SP'} Now, save "string_sql" and "params" (you might need to get creative about saving the dictionary) in your database. Later you can retrieve them and: conn = <whatever to get a good connection to database> results = conn.execute(text(string_sql), params).fetchall() This approach has all the limitations of using text() as described in the documentation. To me, the most important is that I have lost any knowledge about the nature of each column. I do not know that the first column is "Lists.ListName". Maybe there is an attribute on the result set that allows me to discover that information, but I don't know what it is. Hope this helps a little --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---