On Tue, Jan 2, 2018 at 7:53 PM, Aubrey <[email protected]> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
