Thank you for the more detailed explanation! I will do some experiments with it!
Gregg On Aug 4, 12:39 pm, Michael Bayer <[email protected]> wrote: > On Aug 4, 2010, at 1:26 PM, Gregg Lind wrote: > > > > > 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! > > But, that is exactly what is sent to the DBAPI. The "quoting" happens at > the earliest in the DBAPI layer. Some DBAPIs don't ever "quote" anything, > the binds are sent separately for some backends and the database server > itself handles interpolation internally. If you turn on your PG logs to > log SQL, you'd see the quoting affair is pretty unpleasant so its critical > that DBAPIs handle this. > > The parameters are available from the compiled object as the "params" > collection. > > > 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 <[email protected]> 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 [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://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 [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.
