Michael Bayer wrote:
> On Feb 6, 1:20 pm, "S.R. Larmes" <[EMAIL PROTECTED]> wrote:
> >
> > a) How do I do:
> >
> > UPDATE sometable SET value = value * 0.9 + 1 [ + optional WHERE
> > clause ] ?
> >
> > I've tried experimenting with .update(values=..) or stuff
> > like .execute(value=sometable.c.value*0.9+1) but it just tries to do
> > strange things. (SET value=sometable.value * %(sometable_value)s + %
> > (literal)s ??)
>
> that looks correct to me...all literal values are by default converted
> to bind params. if you execute the statement the bind values will be
> set up correctly.
Ahh, got it to work now. It doesn't seem to work with
.execute(value=table.c.value*0.9)) (gives programming error), but that
makes sense to me. :) With .update(values={..}) it works fine! Sorry
about that.
Thanks for the swift reply by the way.
> > b) I have an array (or tuple) with values that I'd just like to fire
> > off to insert() which is compiled to work on one column (the other
> > columns have defaults or pre-set values)... To me, this one ought to
> > be obvious and intuitive, and maybe I'm just stupid or blind, but I
> > can't find a way.
>
> how did you compile an insert() to work on just one (unnamed) column ?
> like table.insert(values=['someval']) ?
No, I didn't even know about that? I just meant that all the other
columns had bound values in values={..} or as default values so I
would
expect execute() (etc) to figure out their values automatically
without
me having to mention those columns again.
> > (Alternatively, if I have an array such as [(1, 'hi', 'text'), (2,
> > 'hello', 'tuxt'), ...] where the columns match up to the columns in
> > the table (or a given insert())...)
> >
> > .execute(*[dict(name=x) for x in array]) # to me, this is inelegant
> > and horribly roundabout
>
> well, SA is mostly name oriented at this time for several reasons:
>
> 1. there is no chance of ambiguity. for example, if you write code
> that just says table.insert().execute(1,2,3,4,5), supposing the table
> is reflected from the local database - that code might not be portable
> if executed on a different database where the table was created with a
> different column ordering, or had columns added via ALTER, etc.
That's true, but I imagine there would be a way to specify the columns
(once, when getting the insert()), and then trust subsequent
execute()s
to permute the arguments into their right order...
> 2. it is more database-neutral to build off named parameters, since
> its easy to convert from named param to positional, hard to convert in
> the other direction (ordering may not be consistent depending on how a
> dialect compiles a statement). as not all DBAPIs support positional
> bind parameters anyway, SA is going to create named bind parameters in
> any case so the "inelegant and roundabout" behavior will still occur,
> albeit behind the scenes, if support for positional execute()
> arguments were improved.
>
> since its *only* inserts that positional parameters even have a chance
> of working on all DBs, SA's parameter-processing logic was written
> around a "named parameter" concept since positional params are of very
> limited use.
But aren't the result set from selects also positional (AS WELL as
name-centric):
.select().execute().fetchone() gives something which can be thought
of either as a hash or as a tuple à la (1, 'ah', 102), and I'm using
the tuple... It would thus be consistent to be able to use similar
constructs with inserts.
> 3. lets look at the proposed feature itself. let me preface by
> saying, i will accept this feature into SA, and i am not opposed to
> it. if an adequate patch is provided, i will commit it. however, its
> not a priority for me to implement this myself. this is because it
> would be complex to implement, and the current insert() method is one
> step and is a "single way to do it". the method you are proposing
> requries two steps in most cases:
>
> i.e.
>
> table.insert().execute(*[dict(name=x) for x in array])
>
> specifies the column names and their values in one step. the proposed
> method requires in most cases a separate column specification step,
> currently the inelegant:
>
> table.insert(values=['fake value 1', 'fake value2']).execute(*array)
>
> but if we added a column list argument, would look like:
>
> insert([table.c.col1, table.c.col2]).execute(*array)
>
> which is still two steps. of course if you want to insert the whole
Two steps, but, I feel, more consistent and overall better design,
without always having to go via the tiny temporary dictionary objects.
Also, I don't think it should be necessary to unpack the array at all,
since we're using Python. If we wanted to enforced strict typing we
might be better off programming in a statically-typed environment
(no?). To me, the incredible dynamic powers of introspection and
polymorphism is half of the point of using Python. If I pass an array
to insert().execute() it should be intuitively obvious that I'm
trying to insert a series of rows, each represented by one item (be
it a scalar or a tuple) in the list... Likewise, if I pass a
dictionary or use keyword arguments, I'm out for named columns, and
so on. These could also be combined with well-defined rules (e.g.
that keyword arguments will always override values provided in any
array of tuples) for an even more expressive execute().
However! Like you, I'm just thinking aloud here, I'm not about to
write a patch for this since the "make a lot of tiny dicts"-method
works fine for now... :)
> row, then youd leave the table out...then you have some implicit
> behavior going on which may be less than ideal. also, we now have to
> check for error conditions like this:
>
> insert([table.c.col1, table.c.col2]).execute(col1=5, col2=7,
> col3=15)
>
> this probably breaks right now if you use the values clause in this
> manner.
>
> so..yeah...not against it as a feature, but to me its decidedly more
> complex and ambiguous than the current method.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
