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.
