possible bug on sqlite regarding automatic constraints when using alembic recommended naming conventions

[Jonathan Vanasco]

Mike-

I'm not sure if this is a bug or docs incompatibility issue, and I know metadata may be going away soon...  but I'm leaning towards a bug.

I was setting up a test suite for a Pyramid plugin, and used the Alembic naming conventions (https://alembic.sqlalchemy.org/en/latest/naming.html) as Pyramid references them too.

Using the sqlite driver, this generates a fatal exception:

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

Tracing things around, it seems due to `sqlalchemy.Boolean` creating a constraint by default. Switching Boolean to Unicode immediately fixes this; passing in `create_constraint=False` works too.

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Standard imports


import sqlalchemy
import sqlalchemy.orm


from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, Integer, Column, Unicode, ForeignKey, String




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"
}
_metadata = sqlalchemy.MetaData(naming_convention=NAMING_CONVENTION)


Base = declarative_base(metadata=_metadata)


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Define some models that inherit from Base


class Foo(Base):
    __tablename__ = 'foo'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    status_id = sqlalchemy.Column(sqlalchemy.Boolean, nullable=True, default=None)
    # the following works:

    # status_id = sqlalchemy.Column(sqlalchemy.Unicode, nullable=True, default=None)

    # the following works:

    # status_id = sqlalchemy.Column(sqlalchemy.Boolean(create_constraint=True), nullable=True, default=None)

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# set the engine


engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

[Mike Bayer]

On Mon, Jul 22, 2019, at 6:01 PM, Jonathan Vanasco wrote:

Mike-

I'm not sure if this is a bug or docs incompatibility issue, and I know metadata may be going away soon...  but I'm leaning towards a bug.

I was setting up a test suite for a Pyramid plugin, and used the Alembic naming conventions (https://alembic.sqlalchemy.org/en/latest/naming.html) as Pyramid references them too.

Using the sqlite driver, this generates a fatal exception:

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

Tracing things around, it seems due to `sqlalchemy.Boolean` creating a constraint by default. Switching Boolean to Unicode immediately fixes this; passing in `create_constraint=False` works too.

well that's what it's being told to do, booleans create a CHECK constraint, the naming convention is "ck_%(table_name)s_%(constraint_name)s", and "constraint_name" is not generated.   I know this is a bit of a catch but I'm not sure what other choice it should be making.

likely , the "create_constraint" flag should default to False for booleans and enums.   I think it was a mistake to default these to true, I think people usually don't care.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ac139582-d690-48c1-ac18-cf1536f37750%40googlegroups.com.

[Jonathan Vanasco]

On Monday, July 22, 2019 at 9:36:10 PM UTC-4, Mike Bayer wrote:

likely , the "create_constraint" flag should default to False for booleans and enums.   I think it was a mistake to default these to true, I think people usually don't care.

Everything you said was apparent to me, but I'm a seasoned SqlAlchemy user.  I dove into the docs and didn't see warning dragons or anything that would suggest this happens alongside the Alembic or Boolean docs... this seemed like something that would trip up inexperienced users.

I'm assuming I never saw this before on Postgres or MySql because sqlite requires a constraint to mimic boolean behaviors as there is no native type, but I usually reflect tables on those platforms so that could be the reason.

Would it make sense to default the constraint name to something like "sa_autoname_xxxxx' where xxxx is a random hash?

[Mike Bayer]

I would say no, because you are asking SQLAlchemy to make a guess a a name when the database would normally be doing this; however, that's not taking place since you've explicitly told it about  a naming convention you'd like to use!   if this naming convention should include "sa_autoname_xxxx" that should be explicit within the naming convention.

this points to that, you probably don't want to be using %(constraint_name)s in your convention.  Probably some other kind of token, like %({constraint_name:sa_autoname_12345})s or something, that is, some as-yet not defined feature to add to naming convention that allows constraint_name to have a fallback of some kind.

However, you can make this feature yourself, as naming conventions allow custom callables:

https://docs.sqlalchemy.org/en/13/core/constraints.html#constraint-naming-conventions

it's near the end, and is a little weird in this case, but looks like:

def auto_constraint_name(constraint, table):

    if constraint.name is None or constraint.name == "_unnamed_":

        return "sa_autoname_%s" % str(uuid.uuid4())[0:5]

    else:

        return constraint.name

NAMING_CONVENTION = {

    "auto_constraint_name": auto_constraint_name,

    "ix": 'ix_%(column_0_label)s',

    "uq": "uq_%(table_name)s_%(column_0_name)s",

    "ck": "ck_%(table_name)s_%(auto_constraint_name)s",

    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",

    "pk": "pk_%(table_name)s"

}

_metadata = sqlalchemy.MetaData(naming_convention=NAMING_CONVENTION)

as always, this is not well documented, especially the weirdness with "_unnamed_" which is a default token of sorts, and if you have any time to help at all creating an example and/or working out use cases to make this easier for this as it should likely be the default for "ck", let me know.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0afd534d-f60c-4434-9a61-3db41e918bc5%40googlegroups.com.

[Jonathan Vanasco]

That's great, Mike.  I will help out on this and handle the below.  I'll do a Github ticket for tracking, and you can assign it to me.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.