On Tue, Jan 2, 2018 at 7:53 PM, Aubrey <aub...@kleetope.net> wrote:
> Hello,
>
> I've just been thrown by what seems like an inconsistency in how parameters
> can be passed to an insert statement that adds multiple rows.
>
> To demonstrate:
>
>
> from sqlalchemy import MetaData, String, Integer, Table, Column
> from sqlalchemy.dialects.postgresql.base import PGDialect
>
> m = MetaData()
>
> t = Table('mytable', m,
> Column('int_col', Integer),
> Column('str_col', String),
> )
>
> print("Case 1")
>
> print(t.insert().values(
> {t.c.str_col:"string", t.c.int_col:2}
> ).compile(dialect=PGDialect()))
>
> print("Case 2")
>
> print(t.insert().values(
> [
> {t.c.str_col:"str", t.c.int_col:1}
> ]
> ).compile(dialect=PGDialect()))
>
> print("Case 3")
>
> print(t.insert().values(
> [
> {t.c.str_col:"string", t.c.int_col:1},
> {t.c.str_col:"text", t.c.int_col:2}
> ]
> ).compile(dialect=PGDialect()))
>
>
> Case 1 and 2 will work but Case 3 will fail.
>
> I believe the this is because
> `sqlalchemy.sql.crud._extend_values_for_multiparams` only searches for string
> keys in each row. I altered the function to check for a column object or a
> string and Case 3 produced the expected output after this.
>
> I'm still getting used to sqlalchemy and I am unsure if this is intended, but
> it certainly took me by surprise. If it's not intentional, the fix seems very
> minor - I'd be happy to attempt a patch.
a little frustrating because I've checked the documentation again and
nowhere can I find it documenting the use case of sending column
objects as keys to values() for an INSERT; but for UPDATE there are.
Yet, there are tests that do test the use case of using column objects
as keys in the context of INSERT. the ORM uses strictly column keys
for INSERT but in a few spots does use the column object as key in the
case of an UPDATE.
The reason columns as objects make a little more sense for UPDATE is
because some databases do actually allow for a SQL expression that is
more than just a simple column to be established as the left side of
the SET clause (like Postgresql JSON assignment), but no such
equivalent exists on the INSERT side.
Nevertheless, it seems like a use case that should work across the
board but I'm kind of curious how you came up with it, if this is
actually documented somewhere I'm not seeing, or if documentation
showing columns as keys came from the UPDATE use case which naturally
seems like it should fit for INSERT too. I unfortunately have to
make sure not only that all the documented use cases are covered but
also all the naturally occurring, non-documented use cases, like this
one, also work, so I seek to understand better how people come up with
these cases.
TL;DR sure please supply a PR for the execute multiple case, thanks!
>
> Cheers,
> Aubrey
>
> --
> 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.
