From c81c7ff3d59469cf6ceccbcf1593fd0563f0eaf3 Mon Sep 17 00:00:00 2001 From: Michael Trier Date: Mon, 20 Oct 2008 15:21:00 +0000 Subject: [PATCH] Modifications to allow the backends to control the behavior of an empty insert. If supports_empty_insert is True then the backend specifically supports the 'insert into t1 () values ()' syntax. If supports_default_values is True then the backend supports the 'insert into t1 default values' syntax. If both are false then the backend has no support for empty inserts at all and an exception gets raised. Changes here are careful to not change current behavior except where the current behavior was failing to begin with. --- lib/sqlalchemy/databases/mssql.py | 2 ++ lib/sqlalchemy/databases/postgres.py | 2 ++ lib/sqlalchemy/databases/sqlite.py | 21 ++------------------- lib/sqlalchemy/engine/default.py | 3 ++- lib/sqlalchemy/sql/compiler.py | 15 +++++++++++---- test/sql/defaults.py | 18 +++++++++++++++++- 6 files changed, 36 insertions(+), 25 deletions(-) diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py index 1ff482cf56..4bbc2caf77 100644 --- a/lib/sqlalchemy/databases/mssql.py +++ b/lib/sqlalchemy/databases/mssql.py @@ -361,6 +361,8 @@ class MSSQLExecutionContext_pyodbc (MSSQLExecutionContext): class MSSQLDialect(default.DefaultDialect): name = 'mssql' + supports_default_values = True + supports_empty_insert = False colspecs = { sqltypes.Unicode : MSNVarchar, diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 1c410af538..c8abeb6db7 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -302,6 +302,8 @@ class PGDialect(default.DefaultDialect): preexecute_pk_sequences = True supports_pk_autoincrement = False default_paramstyle = 'pyformat' + supports_default_values = True + supports_empty_insert = False def __init__(self, server_side_cursors=False, **kwargs): default.DefaultDialect.__init__(self, **kwargs) diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py index 70c3f74ecb..b3de98100c 100644 --- a/lib/sqlalchemy/databases/sqlite.py +++ b/lib/sqlalchemy/databases/sqlite.py @@ -242,6 +242,8 @@ class SQLiteDialect(default.DefaultDialect): supports_alter = False supports_unicode_statements = True default_paramstyle = 'qmark' + supports_default_values = True + supports_empty_insert = False def __init__(self, **kwargs): default.DefaultDialect.__init__(self, **kwargs) @@ -465,25 +467,6 @@ class SQLiteCompiler(compiler.DefaultCompiler): # sqlite has no "FOR UPDATE" AFAICT return '' - def visit_insert(self, insert_stmt): - self.isinsert = True - colparams = self._get_colparams(insert_stmt) - preparer = self.preparer - - if not colparams: - if not self.dialect.supports_default_values: - raise exc.NotSupportedError( - "The version of SQLite you are using, %s, does not support DEFAULT VALUES." % (self.dialect.dbapi.sqlite_version)) - - return ("INSERT INTO %s DEFAULT VALUES" % ( - (preparer.format_table(insert_stmt.table),))) - else: - return ("INSERT INTO %s (%s) VALUES (%s)" % - (preparer.format_table(insert_stmt.table), - ', '.join([preparer.format_column(c[0]) - for c in colparams]), - ', '.join([c[1] for c in colparams]))) - class SQLiteSchemaGenerator(compiler.SchemaGenerator): diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index a901427029..96dadd0455 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -39,7 +39,8 @@ class DefaultDialect(base.Dialect): supports_pk_autoincrement = True dbapi_type_map = {} default_paramstyle = 'named' - supports_default_values = True + supports_default_values = False + supports_empty_insert = True def __init__(self, convert_unicode=False, assert_unicode=False, encoding='utf-8', paramstyle=None, dbapi=None, **kwargs): self.convert_unicode = convert_unicode diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 0117b96ff2..2072d5a272 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -570,11 +570,18 @@ class DefaultCompiler(engine.Compiled): insert = ' '.join(["INSERT"] + [self.process(x) for x in insert_stmt._prefixes]) - return (insert + " INTO %s (%s) VALUES (%s)" % + if not colparams and not self.dialect.supports_default_values and not self.dialect.supports_empty_insert: + raise exc.NotSupportedError( + "The version of %s you are using does not support empty inserts." % self.dialect.name) + elif not colparams and self.dialect.supports_default_values: + return (insert + " INTO %s DEFAULT VALUES" % ( + (preparer.format_table(insert_stmt.table),))) + else: + return (insert + " INTO %s (%s) VALUES (%s)" % (preparer.format_table(insert_stmt.table), - ', '.join(preparer.quote(c[0].name, c[0].quote) - for c in colparams), - ', '.join(c[1] for c in colparams))) + ', '.join([preparer.format_column(c[0]) + for c in colparams]), + ', '.join([c[1] for c in colparams]))) def visit_update(self, update_stmt): self.stack.append({'from': set([update_stmt.table])}) diff --git a/test/sql/defaults.py b/test/sql/defaults.py index 4f0f929f17..169d60c670 100644 --- a/test/sql/defaults.py +++ b/test/sql/defaults.py @@ -1,8 +1,9 @@ import testenv; testenv.configure_for_tests() import datetime from sqlalchemy import Sequence, Column, func +from sqlalchemy.sql import select, text from testlib import sa, testing -from testlib.sa import MetaData, Table, Integer, String, ForeignKey +from testlib.sa import MetaData, Table, Integer, String, ForeignKey, Boolean from testlib.testing import eq_ from sql import _base @@ -472,6 +473,21 @@ class PKIncrementTest(_base.TablesTest): con.close() +class EmptyInsertTest(testing.TestBase): + @testing.exclude('sqlite', '<', (3, 3, 8), 'no empty insert support') + def test_empty_insert(self): + metadata = MetaData(testing.db) + t1 = Table('t1', metadata, + Column('is_true', Boolean, server_default=('1'))) + metadata.create_all() + + try: + result = t1.insert().execute() + self.assertEquals(1, select([func.count(text('*'))], from_obj=t1).scalar()) + self.assertEquals(True, t1.select().scalar()) + finally: + metadata.drop_all() + class AutoIncrementTest(_base.TablesTest): __requires__ = ('identity',) run_define_tables = 'each' -- 2.47.3