Thanks for the advice! One minor nit. At least in my experience, str(bound query) doesn't fill the params, or do quoting properly. Here is a demonstration:
fake_table = Table( 'faketable', metadata, Column('ts',Integer, index=True, nullable=False), Column('url',String, index=True, nullable=False), Column('hits',Integer, nullable=False), PrimaryKeyConstraint('ts','url','hits',name='summary_pk'), ) s = fake_table.c q = select([ s.url, s.ts, ]).\ where(s.url == bindparam("url")).\ where(s.ts == bindparam("ts")).\ where(s.hits < 100) assert fake_table.metadata.bind.name == 'postgresql' #it's bound assert str(q) == """\ SELECT faketable.url, faketable.ts FROM faketable WHERE faketable.url = %(url)s AND faketable.ts = %(ts)s AND faketable.hits < %(hits_1)s""" As you can see the 'url' isn't quoted, which is a problem! Also, the (hits_1) paremeter isn't filled in, even though it's already determined. What I would ideally like to see is this: >>> whatwould_happen(q,**some_dict): SELECT faketable.url, faketable.ts FROM faketable WHERE faketable.url = 'http://mypage.com/index.html' AND faketable.ts = 1829292929 AND faketable.hits < 100 If I had this string repr with filled params, I could just a string sub / regex, and go all the way into hackery! On Aug 4, 10:20 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Aug 4, 2010, at 10:34 AM, Gregg Lind wrote: > > > > > > > suppose: > > > summary_table = Table( > > 'summary', metadata, > > Column('ts',Integer, index=True, nullable=False), > > Column('url',String, index=True, nullable=False), > > Column('hits',Integer, nullable=False), > > PrimaryKeyConstraint('ts','url','hits',name='summary_pk'), > > ) > > > And a query like: > > > s = summary_table.c > > q = select([ > > s.url, > > s.ts, > > ]).\ > > where(s.site == bindparam("url")).\ > > where(s.ts == bindparam("ts") > > > * how to 'copy' a query. copy.copy(q) seems to be inadequate > > select() has a _generate() method that is used internally for "generative" > operations. But select() supports the "generative" interface specifically so > that you can treat it as an immutable structure, and copying should not be > necessary (I use it occasionally when I want to attach some additional state > to a select and not affect the original, though that is already a hacky > situation). > > > * how to print it, with params filled in. str(q) isn't quite enough. > > (I know this has been covered before, but I can't seem to find it, > > and > > if it's not in the docs, it should be!). The query is bound to an > > engine already. My desired goal is to see the actual sql (with > > filled quoted params) that would get sent to the engine. If I had > > this, I could always just use a regex to change the table. > > if the query is bound to an engine, meaning, its against a Table who's > MetaData is bound to the engine, then str(q) will invoke the compiler for > that engine's dialect and you will get the exact SQL that would be emitted. > If not, you call q.compile(bind=myengine) or q.compile(dialect=somedialect). > > > * how to change the table being called. My specific subcase is for an > > inherited > > table. I tried this, but it seems very dirty: > > > q._froms[0].name = 'summary_1279234800' > > > Is there a general method for that I should use? (Also, this will > > be fine > > if I can get the 'copy' business to work. > > This is not the right way to go. select()s are really not designed for > mutation, even though they have some mutative capabilities (which is mostly > for performance reasons). If you want a select that is against some other > table, you need a new select() object. Additionally, above you're even > modifying your Table construct, which, if you have a typical case with Table > objects declared at the module level, definitely isn't going to work very > well (you could do it with ad-hoc table() constructs, perhaps). > > The canonical way to create new selects out of old ones that are different > is to use clause transformation. In this case it would be: > > t1 = Table('summary_table', ...) > t2 = Table('summary_table_xyz', ...) > > def replace(obj): > if obj is t1: > return t2 > elif obj in t1.c: > return t2.c[obj.key] > else: > return None > > from sqlalchemy.sql.visitors import replacement_traverse > new_select = replacement_traverse(old_select, None, replace) > > The name of the table you have above there seems to suggest you have some > kind of "I have a ton of tables with the same columns" thing going on, so > here is a recipe for that: > > from sqlalchemy.sql import Alias > from sqlalchemy.ext.compiler import compiles > > class InhTable(Alias): > def __init__(self, table, name): > Alias.__init__(self, table, table.name + "_" + name) > > @compiles(InhTable) > def compile(element, compiler, **kw): > table_name = compiler.process(element.original, **kw) > return table_name.replace(element.original.name, > element.name) > > #usage: > > t1 = Table('asdf', MetaData(), Column('x', Integer), Column('y', Integer)) > t2 = InhTable(t1, "1279234800") > > print select([t2]) -- 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.