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 <[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 at
http://groups.google.com/group/sqlalchemy?hl=en.
