From f1a3038f480ee1965928cdcd1dc0c47347f270bc Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 23 Jun 2020 16:21:04 -0400 Subject: Default psycopg2 executemany mode to "values_only" The psycopg2 dialect now defaults to using the very performant ``execute_values()`` psycopg2 extension for compiled INSERT statements, and also impements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change. Implements RETURNING for insert with executemany Adds support to return_defaults() mode and inserted_primary_key to support mutiple INSERTed rows, via return_defauls_rows and inserted_primary_key_rows accessors. within default execution context, new cached compiler getters are used to fetch primary keys from rows inserted_primary_key now returns a plain tuple. this is not yet a row-like object however this can be added. Adds distinct "values_only" and "batch" modes, as "values" has a lot of benefits but "batch" breaks cursor.rowcount psycopg2 minimum version 2.7 so we can remove the large number of checks for very old versions of psycopg2 simplify tests to no longer distinguish between native and non-native json Fixes: #5401 Change-Id: Ic08fd3423d4c5d16ca50994460c0c234868bd61c --- lib/sqlalchemy/testing/assertions.py | 3 --- lib/sqlalchemy/testing/assertsql.py | 2 +- lib/sqlalchemy/testing/requirements.py | 14 ++++++++++++++ lib/sqlalchemy/testing/suite/test_insert.py | 8 ++++---- lib/sqlalchemy/testing/suite/test_sequence.py | 6 ++++-- 5 files changed, 23 insertions(+), 10 deletions(-) (limited to 'lib/sqlalchemy/testing') diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py index 54da06a3d..1ea366dac 100644 --- a/lib/sqlalchemy/testing/assertions.py +++ b/lib/sqlalchemy/testing/assertions.py @@ -355,7 +355,6 @@ class AssertsCompiledSQL(object): schema_translate_map=None, render_schema_translate=False, default_schema_name=None, - inline_flag=None, ): if use_default_dialect: dialect = default.DefaultDialect() @@ -451,8 +450,6 @@ class AssertsCompiledSQL(object): }, check_post_param, ) - if inline_flag is not None: - eq_(c.inline, inline_flag) class ComparesTables(object): diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py index 48cbb4694..ef324635e 100644 --- a/lib/sqlalchemy/testing/assertsql.py +++ b/lib/sqlalchemy/testing/assertsql.py @@ -106,7 +106,7 @@ class CompiledSQL(SQLMatchRule): compiled = execute_observed.clauseelement.compile( dialect=compare_dialect, column_keys=context.compiled.column_keys, - inline=context.compiled.inline, + for_executemany=context.compiled.for_executemany, schema_translate_map=map_, ) _received_statement = re.sub(r"[\n\t]", "", util.text_type(compiled)) diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index 163276ca9..3e20f8681 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -323,6 +323,20 @@ class SuiteRequirements(Requirements): "%(database)s %(does_support)s 'RETURNING of multiple rows'", ) + @property + def insert_executemany_returning(self): + """target platform supports RETURNING when INSERT is used with + executemany(), e.g. multiple parameter sets, indicating + as many rows come back as do parameter sets were passed. + + """ + + return exclusions.only_if( + lambda config: config.db.dialect.insert_executemany_returning, + "%(database)s %(does_support)s 'RETURNING of " + "multiple rows with INSERT executemany'", + ) + @property def returning(self): """target platform supports RETURNING for at least one row. diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py index 65741941f..5b8c343c4 100644 --- a/lib/sqlalchemy/testing/suite/test_insert.py +++ b/lib/sqlalchemy/testing/suite/test_insert.py @@ -56,7 +56,7 @@ class LastrowidTest(fixtures.TablesTest): self.tables.autoinc_pk.insert(), data="some data" ) pk = connection.scalar(select([self.tables.autoinc_pk.c.id])) - eq_(r.inserted_primary_key, [pk]) + eq_(r.inserted_primary_key, (pk,)) @requirements.dbapi_lastrowid def test_native_lastrowid_autoinc(self, connection): @@ -184,7 +184,7 @@ class InsertBehaviorTest(fixtures.TablesTest): ) ) - eq_(result.inserted_primary_key, [None]) + eq_(result.inserted_primary_key, (None,)) result = connection.execute( select([dest_table.c.data]).order_by(dest_table.c.data) @@ -204,7 +204,7 @@ class InsertBehaviorTest(fixtures.TablesTest): ), ) ) - eq_(result.inserted_primary_key, [None]) + eq_(result.inserted_primary_key, (None,)) result = connection.execute( select([dest_table.c.data]).order_by(dest_table.c.data) @@ -329,7 +329,7 @@ class ReturningTest(fixtures.TablesTest): self.tables.autoinc_pk.insert(), data="some data" ) pk = connection.scalar(select([self.tables.autoinc_pk.c.id])) - eq_(r.inserted_primary_key, [pk]) + eq_(r.inserted_primary_key, (pk,)) __all__ = ("LastrowidTest", "InsertBehaviorTest", "ReturningTest") diff --git a/lib/sqlalchemy/testing/suite/test_sequence.py b/lib/sqlalchemy/testing/suite/test_sequence.py index 55e8e8406..5a1876bc5 100644 --- a/lib/sqlalchemy/testing/suite/test_sequence.py +++ b/lib/sqlalchemy/testing/suite/test_sequence.py @@ -46,7 +46,9 @@ class SequenceTest(fixtures.TablesTest): def test_insert_lastrowid(self, connection): r = connection.execute(self.tables.seq_pk.insert(), data="some data") - eq_(r.inserted_primary_key, [testing.db.dialect.default_sequence_base]) + eq_( + r.inserted_primary_key, (testing.db.dialect.default_sequence_base,) + ) def test_nextval_direct(self, connection): r = connection.execute(self.tables.seq_pk.c.id.default) @@ -57,7 +59,7 @@ class SequenceTest(fixtures.TablesTest): r = connection.execute( self.tables.seq_opt_pk.insert(), data="some data" ) - eq_(r.inserted_primary_key, [1]) + eq_(r.inserted_primary_key, (1,)) def _assert_round_trip(self, table, conn): row = conn.execute(table.select()).first() -- cgit v1.2.1