sqlalchemy/alembic · error · TypeError

SQL parameters not allowed with as_sql

Error message

SQL parameters not allowed with as_sql

What it means

DefaultImpl._exec raises TypeError when SQL parameters (params or multiparams) are supplied while the implementation is in as_sql (offline) mode. Offline mode outputs literal SQL text to a script file and cannot bind runtime parameters, so passing bound parameters is disallowed.

Solutions

1. Use literal_binds so parameters are inlined: compile the statement with compile_kwargs={'literal_binds': True} before executing offline.
2. Run the migration online (without --sql) so bound parameters execute normally.
3. For bulk_insert in offline mode, ensure literal_binds is enabled in the migration context configuration.

Example fix

# before (offline --sql)
op.execute(text('UPDATE t SET c = :v'), params={'v': 5})

# after: inline literals
from sqlalchemy import bindparam
op.execute('UPDATE t SET c = 5')

from alembic import context

def safe_execute(op, stmt, params=None):
    if context.is_offline_mode() and params:
        raise RuntimeError('Cannot use params in offline mode; inline literals')
    op.execute(stmt, *([params] if params else []))

def is_offline(impl) -> bool:
    return bool(getattr(impl, 'as_sql', False))

When it happens

Trigger: Running migrations with `alembic upgrade head --sql` (or migration_context configured with as_sql=True) and executing a statement that carries params/multiparams, e.g. bulk_insert or op.execute with bound parameters in offline mode.

Common situations: Data migrations using op.bulk_insert or parameterized op.execute run via --sql in CI; tests that exercise the same migration both online and offline.

Related errors

• Can't send params and multiparams at the same time
• Cannot call run_async in SQL mode
• Can't create table in batch mode
• can't return inspector as this AutogenContext has no…
• Individual alter column constructs not supported by MySQL

AI-assisted analysis of sqlalchemy/alembic@5551b5d35f (2026-08-11). Data as JSON: /api/errors/0bc6e00142b7ba20. Report an issue: GitHub.

Appendix: source

Thrown at alembic/ddl/impl.py:220

        """

    @property
    def bind(self) -> Connection | None:
        return self.connection

    def _exec(
        self,
        construct: Executable | str,
        execution_options: Mapping[str, Any] | None = None,
        multiparams: Sequence[Mapping[str, Any]] | None = None,
        params: Mapping[str, Any] = util.immutabledict(),
    ) -> CursorResult | None:
        if isinstance(construct, str):
            construct = text(construct)
        if self.as_sql:
            if multiparams is not None or params:
                raise TypeError("SQL parameters not allowed with as_sql")

            compile_kw: dict[str, Any]
            if self.literal_binds and not isinstance(
                construct, schema.DDLElement
            ):
                compile_kw = dict(compile_kwargs={"literal_binds": True})
            else:
                compile_kw = {}

            if TYPE_CHECKING:
                assert isinstance(construct, ClauseElement)
            compiled = construct.compile(dialect=self.dialect, **compile_kw)
            self.static_output(
                str(compiled).replace("\t", "    ").strip()
                + self.command_terminator
            )
            return None
        else:

View on GitHub (pinned to 5551b5d35f)