Thank you for the more detailed explanation! I will do some
experiments with it!
Gregg
On Aug 4, 12:39 pm, Michael Bayer <mike...@zzzcomputing.com> 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 <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
> > 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 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.
