SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillssqlalchemy-azure-sql

sqlalchemy-azure-sql

Data & analytics

View repository

Updated 10-16 00:31

About this skill

SQLAlchemy on Azure SQL Database

SQLAlchemy reaches Azure SQL Database through a dialect whose generated SQL differs from every other backend in one specific way, and almost every surprise here traces back to it. Every Python block below was verified on 2026-09-03 against SQLAlchemy 2.0.52 and pyodbc 5.3.0 and its output quoted verbatim; the version boundary below was measured the same day against 2.0.8, 2.0.9 and 2.0.10. Engine error numbers are from Microsoft Learn.

The dialect is named mssql and that name belongs only in the URL scheme. Everywhere else the product is Azure SQL Database, and an agent that adopts the dialect name has begun giving advice for a different product with different limits.

The engine URL

import os
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

credential = { # read them, never inline them "username": os.environ["SQL_USER"], "password": os.environ["SQL_PASSWORD"], } url = URL.create( "mssql+pyodbc", host=os.environ["SQL_HOST"], # .database.windows.net port=1433, database=os.environ["SQL_DATABASE"], query={"driver": "ODBC Driver 18 for SQL Server"}, **credential, ) engine = create_engine(url, pool_pre_ping=True) print(url.render_as_string(hide_password=True))


`URL.create` escapes the characters that break a hand built URL; `render_as_string` logs one with
the password back as `***`.

Driver choice, retry and passwordless connections belong to other skills; see References.

## The OUTPUT clause, which is the whole skill

To learn a server generated key the dialect adds an OUTPUT clause to the INSERT. Do not take that
on trust, compile it:

```python
from sqlalchemy import Column, Integer, String, insert
from sqlalchemy.dialects import mssql
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):                       # columns shared by both models
    id = Column(Integer, primary_key=True)
    sku = Column(String(20))

class Order(Base):
    __tablename__ = "orders"

class Audited(Base):                               # this one carries a trigger
    __tablename__ = "audited_orders"
    __table_args__ = {"implicit_returning": False}  # so: no OUTPUT clause

d = mssql.dialect()
print(insert(Order).values(sku="x").compile(dialect=d))
print(insert(Audited).values(sku="x").compile(dialect=d))
print(insert(Audited).values(sku="x").returning(Audited.id).compile(dialect=d))

Three lines out, and every claim below is one:

INSERT INTO orders (sku) OUTPUT inserted.id VALUES (:sku)
INSERT INTO audited_orders (sku) VALUES (:sku)
INSERT INTO audited_orders (sku) OUTPUT inserted.id VALUES (:sku)

Azure SQL Database refuses the first when the target carries an enabled trigger:

Msg 334, Level 15, State 1
The target table 'dbo.audited_orders' of the DML statement cannot have any enabled triggers
if the statement contains an OUTPUT clause without INTO clause.

Four properties decide how this plays out, and each is where agents guess wrong.

  1. It is per table, and nothing detects it. The dialect has no trigger reflection and does not probe for one, so a codebase where 40 models work and one fails is the normal shape of the bug.
  2. The switch is on the table, not the engine, as __table_args__ above or implicit_returning=False passed to Table in Core. The key then comes back from scope_identity(), one extra round trip that works on a triggered table. Set it per table, not everywhere as a precaution: it costs the batched insert path on tables that never needed it.
  3. The engine level parameter is a trap. create_engine(url, implicit_returning=True) and the False form are both accepted, both raise SADeprecationWarning, and neither changes one character of the SQL above.
  4. The flag does not cover an explicit returning(). That is the third printed line: with the flag off, a hand written insert().returning(...) still compiles to OUTPUT without INTO, and update().returning(...) and delete().returning(...) do the same.

Triggers cluster on auditing and history tables, exactly where an ORM meets an existing schema. Find them before mapping one, with the query under Check it worked.

fast_executemany, and what changed in 2.0

Same cause as the trigger error above: the generated returning clause. An ORM insert of a mapped class with a server generated key carries one, because that is the OUTPUT clause, and that is the case the flag does not reach, so by default it changes nothing for exactly the bulk inserts people set it for.

fast_executemany=True has existed since SQLAlchemy 1.3; what changed is what it does, and the boundary is measurable with no database:

python3 -c "import sqlalchemy; from sqlalchemy.dialects import mssql; \
print(sqlalchemy.__version__, mssql.dialect().use_insertmanyvalues)"

That prints 2.0.8 True, 2.0.9 False, 2.0.10 True. SQLAlchemy 2.0 introduced insertmanyvalues, a batched INSERT form that returns keys, and because it returns keys it took precedence over the driver's array binding and the flag stopped having any effect. 2.0.9 gave that effect back for multi-parameter INSERT statements carrying no returning clause, switching insertmanyvalues off here entirely to do it; 2.0.10 restored it, so 2.0.10 is the floor.

So the flag bites only where the INSERT carries no returning clause: the rows already hold their keys, or the table is declared implicit_returning=False, or the load goes through Core rather than tracked ORM objects.

Documented costs make this a decision, not a default: the batch must fit in memory, the parameter is honoured for the Microsoft ODBC driver only, and setinputsizes is not used there, which is where the surprising type handling on large loads comes from.

Type mapping, before the first migration

from sqlalchemy import Column, MetaData, String, Table, Text, Unicode, UnicodeText
from sqlalchemy.dialects import mssql
from sqlalchemy.schema import CreateTable

t = Table("t", MetaData(),
          Column("a", String(50)), Column("b", Unicode(50)),
          Column("c", Text()), Column("d", UnicodeText()))
print(CreateTable(t).compile(dialect=mssql.dialect()))                            # not connected
print(CreateTable(t).compile(dialect=mssql.dialect(deprecate_large_types=True)))  # connected

The second print is what the ORM creates:

a VARCHAR(50) NULL, b NVARCHAR(50) NULL, c VARCHAR(max) NULL, d NVARCHAR(max) NULL

String is not Unicode. A model that uses it for names, addresses or user text gets a non-Unicode column, and the mismatch between it and the Unicode parameter Python binds is the implicit conversion that stops an index being used. Prefer Unicode or NVARCHAR(n) explicitly.

That the two prints differ is the second trap. Offline, deprecate_large_types is None and c and d compile to TEXT and NTEXT; on first connect the dialect sets it from the server version, and Azure SQL Database is always past the threshold. Compiling offline is the normal way to inspect DDL and wrong for exactly this pair, so never report a TEXT or NTEXT read out of a compiled statement as what will be created.

One default worth pinning: Sequence("s") with no start compiles to a bare CREATE SEQUENCE s, and Microsoft Learn gives an ascending sequence's default start as the minimum value of its type, -9223372036854775808 for the default bigint. Pass start=1, and prefer an identity column for surrogate keys.

Alembic

Alembic drives the same dialect, so all the above applies to the DDL it emits. Read that DDL before it runs, which is the point of offline mode:

alembic revision --autogenerate -m "add audited_orders"
alembic upgrade head --sql > migration.sql     # writes SQL, runs nothing
grep -nE 'TEXT|NTEXT|VARCHAR\(' migration.sql  # the type mapping above, before it ships
alembic upgrade head

Three operations need arguments here that they need nowhere else, and autogenerate will not add them:

  • alter_column requires existing_type when changing nullability.
  • drop_index requires table_name.
  • drop_column on a column with a DEFAULT, CHECK or single foreign key constraint takes mssql_drop_default, mssql_drop_check or mssql_drop_foreign_key to drop the unnamed constraint first.

These come from the Alembic documentation, not from a run: Alembic was not installed on the machine that produced the numbers above. Whether a migration is safe to run at all, expand and contract, and never migrating on startup, are schema-migrations-safely.

Check it worked

Three checks, in the order they catch things. Which tables carry an enabled trigger is first, because nothing in Python will tell you:

sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 -Q \
  "SELECT OBJECT_SCHEMA_NAME(parent_id) + '.' + OBJECT_NAME(parent_id) AS triggered_table
   FROM sys.triggers WHERE parent_class = 1 AND is_disabled = 0 ORDER BY 1;"

Expect one row per table needing implicit_returning=False, none on a schema with no triggers. Keep -m-1: a severity 10 message otherwise prints with no Msg number, and -b will not fail on one.

-m-1 is an ODBC sqlcmd instruction, meaning the 18.x build from mssql-tools18 or the Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build brew install sqlcmd and winget install sqlcmd install, prints no Msg header on a severity 10 message at any -m value, so on that build a severity 10 message prints with no number whether you keep the flag or not. Query sys.messages or use the ODBC build when the number matters.

Every model that needs the flag has it. Print the other list and compare:

print(sorted(t.name for t in Base.metadata.tables.values() if t.implicit_returning))

Table.implicit_returning is True by default and False once set, so expect no name from the sqlcmd output here. Any that appears is the next Msg 334.

The columns the engine actually created. Run this against the migrated database:

SELECT OBJECT_NAME(c.object_id) AS tbl, c.name AS col, t.name AS type_name
FROM sys.columns AS c JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE OBJECTPROPERTY(c.object_id, 'IsUserTable') = 1
  AND t.name IN ('varchar', 'char', 'text', 'ntext');

Expect zero rows anywhere text can hold a non-ASCII character. A varchar or char row is a String column, a text or ntext row is DDL from a dialect that never connected, and the engine accepts both without complaint, which is why nothing else catches them.

Do not

  • Do not claim fast_executemany=True sped up an ORM bulk insert without first checking that the statement carries no returning clause. On the default configuration it did not.
  • Do not inline a credential in an engine URL, and do not call the product mssql.

References

  • SQLAlchemy Microsoft SQL Server dialect: triggers, identity, sequences, fast_executemany, setinputsizes. Fetch it before asserting what the dialect emits.
  • insertmanyvalues: what the batched form generates and how to turn it off. Read it when a bulk load is slow or noisy.
  • The OUTPUT clause: the restriction in the engine's own words, per DML action. Read it to confirm the rule, not the text.
  • Alembic operations reference: the dialect-specific arguments above. Read it while writing a migration that alters or drops a column.
  • connect-from-python: driver choice, installation, pooling. Open it before writing the URL; the first-party driver has a dialect only from SQLAlchemy 2.1.0b2, not for production.
  • connect-to-azure-sql: encryption, retry, pool sizing. Open it the moment a transient error appears: pool_pre_ping tests a connection at checkout and is not a retry policy.
  • entra-id-auth: passwordless connections, which carry no user, no password and no Trusted_Connection. Open it before putting a token flow near the engine URL.
  • design-azure-sql-schema: key length, sizing, collation. Open it before the first migration.
  • schema-migrations-safely: the migration doctrine Alembic inherits, including why a pipeline can report success on a migration that only warned.