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.
Thanks! Jeff On Oct 22, 7:06 pm, Michael Bayer <[email protected]> wrote: > On Oct 22, 2009, at 6:33 PM, jeff wrote: > > > > > I would like to allow user's to save favorite queries in my Python > > app. Is there a way to find out the SQL statement as a string that > > can be then reused (e.g. Engine.execute(text(savedQueryText) ) )? > > Or is there another solution to this need? > > your best bet would be to serialize them using the serializer extension. > > http://www.sqlalchemy.org/docs/05/reference/ext/serializer.html?highl... > > the examples are ORM centric but you can use select() constructs too - > the session argument is optional. > > otherwise if you just want the raw SQL string, just call str > (statement). the string SQL is less wieldy being sent back into > SQLAlchemy though. > > > > > I generate a select object to execute. The string representations > > don't appear in a format that can be readily reused (or are missing > > the parameter values): > > str(query) > > 'SELECT host_status.host_name, host_status.version, > > host_status.host_id, host_status.sys_uptime, host_status.host_uptime, > > host_status.load_avg, count(%(param_1)s) AS "CMs", > > host_status.db_size, host_status.db_status, host_status.update_time > > \nFROM host_status, cm_status \nWHERE upper(host_status.site) = % > > (upper_1)s AND host_status.host_name = cm_status.host_name GROUP BY > > host_status.host_name, host_status.version, host_status.host_id, > > host_status.sys_uptime, host_status.host_uptime, host_status.load_avg, > > host_status.db_size, host_status.db_status, host_status.update_time > > ORDER BY host_status.host_name ASC' > > > str(self.execute(query)) > > 2009-10-22 16:19:42,642 INFO sqlalchemy.engine.base.Engine.0x...67b0 > > SELECT host_status.host_name, host_status.version, > > host_status.host_id, host_status.sys_uptime, host_status.host_uptime, > > host_status.load_avg, count(%(param_1)s) AS "CMs", > > host_status.db_size, host_status.db_status, host_status.update_time > > FROM host_status, cm_status > > WHERE upper(host_status.site) = %(upper_1)s AND host_status.host_name > > = cm_status.host_name GROUP BY host_status.host_name, > > host_status.version, host_status.host_id, host_status.sys_uptime, > > host_status.host_uptime, host_status.load_avg, host_status.db_size, > > host_status.db_status, host_status.update_time ORDER BY > > host_status.host_name ASC > > 2009-10-22 16:19:42,642 INFO sqlalchemy.engine.base.Engine.0x...67b0 > > {'param_1': 'cm_status.host_name', 'upper_1': 'LCO'} > > '<sqlalchemy.engine.base.ResultProxy object at 0x01D33F90>' > > > Thanks! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
