diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-03-08 00:15:46 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-03-08 00:15:46 -0500 |
commit | 6cc9c976d6c101f03579e4e4821bd6277599191a (patch) | |
tree | b9ca63e10bd7dca6c8556ec0735b5f4cb7f87f6a | |
parent | 334e04d710f16a04ce4d9ed737ec2d0a02b11de4 (diff) | |
download | alembic-6cc9c976d6c101f03579e4e4821bd6277599191a.tar.gz |
- Fixed bug where :meth:`.Operations.bulk_insert` would not function
properly when :meth:`.Operations.inline_literal` values were used,
either in --sql or non-sql mode. The values will now render
directly in --sql mode. For compatibility with "online" mode,
a new flag :paramref:`~.Operations.inline_literal.multiparams`
can be set to False which will cause each parameter set to be
compiled and executed with individual INSERT statements.
fixes #179
-rw-r--r-- | alembic/ddl/impl.py | 12 | ||||
-rw-r--r-- | alembic/ddl/mssql.py | 6 | ||||
-rw-r--r-- | alembic/operations.py | 51 | ||||
-rw-r--r-- | docs/build/changelog.rst | 12 | ||||
-rw-r--r-- | tests/test_bulk_insert.py | 38 |
5 files changed, 111 insertions, 8 deletions
diff --git a/alembic/ddl/impl.py b/alembic/ddl/impl.py index 4b85a00..79cbd36 100644 --- a/alembic/ddl/impl.py +++ b/alembic/ddl/impl.py @@ -163,7 +163,7 @@ class DefaultImpl(with_metaclass(ImplMeta)): def drop_index(self, index): self._exec(schema.DropIndex(index)) - def bulk_insert(self, table, rows): + def bulk_insert(self, table, rows, multiinsert=True): if not isinstance(rows, list): raise TypeError("List expected") elif rows and not isinstance(rows[0], dict): @@ -171,7 +171,9 @@ class DefaultImpl(with_metaclass(ImplMeta)): if self.as_sql: for row in rows: self._exec(table.insert(inline=True).values(**dict( - (k, _literal_bindparam(k, v, type_=table.c[k].type)) + (k, + _literal_bindparam(k, v, type_=table.c[k].type) + if not isinstance(v, _literal_bindparam) else v) for k, v in row.items() ))) else: @@ -179,7 +181,11 @@ class DefaultImpl(with_metaclass(ImplMeta)): if not hasattr(table, '_autoincrement_column'): table._autoincrement_column = None if rows: - self._exec(table.insert(inline=True), multiparams=rows) + if multiinsert: + self._exec(table.insert(inline=True), multiparams=rows) + else: + for row in rows: + self._exec(table.insert(inline=True).values(**row)) def compare_type(self, inspector_column, metadata_column): diff --git a/alembic/ddl/mssql.py b/alembic/ddl/mssql.py index 095dbf3..58b57cf 100644 --- a/alembic/ddl/mssql.py +++ b/alembic/ddl/mssql.py @@ -86,19 +86,19 @@ class MSSQLImpl(DefaultImpl): schema=schema, name=name) - def bulk_insert(self, table, rows): + def bulk_insert(self, table, rows, **kw): if self.as_sql: self._exec( "SET IDENTITY_INSERT %s ON" % self.dialect.identifier_preparer.format_table(table) ) - super(MSSQLImpl, self).bulk_insert(table, rows) + super(MSSQLImpl, self).bulk_insert(table, rows, **kw) self._exec( "SET IDENTITY_INSERT %s OFF" % self.dialect.identifier_preparer.format_table(table) ) else: - super(MSSQLImpl, self).bulk_insert(table, rows) + super(MSSQLImpl, self).bulk_insert(table, rows, **kw) def drop_column(self, table_name, column, **kw): diff --git a/alembic/operations.py b/alembic/operations.py index dfb942b..c11d381 100644 --- a/alembic/operations.py +++ b/alembic/operations.py @@ -774,7 +774,7 @@ class Operations(object): t.append_constraint(const) self.impl.drop_constraint(const) - def bulk_insert(self, table, rows): + def bulk_insert(self, table, rows, multiinsert=True): """Issue a "bulk insert" operation using the current migration context. @@ -808,8 +808,55 @@ class Operations(object): 'create_date':date(2008, 8, 15)}, ] ) + + When using --sql mode, some datatypes may not render inline automatically, + such as dates and other special types. When this issue is present, + :meth:`.Operations.inline_literal` may be used:: + + op.bulk_insert(accounts_table, + [ + {'id':1, 'name':'John Smith', + 'create_date':op.inline_literal("2010-10-05")}, + {'id':2, 'name':'Ed Williams', + 'create_date':op.inline_literal("2007-05-27")}, + {'id':3, 'name':'Wendy Jones', + 'create_date':op.inline_literal("2008-08-15")}, + ], + multiparams=False + ) + + When using :meth:`.Operations.inline_literal` in conjunction with + :meth:`.Operations.bulk_insert`, in order for the statement to work + in "online" (e.g. non --sql) mode, the + :paramref:`~.Operations.inline_literal.multiparams` + flag should be set to ``False``, which will have the effect of + individual INSERT statements being emitted to the database, each + with a distinct VALUES clause, so that the "inline" values can + still be rendered, rather than attempting to pass the values + as bound parameters. + + .. versionadded:: 0.6.4 :meth:`.Operations.inline_literal` can now + be used with :meth:`.Operations.bulk_insert`, and the + :paramref:`~.Operations.inline_literal.multiparams` flag has + been added to assist in this usage when running in "online" + mode. + + :param table: a table object which represents the target of the INSERT. + + :param rows: a list of dictionaries indicating rows. + + :param multiinsert: when at its default of True and --sql mode is not + enabled, the INSERT statement will be executed using + "executemany()" style, where all elements in the list of dictionaries + are passed as bound parameters in a single list. Setting this + to False results in individual INSERT statements being emitted + per parameter set, and is needed in those cases where non-literal + values are present in the parameter sets. + + .. versionadded:: 0.6.4 + """ - self.impl.bulk_insert(table, rows) + self.impl.bulk_insert(table, rows, multiinsert=multiinsert) def inline_literal(self, value, type_=None): """Produce an 'inline literal' expression, suitable for diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst index bf58c6f..69b64f9 100644 --- a/docs/build/changelog.rst +++ b/docs/build/changelog.rst @@ -6,6 +6,18 @@ Changelog :version: 0.6.4 .. change:: + :tags: bug + :tickets: 179 + + Fixed bug where :meth:`.Operations.bulk_insert` would not function + properly when :meth:`.Operations.inline_literal` values were used, + either in --sql or non-sql mode. The values will now render + directly in --sql mode. For compatibility with "online" mode, + a new flag :paramref:`~.Operations.inline_literal.multiparams` + can be set to False which will cause each parameter set to be + compiled and executed with individual INSERT statements. + + .. change:: :tags: bug, py3k :tickets: 175 diff --git a/tests/test_bulk_insert.py b/tests/test_bulk_insert.py index 8133a29..cc56731 100644 --- a/tests/test_bulk_insert.py +++ b/tests/test_bulk_insert.py @@ -4,6 +4,7 @@ from alembic import op from sqlalchemy import Integer, String from sqlalchemy.sql import table, column from sqlalchemy import Table, Column, MetaData +from sqlalchemy.types import TypeEngine from . import op_fixture, eq_, assert_raises_message @@ -108,6 +109,24 @@ def test_bulk_insert_mssql(): 'INSERT INTO ins_table (id, v1, v2) VALUES (:id, :v1, :v2)' ) +def test_bulk_insert_inline_literal_as_sql(): + context = op_fixture('postgresql', True) + + class MyType(TypeEngine): + pass + + t1 = table('t', column('id', Integer), column('data', MyType())) + + op.bulk_insert(t1, [ + {'id': 1, 'data': op.inline_literal('d1')}, + {'id': 2, 'data': op.inline_literal('d2')}, + ]) + context.assert_( + "INSERT INTO t (id, data) VALUES (1, 'd1')", + "INSERT INTO t (id, data) VALUES (2, 'd2')" + ) + + def test_bulk_insert_as_sql(): context = _test_bulk_insert('default', True) context.assert_( @@ -204,3 +223,22 @@ class RoundTripTest(TestCase): ] ) + def test_bulk_insert_inline_literal(self): + class MyType(TypeEngine): + pass + + t1 = table('foo', column('id', Integer), column('data', MyType())) + + self.op.bulk_insert(t1, [ + {'id': 1, 'data': self.op.inline_literal('d1')}, + {'id': 2, 'data': self.op.inline_literal('d2')}, + ], multiinsert=False) + + eq_( + self.conn.execute("select id, data from foo").fetchall(), + [ + (1, "d1"), + (2, "d2"), + ] + ) + |