Adding Naming Convention to Existing Database

Question

I'm using sqlalchemy and am trying to integrate alembic for database migrations.

My database currently exists and has a number of ForeignKeys defined without names. I would like to add a naming convention to allow for migrations that affect ForeignKey columns.

I've added the naming convention given here to the top of my models.py file: SQLAlchemy Naming Constraints

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"
              }

DeclarativeBase = declarative_base()


DeclarativeBase.metadata = MetaData(naming_convention=convention)

def db_connect():
   return create_engine(URL(**settings.DATABASE))

def create_reviews_table(engine):
    DeclarativeBase.metadata.create_all(engine)

class Review(DeclarativeBase):

    __tablename__ = 'reviews'

    id = Column(Integer, primary_key=True)
    review_id = Column('review_id', String, primary_key=True)
    resto_id = Column('resto_id', Integer, ForeignKey('restaurants.id'),
            nullable=True)
    url = Column('url', String),
    resto_name = Column('resto_name', String)

I've set up alembic/env.py as per the tutorial instructions, feeding my model's metadata into target_metadata.

When I run

$: alembic current

I get the following error: sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

In the docs they say that "This same feature [generating names for columns using a naming convention] takes effect even if we just use the Column.unique flag:" 1, so I'm thinking that there shouldn't be a problem (they go on to give an example using a ForeignKey that isn't named too).

Do I need to go back and give all my constraints explicit names, or is there a way to do it automatically?

Answer 1

just modify th "ck" in convention to "ck": "ck_%(table_name)s_%(column_0_name)s"。it works for me .

refer to see sqlalchemy docs

Answer 2

What this error message is telling you is that you should name constraints explicitly. The constraints it's referring to are Boolean, Enum etc but not foreignkeys nor primary keys. So go through your table, wherever you have a Boolean or Enum add a name to it. For example:

is_active = Column(Boolean(name='is_active'))

That's what you need to do.