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 at
> http://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.
