Python sqlalchemy.exc.CompileError() Examples

def test_server_default_absent_value(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, server_default=func.foobar()),
        )

        values = [
            {"id": 1, "data": "data1", "foo": "plainfoo"},
            {"id": 2, "data": "data2"},
            {"id": 3, "data": "data3", "foo": "otherfoo"},
        ]

        assert_raises_message(
            exc.CompileError,
            "INSERT value for column sometable.foo is explicitly rendered "
            "as a boundparameter in the VALUES clause; a Python-side value or "
            "SQL expression is required",
            table.insert().values(values).compile,
        ) 

def test_on_conflict_clause_check_constraint_from_column(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column(
                "x",
                Integer,
                CheckConstraint("x > 1", sqlite_on_conflict="FAIL"),
            ),
        )

        assert_raises_message(
            exc.CompileError,
            "SQLite does not support on conflict "
            "clause for column check constraint",
            CreateTable(t).compile,
            dialect=sqlite.dialect(),
        ) 

def test_colliding_col_label_from_index_flag_no_conv(self):
        t1 = self._colliding_name_fixture({"ck": "foo"}, {"index": True})

        idx = list(t1.indexes)[0]

        # this behavior needs to fail, as of #4911 since we are testing it,
        # ensure it raises a CompileError.  In #4289 we may want to revisit
        # this in some way, most likely specifically to Postgresql only.
        assert_raises_message(
            exc.CompileError,
            "CREATE INDEX requires that the index have a name",
            CreateIndex(idx).compile,
        )

        assert_raises_message(
            exc.CompileError,
            "DROP INDEX requires that the index have a name",
            DropIndex(idx).compile,
        ) 

def test_limit_offset_no_int_coercion_three(self):
        exp1 = bindparam("Q")
        exp2 = bindparam("Y")
        sel = select([1]).limit(exp1).offset(exp2)

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for limit",
            getattr,
            sel,
            "_limit",
        )

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for offset",
            getattr,
            sel,
            "_offset",
        ) 

def test_columns_augmented_sql_illegal_label_reference(self):
        User, Address = self.classes.User, self.classes.Address

        sess = create_session()

        q = sess.query(User.id, User.name.label("foo"), Address.id).distinct(
            "not a label"
        )

        from sqlalchemy.dialects import postgresql

        assert_raises_message(
            sa_exc.CompileError,
            "Can't resolve label reference for ORDER BY / "
            "GROUP BY / DISTINCT etc.",
            q.with_labels().statement.compile,
            dialect=postgresql.dialect(),
        ) 

def test_limit_offset_no_int_coercion_two(self):
        exp1 = literal_column("Q")
        exp2 = literal_column("Y")
        sel = select([1]).limit(exp1).offset(exp2)

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for limit",
            getattr,
            sel,
            "_limit",
        )

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for offset",
            getattr,
            sel,
            "_offset",
        ) 

def test_order_by_w_eager_five(self):
        """essentially the same as test_eager_relations -> test_limit_3,
        but test for textual label elements that are freeform.
        this is again #3392."""

        User = self.classes.User
        Address = self.classes.Address

        sess = create_session()

        q = sess.query(User, Address.email_address.label("email_address"))

        result = (
            q.join("addresses")
            .options(joinedload(User.orders))
            .order_by("email_address desc")
            .limit(1)
            .offset(0)
        )

        assert_raises_message(
            sa_exc.CompileError,
            "Can't resolve label reference for ORDER BY / GROUP BY",
            result.all,
        ) 

def post_create_table(self, table):
        raw_connection = table.bind.raw_connection()
        # TODO Supports orc, avro, json, csv or tsv format
        text = "STORED AS PARQUET\n"

        location = (
            raw_connection._driver_kwargs["s3_dir"]
            if "s3_dir" in raw_connection._driver_kwargs
            else raw_connection.s3_staging_dir
        )
        if not location:
            raise exc.CompileError(
                "`s3_dir` or `s3_staging_dir` parameter is required"
                " in the connection string."
            )
        text += "LOCATION '{0}{1}/{2}/'\n".format(location, table.schema, table.name)

        compression = raw_connection._driver_kwargs.get("compression")
        if compression:
            text += "TBLPROPERTIES ('parquet.compress'='{0}')\n".format(
                compression.upper()
            )

        return text 

def test_fk_illegal_sql_phrases(self):
        a = Table("a", MetaData(), Column("q", Integer))
        b = Table("b", MetaData(), Column("p", Integer))

        for kw in ("onupdate", "ondelete", "initially"):
            for phrase in (
                "NOT SQL",
                "INITALLY NOT SQL",
                "FOO RESTRICT",
                "CASCADE WRONG",
                "SET  NULL",
            ):
                const = schema.AddConstraint(
                    schema.ForeignKeyConstraint(
                        [a.c.q], [b.c.p], **{kw: phrase}
                    )
                )
                assert_raises_message(
                    exc.CompileError,
                    r"Unexpected SQL phrase: '%s'" % phrase,
                    const.compile,
                ) 

def create_query(cls, sql: str, params: Dict[str, Any]) -> str:
        text_sql: TextClause = text(sql)
        kwargs = {'dialect': cls.DIALECT, 'compile_kwargs': {"literal_binds": True}}
        try:
            return str(
                text_sql.bindparams(
                    **{k: null() if v is None else v for k, v in params.items()}
                ).compile(**kwargs)
            )
        except CompileError as e:
            invalid_param_match = re.match(INVALID_PARAMETER_MESSAGE, e.args[0])
            if invalid_param_match:  # pragma: no cover
                raise BadRequestException(
                    message=f'Cannot find parameter: {invalid_param_match.group(1)}'
                )
            raise  # pragma: no cover
        except ArgumentError as e:
            undefined_param_match = re.match(UNDEFINED_PARAMETER_MESSAGE, e.args[0])
            if undefined_param_match:  # pragma: no cover
                undefined_param: str = undefined_param_match.group(1)
                return cls.create_query(
                    sql, {k: v for k, v in params.items() if k != undefined_param}
                )
            raise  # pragma: no cover 

def get_column_specification(self, column, **kwargs):

        if column.table is None:
            raise exc.CompileError(
                "Teradata requires Table-bound columns "
                "in order to generate DDL")

        colspec = (self.preparer.format_column(column) + " " +\
                        self.dialect.type_compiler.process(
                          column.type, type_expression=column))

        # Null/NotNull
        if column.nullable is not None:
            if not column.nullable or column.primary_key:
                colspec += " NOT NULL"

        return colspec 

def test_match_kw_raises(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            m,
            Column(
                "id",
                Integer,
                ForeignKey("t1.id", match="XYZ"),
                primary_key=True,
            ),
        )

        assert_raises_message(
            exc.CompileError,
            "MySQL ignores the 'MATCH' keyword while at the same time causes "
            "ON UPDATE/ON DELETE clauses to be ignored.",
            schema.CreateTable(t2).compile,
            dialect=mysql.dialect(),
        ) 

def test_varchar_raise(self, type_):
        type_ = sqltypes.to_instance(type_)
        assert_raises_message(
            exc.CompileError,
            "VARCHAR requires a length on dialect mysql",
            type_.compile,
            dialect=mysql.dialect(),
        )

        t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
        assert_raises_message(
            exc.CompileError,
            r"\(in table 'sometable', column 'somecolumn'\)\: "
            r"(?:N)?VARCHAR requires a length on dialect mysql",
            schema.CreateTable(t1).compile,
            dialect=mysql.dialect(),
        ) 

def visit_column_default(element, compiler, **kw):
    if sqla_compat.has_computed and (
        isinstance(element.default, sqla_compat.Computed)
        or isinstance(element.existing_server_default, sqla_compat.Computed)
    ):
        raise exc.CompileError(
            'Adding or removing a "computed" construct, e.g. GENERATED '
            "ALWAYS AS, to or from an existing column is not supported."
        )

    return "%s %s %s" % (
        alter_table(compiler, element.table_name, element.schema),
        alter_column(compiler, element.column_name),
        "SET DEFAULT %s" % format_server_default(compiler, element.default)
        if element.default is not None
        else "DROP DEFAULT",
    ) 

def post_create_table(self, table):
        raw_connection = table.bind.raw_connection()
        # TODO Supports orc, avro, json, csv or tsv format
        text = "STORED AS PARQUET\n"

        location = (
            raw_connection._kwargs["s3_dir"]
            if "s3_dir" in raw_connection._kwargs
            else raw_connection.s3_staging_dir
        )
        if not location:
            raise exc.CompileError(
                "`s3_dir` or `s3_staging_dir` parameter is required"
                " in the connection string."
            )
        schema = table.schema if table.schema else raw_connection.schema_name
        text += "LOCATION '{0}{1}/{2}/'\n".format(location, schema, table.name)

        compression = raw_connection._kwargs.get("compression")
        if compression:
            text += "TBLPROPERTIES ('parquet.compress'='{0}')\n".format(
                compression.upper()
            )

        return text 

def get_column_specification(self, column, **kwargs):
        colspec = self.preparer.format_column(column) + " " + \
                        self.dialect.type_compiler.process(column.type)

        if column.table is None:
            raise exc.CompileError(
                        "The Sybase dialect requires Table-bound "
                       "columns in order to generate DDL")
        seq_col = column.table._autoincrement_column

        # install a IDENTITY Sequence if we have an implicit IDENTITY column
        if seq_col is column:
            sequence = isinstance(column.default, sa_schema.Sequence) \
                                    and column.default
            if sequence:
                start, increment = sequence.start or 1, \
                                    sequence.increment or 1
            else:
                start, increment = 1, 1
            if (start, increment) == (1, 1):
                colspec += " IDENTITY"
            else:
                # TODO: need correct syntax for this
                colspec += " IDENTITY(%s,%s)" % (start, increment)
        else:
            default = self.get_column_default_string(column)
            if default is not None:
                colspec += " DEFAULT " + default

            if column.nullable is not None:
                if not column.nullable or column.primary_key:
                    colspec += " NOT NULL"
                else:
                    colspec += " NULL"

        return colspec 

def _illegal_type_fixture(self):
        class MyType(types.TypeEngine):
            pass

        @compiles(MyType)
        def compile_(element, compiler, **kw):
            raise exc.CompileError("Couldn't compile type")

        return MyType 

def bindparam_string(self, name, **kw):
        quote = getattr(name, 'quote', None)
        if quote is True or quote is not False and \
                self.preparer._bindparam_requires_quotes(name):
            if kw.get('expanding', False):
                raise exc.CompileError(
                    "Can't use expanding feature with parameter name "
                    "%r on Oracle; it requires quoting which is not supported "
                    "in this context." % name)
            quoted_name = '"%s"' % name
            self._quoted_bind_names[name] = quoted_name
            return OracleCompiler.bindparam_string(self, quoted_name, **kw)
        else:
            return OracleCompiler.bindparam_string(self, name, **kw) 

def visit_VARCHAR(self, type_):
        if not type_.length:
            raise exc.CompileError(
                    "VARCHAR requires a length on dialect %s" %
                    self.dialect.name)
        basic = super(FBTypeCompiler, self).visit_VARCHAR(type_)
        return self._extend_string(type_, basic) 

def get_column_specification(self, column, **kwargs):
        colspec = self.preparer.format_column(column) + " " + \
            self.dialect.type_compiler.process(
                column.type, type_expression=column)

        if column.table is None:
            raise exc.CompileError(
                "The Sybase dialect requires Table-bound "
                "columns in order to generate DDL")
        seq_col = column.table._autoincrement_column

        # install a IDENTITY Sequence if we have an implicit IDENTITY column
        if seq_col is column:
            sequence = isinstance(column.default, sa_schema.Sequence) \
                and column.default
            if sequence:
                start, increment = sequence.start or 1, \
                    sequence.increment or 1
            else:
                start, increment = 1, 1
            if (start, increment) == (1, 1):
                colspec += " IDENTITY"
            else:
                # TODO: need correct syntax for this
                colspec += " IDENTITY(%s,%s)" % (start, increment)
        else:
            default = self.get_column_default_string(column)
            if default is not None:
                colspec += " DEFAULT " + default

            if column.nullable is not None:
                if not column.nullable or column.primary_key:
                    colspec += " NOT NULL"
                else:
                    colspec += " NULL"

        return colspec 

def test_raise_expr_no_column(self):
        idx = Index("foo", func.lower(5))

        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
            dialect=testing.db.dialect,
        )
        assert_raises_message(
            exc.CompileError,
            "Index 'foo' is not associated with any table.",
            schema.CreateIndex(idx).compile,
        ) 

def test_delayed_col_naming(self):
        my_str = Column(String)

        sel1 = select([my_str])

        assert_raises_message(
            exc.InvalidRequestError,
            "Cannot initialize a sub-selectable with this Column",
            lambda: sel1.subquery().c,
        )

        # calling label or scalar_subquery doesn't compile
        # anything.
        sel2 = select([func.substr(my_str, 2, 3)]).label("my_substr")

        assert_raises_message(
            exc.CompileError,
            "Cannot compile Column object until its 'name' is assigned.",
            sel2.compile,
            dialect=default.DefaultDialect(),
        )

        sel3 = select([my_str]).scalar_subquery()
        assert_raises_message(
            exc.CompileError,
            "Cannot compile Column object until its 'name' is assigned.",
            sel3.compile,
            dialect=default.DefaultDialect(),
        )

        my_str.name = "foo"

        self.assert_compile(sel1, "SELECT foo")
        self.assert_compile(
            sel2, "(SELECT substr(foo, :substr_2, :substr_3) AS substr_1)"
        )

        self.assert_compile(sel3, "(SELECT foo)") 

def test_columnadapter_anonymized(self):
        """test issue #3148

        Testing the anonymization applied from the ColumnAdapter.columns
        collection, typically as used in eager loading.

        """
        exprs = [
            table1.c.myid,
            table1.c.name.label("t1name"),
            func.foo("hoho").label("x"),
        ]

        ta = table1.alias()
        adapter = sql_util.ColumnAdapter(ta, anonymize_labels=True)

        s1 = (
            select([adapter.columns[expr] for expr in exprs])
            .apply_labels()
            .order_by("myid", "t1name", "x")
        )

        assert_raises_message(
            exc.CompileError,
            r"Can't resolve label reference for ORDER BY / GROUP BY / "
            "DISTINCT etc. "
            "Textual SQL "
            "expression 't1name' should be explicitly "
            r"declared as text\('t1name'\)",
            s1.compile,
        ) 

def _test_exception(self, stmt, offending_clause, dialect=None):
        assert_raises_message(
            exc.CompileError,
            r"Can't resolve label reference for ORDER BY / GROUP BY / "
            "DISTINCT etc. "
            "Textual SQL "
            "expression %r should be explicitly "
            r"declared as text\(%r\)" % (offending_clause, offending_clause),
            stmt.compile,
            dialect=dialect,
        ) 

def test_unary_no_ops(self):
        assert_raises_message(
            exc.CompileError,
            "Unary expression has no operator or modifier",
            UnaryExpression(literal("x")).compile,
        ) 

def test_not_supported(self):
        table1 = self.tables.mytable

        dialect = default.DefaultDialect()
        stmt = table1.insert().values([{"myid": 1}, {"myid": 2}])
        assert_raises_message(
            exc.CompileError,
            "The 'default' dialect with current database version settings "
            "does not support in-place multirow inserts.",
            stmt.compile,
            dialect=dialect,
        ) 

def test_supports_empty_insert_false(self):
        table1 = self.tables.mytable

        dialect = default.DefaultDialect()
        dialect.supports_empty_insert = dialect.supports_default_values = False

        stmt = table1.insert().values({})  # hide from 2to3
        assert_raises_message(
            exc.CompileError,
            "The 'default' dialect with current database version "
            "settings does not support empty inserts.",
            stmt.compile,
            dialect=dialect,
        ) 

def test_insert_returning_not_in_default(self):
        table1 = self.tables.mytable

        stmt = table1.insert().returning(table1.c.myid)
        assert_raises_message(
            exc.CompileError,
            "RETURNING is not supported by this dialect's statement compiler.",
            stmt.compile,
            dialect=default.DefaultDialect(),
        ) 

def test_unconsumed_names_values_dict(self):
        table1 = self.tables.mytable

        checkparams = {"myid": 3, "name": "jack", "unknowncol": "oops"}

        stmt = insert(table1, values=checkparams)
        assert_raises_message(
            exc.CompileError,
            "Unconsumed column names: unknowncol",
            stmt.compile,
            dialect=postgresql.dialect(),
        ) 

def test_unconsumed_names_kwargs(self):
        t = table("t", column("x"), column("y"))
        assert_raises_message(
            exc.CompileError,
            "Unconsumed column names: z",
            t.insert().values(x=5, z=5).compile,
        )