I have a python project that used sqlalchemy 1.2.18, alembic 1.0.7 and pysqlcipher3 1.0.3 to manage a local SQLite DB. I recently upgraded the project to python 3.10 and in doing so, updated various packages including sqlalchemy -> 1.4.39, alembic -> 1.13.1 and pysqlcipher3 -> 1.2.0. My application was running fine until I tried to run an autogenerated migration. The migration fails when attempting to create the temporary table. The source of error is an existing column that is not altered in the new migration. The error states the following:

pysqlcipher3.dbapi2.OperationalError: default value of column [existing_col] is not constant.

The existing code for the column in question is the following:

existing_col = sql.Column(
               sql.String(16),
               default="DEF",
               server_default=sql.sql.expression.text("DEF")
               )

The default values are constant so I do not know why the error states that they are not constant.

I’ve found that if I rollback the updates to use alembic==1.0.10, there is no longer an issue with migrations. As soon as I update to alembic==1.0.11, the error returns. When reading the release history, I found the following snippet for 1.0.11 which could be pertinent:
“””
SQLite server default reflection will ensure parenthesis are surrounding a column default expression that is detected as being a non-constant expression, such as a datetime() default, to accommodate for the requirement that SQL expressions have to be parenthesized when being sent as DDL. Parenthesis are not added to constant expressions to allow for maximum cross-compatibility with other dialects and existing test suites (such as Alembic’s), which necessarily entails scanning the expression to eliminate for constant numeric and string values. The logic is added to the two “reflection->DDL round trip” paths which are currently autogenerate and batch migration. Within autogenerate, the logic is on the rendering side, whereas in batch the logic is installed as a column reflection hook.
“””

I’ve tried some suggested solutions including adding single quotes to the server_default (“‘DEF'”),escaping the single quotes (“‘(DEF)'”), adding literal bindings, and deleting the default argument. None of them worked.

The migrations work if I create a new database but this would be inconvenient for app users since they will essentially have their history reset. Any insight as to why the migrations are complaining about non-constant values despite them being constant would be greatly appreciated. Thanks!