On Thu, Sep 7, 2017 at 11:10 AM, Colin Deasy <coldeasy.hotm...@gmail.com> wrote:
> Hi,
>
> I have a use-case where I need to set the value of a column based on the
> values of other columns during an insert.
> This works fine for an insert for a single row where
> `context.current_parameters` reflects the values of the other columns I care
> about.
> When I have a multi-row insert, `context.current_parameters` is no longer
> usable since the column names are munged (e.g. assuming a `user_id` column,
> and an insert like `insert(table).values([{'user_id': 1}, {'user_id': 2},
> {'user_id': 3}])` you might get something like `{'user_id_m0': 1,
> 'user_id_m1': 2, 'user_id_m2': 3}`. It doesn't seem possible to tell which
> row we are operating on just by looking at `context` so we cannot determine
> which munged name we should look at. Ideally the current parameters would
> only reflect the parameters for a single row.
>
> Is this a known limitation of defaults usage? Any workarounds?
normally you would use standard executemany() style:
conn.execute(insert(table), [{"user_id": 1}, {"user_id": 2}, {"user_id": 3}])
In this form, you would receive each dictionary individually.
The insert.values(< multiparams > ) syntax is specifically a single
SQL statement:
INSERT INTO table (user_id) VALUES (:user_id_m0) (:user_id_m1)
(:user_id_m2) (:user_id_m3) ...
in this form, you are getting the parameters that are passed with the
statement, and it is a single dictionary with many values in it.
this statement form is unlike any others in SQL so no special steps
were taken within the context system to make it more palatable for the
calling program.
This is easy to work around, and I'm also not sure if integrating the
workaround into the API emitted towards the default callable is the
right thing to do - perhaps some folks expect to see the full set of
parameters or have their own workarounds which would be impacted.
Workaround is:
def my_callable(parameters):
if "user_id" in parameters:
# do the default thing
else:
cols_we_care_about = ('user_id', 'some_id')
i = 0
while "user_id_m%d" % i in parameters:
d = dict((key, parameters["%s_%d" % (key, i)]) for key in
cols_we_care_about)
my_callable(d)
parameters.update(("%s_%d" % (key, i), d[key]) for key in d)
i += 1
>
> Cheers
> Colin
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
