Hi all,
this is loosely related to my other post: modern SA/Alembic transaction
handling changed (unsurprisingly! :-) somewhat, and old migration trick
does not work anymore. I found a way out, but maybe there is a better
approach, who knows!
My app, being SQLite based, installs an event handler to force SQLite
FKs check[0]:
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
As explained in the related Alembic doc[1], I used to disable that check
in some migrations (for example this[2]), something that surely worked
when I applied them in production, nearly two years ago.
Since my last migration something has changed though, and that trick
doesn't work anymore, apparently because for some reason nowadays
Alembic issues a BEGIN well before running the migration script and,
accordingly to SQLite doc[3], that instruction does not work within a
transaction: a new migration like the following, that change an existing
column to be NOT NULL
def upgrade():
op.execute("PRAGMA foreign_keys=OFF")
op.execute("UPDATE clubs SET nationality='wrl' WHERE nationality IS NULL")
with op.batch_alter_table('clubs') as batch:
batch.alter_column('nationality',
existing_type=sa.CHAR(length=3),
nullable=False)
now fails, even running it on a "production equivalent context" that is
still based on SA 1.3 and Alembic 1.7.6: as soon as it "drops" the old table
and is going to rename the new temporary table built by
"batch_alter_table()" the FK check interrupts the process with a
"sqlite3.IntegrityError: FOREIGN KEY constraint failed" error.
I found a possible, less-than-ideal workaround moving that PRAGMA
instruction into the Alembic new, modernized env.py preamble I'm trying
out while upgrading to SA 2:
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
# Disable FK validation, turned on by
models/__init__.py::set_sqlite_pragma()
connection.exec_driver_sql("PRAGMA foreign_keys=OFF")
context.configure(
connection=connection, target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
As this unconditionally affects all migrations, it obviously may ruins
further ones, where the FK check would be desiderable.
Can you suggest some better approach?
Thanks again,
bye, lele.
[0]
https://gitlab.com/metapensiero/SoL/-/blob/master/src/sol/models/__init__.py#L215
[1]
https://alembic.sqlalchemy.org/en/latest/batch.html#dealing-with-referencing-foreign-keys
[2]
https://gitlab.com/metapensiero/SoL/-/blob/master/alembic/versions/d311277a4da7_widen_urls_size_to_128_characters.py#L17
[3] https://www.sqlite.org/pragma.html#pragma_foreign_keys
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it | -- Fortunato Depero, 1929.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/87cz7gdnip.fsf%40metapensiero.it.
