[sqlalchemy] Re: Updating a column to an expression containing itself; Inserting data by lists or tuples
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
[sqlalchemy] Re: Updating a column to an expression containing itself; Inserting data by lists or tuples
Well one good thing about the positional-style INSERT is that it just
might be useful for implementing INSERT INTO SELECT FROM, which
would be a pretty useful addition to the SQL API layer
On 2/6/07, S.R. Larmes [EMAIL PROTECTED] wrote:
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
[sqlalchemy] Re: Updating a column to an expression containing itself; Inserting data by lists or tuples
its all good, just not a high priority for me :). feel free to dig
in. the insert/update code is already a little intense due to it
already having multiple ways to do the same thing, not to mention its
some of the first code i wrote in SA and feels a little old, so adding
features like these might not be so straightforward.
On Feb 6, 3:46 pm, Rick Morrison [EMAIL PROTECTED] wrote:
Well one good thing about the positional-style INSERT is that it just
might be useful for implementing INSERT INTO SELECT FROM, which
would be a pretty useful addition to the SQL API layer
On 2/6/07, S.R. Larmes [EMAIL PROTECTED] wrote:
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
