support sqlite date casts in batch mode under sqlite.cast_for_batch_migrate, or at least a hook

[paoloalba]

I am trying to migrate a column from type Date to type Datetime. Here the migration script:

from alembic import op import sqlalchemy as sa

def upgrade(): with op.batch_alter_table('col_1', schema=None) as batch_op: batch_op.alter_column('date', existing_type=sa.DATE, type_=sa.DateTime, existing_nullable=False)

I would expect the new column to contain text of the ISO8601 format ( I am operating on an SQLite database).

Running the upgrade command the migration does not throw any exception. Here the stdout: INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> 0a2d90c1423d, empty message

The modified column after contains only the year instead, i.e. 4 characters.

I am running:

• OS: Ubuntu 20.04.4 LTS
• Python: 3.9.12
• Alembic: 1.7.7
• SQLAlchemy: 1.4.36

[zzzeek]

SQLite considers cast(value, datetime) to just chop off all the data and return the first token:

sqlite> select cast("2003-12-17" as datetime);
2003

this is one of many unfortunate behaviors of SQLite.

we don't have a hook in "batch" to override the simple CAST that the SQLite dialect outputs when types are changed in batch mode, that would be at least a first step to covering this. implementing string operations for movement between date/datetime/time in all directions would be nicer, but would not be customizable for those using custom date/time formats, plus given things like timezones etc., it seems likely that a hook for this cast should be provided so that the user can implement how they'd like this conversion to proceed.

All of that said, you still can get a "hook" here by overriding the compilation for Cast itself, so here is a recipe I have tested that you can use for now.

import sqlalchemy as sa

from alembic import op


# revision identifiers, used by Alembic.
revision = "b426103b44ee"
down_revision = "69c845b93a13"
branch_labels = None
depends_on = None


from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Cast, type_coerce
from sqlalchemy import Date, DateTime, String


@compiles(Cast)
def _sqlite_cast(element, compiler, **kw):
    if isinstance(element.clause.type, Date) and isinstance(element.type, DateTime):
        return compiler.process(
            type_coerce(element.clause, String) + " 00:00:00.000000",
            **kw
        )
    else:
        return compiler.visit_cast(element, **kw)

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("test", schema=None) as batch_op:
        batch_op.alter_column(
            "data",
            existing_type=sa.DATE(),
            type_=sa.DateTime(),
            existing_nullable=True,
        )

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("test", schema=None) as batch_op:
        batch_op.alter_column(
            "data",
            existing_type=sa.DateTime(),
            type_=sa.DATE(),
            existing_nullable=True,
        )

    # ### end Alembic commands ###

[paoloalba]

I imagined it was some SQLite constraint. Your solution with the hook works properly! Thanks

[zzzeek]

we need to add a hook for this the next time we have time to work on things, so keeping this open