On Sun, Jan 15, 2023, at 6:51 AM, Lele Gaifax wrote:
> 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
OK, alembic nor sqlalchemy do not emit BEGIN nor they even know how to do this.
what will make a sqlite-level BEGIN occur is if some kind of SQL commands are
running on the connection, like an INSERT or something. This goes with your
other email that I dont see what this mystery command is, ahead of the
migration running.
you might want to try calling op.get_bind().commit() maybe, or try this feature
called autocommit_block:
https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.migration.MigrationContext.autocommit_block
however, overall, I dont know what's going on here, every migration script
should be starting with a clean transaction for sqlite assuming
transaction_per_migration is ON.
>
> 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.
> [email protected] | -- 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 [email protected]
> <mailto:sqlalchemy%[email protected]>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/87cz7gdnip.fsf%40metapensiero.it.
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/4c41fa34-51ec-45af-b99c-3fb34f24f0f8%40app.fastmail.com.
