Executing table insert with variable items yields unexpected results
I recently stumbled over the following feature:
Suppose I have a table model Dummy
inheriting from the declarative base:
class Dummy(Base):
a = Column(Integer)
b = Column(Integer)
In order to efficiently insert many rows into the table I build a list values
of dict
s and run the following commands in a valid session:
session.execute(Dummy.__table__.insert(), values)
session.commit()
Now the kicker is, which needs to be documented I think, that if the first dict
in that list is, for example, {"a": 3}
and any following dict
contains both a
and b
, all the following b
s are ignored, i.e., None. If the first is {"a": 1, "b": 3}
and any following contains only one key then this causes a StatementError
. If both pairs are present in the dictionary but one of them is None
this works perfectly.
So for the sake of efficiency, I can understand why it works the way that it does but I didn't see this written down anywhere and I think it should be. Maybe it belongs in the insert tutorial, not sure.
Comments (3)
-
repo owner -
reporter The last paragraph is actually plenty clear. Thank you for pointing me to it. It's not always easy to find the right piece of documentation for SQLAlchemy, I feel.
-
reporter - changed status to resolved
- Log in to comment
the last paragraph in this section can be improved: http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#executing-multiple-statements perhaps with more of a green "sidebar" or "note" box. I think the StatementError you get itself was added somewhat recently, probably in 0.8, because yes, we try not to do too much with the dictionaries here, as the "I need to insert a zillion dictionaries" use case is where people always zing us with complaints of slowness....in blogs, stackoverflow, you name it. people just love trying to shove bulk inserts where they don't belong.
Anyway. the paragraph in question is in the Core tutorial. If folks are working with table.insert(), even if it's via Session.execute(), I would hope that they refer to this tutorial.