I don't have a solution for that at the moment other than to use a straight Core insert, if there aren't other complicating factors. bulk_insert_mappings() tries to keep the same contract as that of a regular Session.flush(), where None means to "omit" the value, which has affects whether or not column-level defaults fire off.
Perhaps supporting a constant such as BIND_NULL, or adding support for the bulk insert to interpret null() as a bound value, might be a way to support this use case. or just a flag on bulk_insert_mappings, which would be easier to develop and test and less confusing. On 01/06/2016 03:45 PM, Tobias wrote: > Hi, > > I am using Session.bulk_insert_mappings() > <http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.bulk_insert_mappings> > to insert a number of entities. This works great except when there are > entities with None values. For example the following call issues three > insert statements instead of a single one: > > session.bulk_insert_mappings(Doc, [ > dict(id=1, col_a='A', col_b='B'), > dict(id=2, col_a='A', col_b=None), > dict(id=3, col_a='A', col_b='B') > ]) > > > Log: > > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine INSERT > INTO doc (id, col_a, col_b) VALUES (?, ?, ?) > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine (1, 'A', 'B') > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT > INTO doc (id, col_a) VALUES (?, ?) > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (2, 'A') > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT > INTO doc (id, col_a, col_b) VALUES (?, ?, ?) > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (3, 'A', 'B') > > > I would expect that a single insert is made and that `null` is used as > value for `col_b`. I tried to use `sqlalchemy.sql.expression.null()` but > that does not seem to work with the bulk functions. I also know that I > could use evaluates_none() > <http://docs.sqlalchemy.org/en/latest/core/type_api.html#sqlalchemy.types.TypeEngine.evaluates_none>, > but I don't want to change the model because the bulk insertion is only > used in the migration script. > > Here is an example: https://gist.github.com/tsauerwein/d9630336731fff0547ba > > Thanks, > Tobias > > -- > 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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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.
