[sqlalchemy] Re: Saved Queries (or text representation of the SQL)

2009-10-23 Thread jeff 

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 mike...@zzzcomputing.com 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 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Saved Queries (or text representation of the SQL)

2009-10-23 Thread Mike Conley 
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Saved Queries (or text representation of the SQL)

2009-10-23 Thread Michael Bayer 

jeff wrote:

 I would like to save a number of these in a database so size is
 important (the serialized select() was somewhat large...)

using serializer() ?  really?  if you do a naive dumps() with plain
pickle, yes the serialize would be huge.


 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.

it uses bind parameters, yup.

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) )?

yes, compile it using the default compiler so that bind parameter strings
come out as :param format - str(mystatement.compile()).   then also
serialize the parameters on that object, mystatement.compile().params. 
Use them both later when constructing your text() object (text() accepts
bindparams).

you still will have problems though since you aren't maintaining the type
information of result columns.  So you might also want to serialize
[(c.key, c.type) for c in myselect.c] and send that back into text() via
the typemap argument.

Much easier to use serializer.



--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Saved Queries (or text representation of the SQL)

2009-10-23 Thread Mike Conley 
On Fri, Oct 23, 2009 at 1:47 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 Much easier to use serializer.


 I agree with that.

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Saved Queries (or text representation of the SQL)

2009-10-22 Thread Michael Bayer 


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?highlight=serializer#module-sqlalchemy.ext.serializer

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 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
-~--~~~~--~~--~--~---