summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/build/changelog/unreleased_14/6254.rst10
-rw-r--r--lib/sqlalchemy/dialects/mysql/base.py5
-rw-r--r--lib/sqlalchemy/dialects/oracle/base.py4
-rw-r--r--lib/sqlalchemy/dialects/postgresql/base.py3
-rw-r--r--lib/sqlalchemy/dialects/sqlite/base.py5
-rw-r--r--lib/sqlalchemy/engine/default.py10
-rw-r--r--lib/sqlalchemy/sql/compiler.py1
-rw-r--r--lib/sqlalchemy/sql/crud.py6
-rw-r--r--lib/sqlalchemy/testing/assertions.py3
-rw-r--r--lib/sqlalchemy/testing/assertsql.py6
-rw-r--r--lib/sqlalchemy/testing/requirements.py10
-rw-r--r--lib/sqlalchemy/testing/suite/test_insert.py15
-rw-r--r--test/engine/test_deprecations.py1
-rw-r--r--test/orm/inheritance/test_basic.py3
-rw-r--r--test/requirements.py5
-rw-r--r--test/sql/test_defaults.py26
-rw-r--r--test/sql/test_insert.py12
17 files changed, 98 insertions, 27 deletions
diff --git a/doc/build/changelog/unreleased_14/6254.rst b/doc/build/changelog/unreleased_14/6254.rst
new file mode 100644
index 000000000..8cdfc1bec
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/6254.rst
@@ -0,0 +1,10 @@
+.. change::
+ :tags: bug, regression, sql, sqlite
+ :tickets: 6254
+
+ Fixed regression where the introduction of the INSERT syntax "INSERT...
+ VALUES (DEFAULT)" was not supported on some backends that do however
+ support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are
+ now each individually supported or non-supported for each dialect, for
+ example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES".
+ Support for Oracle has also been enabled.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 3966126e2..b576fa959 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2534,6 +2534,11 @@ class MySQLDialect(default.DefaultDialect):
supports_for_update_of = False # default for MySQL ...
# ... may be updated to True for MySQL 8+ in initialize()
+ # MySQL doesn't support "DEFAULT VALUES" but *does* support
+ # "VALUES (DEFAULT)"
+ supports_default_values = False
+ supports_default_metavalue = True
+
supports_sane_rowcount = True
supports_sane_multi_rowcount = False
supports_multivalues_insert = True
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 11ad61675..c25cfb781 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1453,7 +1453,11 @@ class OracleDialect(default.DefaultDialect):
requires_name_normalize = True
supports_comments = True
+
+ # Oracle supports these syntaxes but I'm not able to get them
+ # to work with RETURNING which we usually need
supports_default_values = False
+ supports_default_metavalue = True
supports_empty_insert = False
statement_compiler = OracleCompiler
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index d0915a0c9..c6f71c00c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -3079,6 +3079,9 @@ class PGDialect(default.DefaultDialect):
supports_comments = True
supports_default_values = True
+
+ supports_default_metavalue = True
+
supports_empty_insert = False
supports_multivalues_insert = True
default_paramstyle = "pyformat"
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 83c2a8ea7..17cd37b49 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1791,7 +1791,12 @@ class SQLiteDialect(default.DefaultDialect):
supports_alter = False
supports_unicode_statements = True
supports_unicode_binds = True
+
+ # SQlite supports "DEFAULT VALUES" but *does not* support
+ # "VALUES (DEFAULT)"
supports_default_values = True
+ supports_default_metavalue = False
+
supports_empty_insert = False
supports_cast = True
supports_multivalues_insert = True
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index d45b6d7a7..0bd01c588 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -140,8 +140,18 @@ class DefaultDialect(interfaces.Dialect):
supports_sane_multi_rowcount = True
colspecs = {}
default_paramstyle = "named"
+
supports_default_values = False
+ """dialect supports INSERT... DEFAULT VALUES syntax"""
+
+ supports_default_metavalue = False
+ """dialect supports INSERT... VALUES (DEFAULT) syntax"""
+
+ # not sure if this is a real thing but the compiler will deliver it
+ # if this is the only flag enabled.
supports_empty_insert = True
+ """dialect supports INSERT () VALUES ()"""
+
supports_multivalues_insert = False
supports_is_distinct_from = True
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 8eefa10d1..0c701cb52 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3480,6 +3480,7 @@ class SQLCompiler(Compiled):
if (
not crud_params
and not self.dialect.supports_default_values
+ and not self.dialect.supports_default_metavalue
and not self.dialect.supports_empty_insert
):
raise exc.CompileError(
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index 174a1c131..5fa82bcd0 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -174,7 +174,11 @@ def _get_crud_params(compiler, stmt, compile_state, **kw):
values = _extend_values_for_multiparams(
compiler, stmt, compile_state, values, kw
)
- elif not values and compiler.for_executemany:
+ elif (
+ not values
+ and compiler.for_executemany
+ and compiler.dialect.supports_default_metavalue
+ ):
# convert an "INSERT DEFAULT VALUES"
# into INSERT (firstcol) VALUES (DEFAULT) which can be turned
# into an in-place multi values. This supports
diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py
index 02137474b..9ff2f76eb 100644
--- a/lib/sqlalchemy/testing/assertions.py
+++ b/lib/sqlalchemy/testing/assertions.py
@@ -400,6 +400,7 @@ class AssertsCompiledSQL(object):
use_default_dialect=False,
allow_dialect_select=False,
supports_default_values=True,
+ supports_default_metavalue=True,
literal_binds=False,
render_postcompile=False,
schema_translate_map=None,
@@ -410,6 +411,7 @@ class AssertsCompiledSQL(object):
if use_default_dialect:
dialect = default.DefaultDialect()
dialect.supports_default_values = supports_default_values
+ dialect.supports_default_metavalue = supports_default_metavalue
elif allow_dialect_select:
dialect = None
else:
@@ -421,6 +423,7 @@ class AssertsCompiledSQL(object):
elif dialect == "default":
dialect = default.DefaultDialect()
dialect.supports_default_values = supports_default_values
+ dialect.supports_default_metavalue = supports_default_metavalue
elif dialect == "default_enhanced":
dialect = default.StrCompileDialect()
elif isinstance(dialect, util.string_types):
diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py
index 1bdd11585..98261a374 100644
--- a/lib/sqlalchemy/testing/assertsql.py
+++ b/lib/sqlalchemy/testing/assertsql.py
@@ -76,7 +76,11 @@ class CompiledSQL(SQLMatchRule):
def _compile_dialect(self, execute_observed):
if self.dialect == "default":
- return DefaultDialect()
+ dialect = DefaultDialect()
+ # this is currently what tests are expecting
+ # dialect.supports_default_values = True
+ dialect.supports_default_metavalue = True
+ return dialect
else:
# ugh
if self.dialect == "postgresql":
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index f82d5f065..8a70cc692 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -335,11 +335,19 @@ class SuiteRequirements(Requirements):
return exclusions.only_if(
lambda config: config.db.dialect.supports_empty_insert
- or config.db.dialect.supports_default_values,
+ or config.db.dialect.supports_default_values
+ or config.db.dialect.supports_default_metavalue,
"empty inserts not supported",
)
@property
+ def empty_inserts_executemany(self):
+ """target platform supports INSERT with no values, i.e.
+ INSERT DEFAULT VALUES or equivalent, within executemany()"""
+
+ return self.empty_inserts
+
+ @property
def insert_from_select(self):
"""target platform supports INSERT from a SELECT."""
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index 35f3315c7..3c033a774 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -167,8 +167,21 @@ class InsertBehaviorTest(fixtures.TablesTest):
self.tables.autoinc_pk.c.id != None
)
)
+ eq_(len(r.all()), 1)
- assert len(r.fetchall())
+ @requirements.empty_inserts_executemany
+ def test_empty_insert_multiple(self, connection):
+ r = connection.execute(self.tables.autoinc_pk.insert(), [{}, {}, {}])
+ assert r._soft_closed
+ assert not r.closed
+
+ r = connection.execute(
+ self.tables.autoinc_pk.select().where(
+ self.tables.autoinc_pk.c.id != None
+ )
+ )
+
+ eq_(len(r.all()), 3)
@requirements.insert_from_select
def test_insert_from_select_autoinc(self, connection):
diff --git a/test/engine/test_deprecations.py b/test/engine/test_deprecations.py
index a08725921..fce9946dd 100644
--- a/test/engine/test_deprecations.py
+++ b/test/engine/test_deprecations.py
@@ -1589,6 +1589,7 @@ class DeprecatedExecParamsTest(fixtures.TablesTest):
[(5, "some name"), (6, "some other name")],
)
+ @testing.requires.empty_inserts
def test_single_scalar(self, connection):
users = self.tables.users_autoinc
diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py
index 8769d2b39..1b4a367ac 100644
--- a/test/orm/inheritance/test_basic.py
+++ b/test/orm/inheritance/test_basic.py
@@ -1835,7 +1835,8 @@ class JoinedNoFKSortingTest(fixtures.MappedTest):
testing.db,
sess.flush,
Conditional(
- testing.db.dialect.insert_executemany_returning,
+ testing.db.dialect.insert_executemany_returning
+ and testing.db.dialect.supports_default_metavalue,
[
CompiledSQL(
"INSERT INTO a (id) VALUES (DEFAULT)", [{}, {}, {}, {}]
diff --git a/test/requirements.py b/test/requirements.py
index eca9e0518..29dd55b45 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -847,6 +847,11 @@ class DefaultRequirements(SuiteRequirements):
)
@property
+ def empty_inserts_executemany(self):
+ # waiting on https://jira.mariadb.org/browse/CONPY-152
+ return skip_if(["mariadb+mariadbconnector"]) + self.empty_inserts
+
+ @property
def expressions_against_unbounded_text(self):
"""target database supports use of an unbounded textual field in a
WHERE clause."""
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 543ae1f98..007dc157b 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -2,7 +2,6 @@ import datetime
import itertools
import sqlalchemy as sa
-from sqlalchemy import Boolean
from sqlalchemy import cast
from sqlalchemy import DateTime
from sqlalchemy import exc
@@ -1062,25 +1061,6 @@ class PKIncrementTest(fixtures.TablesTest):
)
-class EmptyInsertTest(fixtures.TestBase):
- __backend__ = True
-
- @testing.fails_on("oracle", "FIXME: unknown")
- def test_empty_insert(self, metadata, connection):
- t1 = Table(
- "t1",
- metadata,
- Column("is_true", Boolean, server_default=("1")),
- )
- metadata.create_all(connection)
- connection.execute(t1.insert())
- eq_(
- 1,
- connection.scalar(select(func.count(text("*"))).select_from(t1)),
- )
- eq_(True, connection.scalar(t1.select()))
-
-
class AutoIncrementTest(fixtures.TestBase):
__backend__ = True
@@ -1088,7 +1068,11 @@ class AutoIncrementTest(fixtures.TestBase):
@testing.requires.empty_inserts
def test_autoincrement_single_col(self, metadata, connection):
single = Table(
- "single", self.metadata, Column("id", Integer, primary_key=True)
+ "single",
+ self.metadata,
+ Column(
+ "id", Integer, primary_key=True, test_needs_autoincrement=True
+ ),
)
self.metadata.create_all(connection)
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index a128db8a9..95a8d02a2 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -951,7 +951,9 @@ class EmptyTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
table1 = self.tables.mytable
dialect = default.DefaultDialect()
- dialect.supports_empty_insert = dialect.supports_default_values = True
+ dialect.supports_empty_insert = False
+ dialect.supports_default_values = True
+ dialect.supports_default_metavalue = True
stmt = table1.insert().values({})
self.assert_compile(
@@ -961,6 +963,14 @@ class EmptyTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
for_executemany=True,
)
+ dialect.supports_default_metavalue = False
+ self.assert_compile(
+ stmt,
+ "INSERT INTO mytable DEFAULT VALUES",
+ dialect=dialect,
+ for_executemany=True,
+ )
+
def test_supports_empty_insert_false(self):
table1 = self.tables.mytable
View Lorry Controller Status