Complete worked example
python
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, inspect, select, update
from alembic.migration import MigrationContext
from alembic.operations import Operations
def inspect_example(names):
if any(not isinstance(name, str) for name in names):
raise ValueError("names must be strings")
engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
try:
metadata = MetaData()
roles = Table("roles", metadata, Column("id", Integer, primary_key=True),
Column("name", String(100), nullable=False))
metadata.create_all(engine)
with engine.begin() as connection:
if names:
connection.execute(roles.insert(), [{"id": i, "name": n} for i, n in enumerate(names, 1)])
operation = Operations(MigrationContext.configure(connection))
operation.add_column("roles", Column("canonical", String(100), nullable=True))
expanded = Table("roles", MetaData(), autoload_with=connection)
last_id, chunks = 0, 0
while True:
page = connection.execute(select(expanded.c.id, expanded.c.name)
.where(expanded.c.id > last_id).order_by(expanded.c.id).limit(2)).all()
if not page:
break
for role_id, name in page:
canonical = name.strip().casefold()
connection.execute(update(expanded).where(expanded.c.id == role_id).values(canonical=canonical))
last_id = page[-1].id
chunks += 1
with operation.batch_alter_table("roles") as batch:
batch.alter_column("canonical", existing_type=String(100), nullable=False)
column = next(c for c in inspect(connection).get_columns("roles") if c["name"] == "canonical")
labels = list(connection.scalars(select(expanded.c.canonical).order_by(expanded.c.id)))
return {"labels": labels, "nullable": column["nullable"], "chunks": chunks}
finally:
engine.dispose()- Reasoning
- The backfill selects rows with ID greater than the last processed ID, orders by that unique key and limits each page. Every row gets a normalized value, then last_id advances to the final ID actually observed. chunks counts nonempty processed pages, not every SQL statement. The worked normalization strips outer whitespace and casefolds text, preserving internal whitespace. For the fixture containing a padded DATA Engineer, QA and Straße, the output includes data engineer, qa and strasse in ID order. The later nullable flag is inspected from the real schema after all rows have been filled.