[sqlalchemy] [alembic] Batch migrations with sqlite and naming conventions

[Peter Erickson]

I'm sure that I'm missing a subtle point with batch migrations and 
naming conventions for SQLite databases, but I can't figure out what I'm 
doing wrong. After renaming a table, I'm using a batch migration to 
update a foreign key in a child table. However, after recreating the 
child table 2 CHECK constraints (being created from a Boolean) aren't 
being named according to the specified naming convention.

To ensure that this post doesn't get too long, here are the parts I 
think are relevant. I can add more if needed.

naming_convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
    }

class RulesetMatch(Base):
    __tablename__ = 'ruleset_matches'
    ...
    viewed = Column(Boolean(name='view_bool'), default=False)
    hide = Column(Boolean(name='hide'), default=False)

# sqlite3 data-dev.sqlite .schema

CREATE TABLE ruleset_matches (
    ...
    viewed BOOLEAN,
    hide BOOLEAN,
    CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
    CONSTRAINT ck_ruleset_matches_view_bool CHECK (viewed IN (0, 1)),
    CONSTRAINT ck_ruleset_matches_hide CHECK (hide IN (0, 1))
);

# migration script

def upgrade():
    with op.batch_alter_table(
            'ruleset_matches',
            naming_convention=naming_convention,
            reflect_args=[
                Column('viewed', Boolean(name='view_bool')),
                Column('hide', Boolean(name='hide'))]) as batch_op:
        # drop_constraint
        # create_foreign_key

# sqlite3 data-dev.sqlite .schema

CREATE TABLE IF NOT EXISTS "ruleset_matches" (
    ...
    viewed BOOLEAN,
    hide BOOLEAN,
    CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
    CONSTRAINT view_bool CHECK (viewed IN (0, 1)),
    CONSTRAINT hide CHECK (hide IN (0, 1)),
    ...
);

As shown in the last schema, the CHECKS are named "view_bool" and "hide" 
unlike what it was previously "ck_ruleset_matches_view_bool" and 
"ck_ruleset_matches_hide." If I remove the "name" attribute in 
reflect_args or remove reflect_args all together, the CHECK constraints 
are unnamed. Removing the naming_convention doesn't seem to affect 
anything. What do I need to do to ensure that the constraints are named 
appropriately?

alembic 0.9.1
sqlalchemy 1.1.9

Thanks in advance.

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.