[sqlalchemy] constant tables / VALUES expression
How do I translate the following: select A.column, V.queried from A, (VALUES ( ('foo'), ('bar') )) as V (queried) where A.column2 = V.queried; into sqlalchemy-speak. I'm not using the ORM. http://www.postgresql.org/docs/8.4/static/sql-values.html -- Jon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki On May 17, 2010, at 8:46 AM, Jon Nelson wrote: How do I translate the following: select A.column, V.queried from A, (VALUES ( ('foo'), ('bar') )) as V (queried) where A.column2 = V.queried; into sqlalchemy-speak. I'm not using the ORM. http://www.postgresql.org/docs/8.4/static/sql-values.html -- Jon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
On Mon, May 17, 2010 at 8:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki Cool! However, http://www.postgresql.org/docs/8.4/static/sql-values.html seems to indicate that that is a performance impact of using VALUES with lots of values. Does anybody know what lots means? 50,000? 500,000? 14? -- Jon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
On Mon, May 17, 2010 at 8:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki The part about the thread that worries me a bit is this: (%s) % , .join(repr(elem) for elem in tup) Is there a way to do this using the same parameter escaping mechanism that the rest of SA uses? The repr() seems a bit off to me here. Of course, being able to specify the names of columns would also be nifty - while it's nice to rely on column1, column2 it's also nice to get in your result sets the names that one might prefer. -- Jon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
On May 17, 2010, at 10:14 AM, Jon Nelson wrote: On Mon, May 17, 2010 at 8:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki The part about the thread that worries me a bit is this: (%s) % , .join(repr(elem) for elem in tup) Is there a way to do this using the same parameter escaping mechanism that the rest of SA uses? The repr() seems a bit off to me here. Of course, being able to specify the names of columns would also be nifty - while it's nice to rely on column1, column2 it's also nice to get in your result sets the names that one might prefer. The escaping mechanism you speak of is native to the DBAPI, in your case psycopg2.I don't believe it has a public API. We have a slightly better escaping mechanism embedded in the compiler for usage with databases that don't allow binds everywhere in the statement. But we have avoided having to build our own escapers for a very long time now as it is a contentious area. Here it is: def render_literal_value(self, value, type_): Render the value of a bind parameter as a quoted literal. This is used for statement sections that do not accept bind paramters on the target driver/database. This should be implemented by subclasses using the quoting services of the DBAPI. if isinstance(value, basestring): value = value.replace(', '') return '%s' % value elif value is None: return NULL elif isinstance(value, (float, int, long)): return repr(value) elif isinstance(value, decimal.Decimal): return str(value) else: raise NotImplementedError(Don't know how to literal-quote value %r % value) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
On Mon, May 17, 2010 at 9:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: On May 17, 2010, at 10:14 AM, Jon Nelson wrote: On Mon, May 17, 2010 at 8:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki The part about the thread that worries me a bit is this: (%s) % , .join(repr(elem) for elem in tup) Is there a way to do this using the same parameter escaping mechanism that the rest of SA uses? The repr() seems a bit off to me here. Of course, being able to specify the names of columns would also be nifty - while it's nice to rely on column1, column2 it's also nice to get in your result sets the names that one might prefer. The escaping mechanism you speak of is native to the DBAPI, in your case psycopg2. I don't believe it has a public API. Exactly. We have a slightly better escaping mechanism embedded in the compiler for usage with databases that don't allow binds everywhere in the statement. But we have avoided having to build our own escapers for a very long time now as it is a contentious area. Hmm. Is there a way to make the VALUES stuff avoid escaping anything and use the existing interpolation (and escaping) mechanism? -- Jon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] constant tables / VALUES expression
On May 17, 2010, at 10:55 AM, Jon Nelson wrote: On Mon, May 17, 2010 at 9:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: On May 17, 2010, at 10:14 AM, Jon Nelson wrote: On Mon, May 17, 2010 at 8:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: it is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628d7ebee5/4421b272d4c7f91f let me add that to the wiki The part about the thread that worries me a bit is this: (%s) % , .join(repr(elem) for elem in tup) Is there a way to do this using the same parameter escaping mechanism that the rest of SA uses? The repr() seems a bit off to me here. Of course, being able to specify the names of columns would also be nifty - while it's nice to rely on column1, column2 it's also nice to get in your result sets the names that one might prefer. The escaping mechanism you speak of is native to the DBAPI, in your case psycopg2.I don't believe it has a public API. Exactly. We have a slightly better escaping mechanism embedded in the compiler for usage with databases that don't allow binds everywhere in the statement. But we have avoided having to build our own escapers for a very long time now as it is a contentious area. Hmm. Is there a way to make the VALUES stuff avoid escaping anything and use the existing interpolation (and escaping) mechanism? when you say existing, if you mean the one native to bind parameters in the DBAPI, not that I'm aware of. If you mean the one we have in Compiler, yeah the compiler is passed to the @compiles decorated call so you should be able to call render_literal_value() from that Compiler object directly. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.