On Fri, Oct 23, 2009 at 12:45 PM, jeff <[email protected]> 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 [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
-~----------~----~----~----~------~----~------~--~---
