- changed milestone to 1.0
- changed component to sql
- edited description
- attached 3067.patch
Naming convention exception for Boolean type on PostgreSQL
mybool = Column(Boolean, nullable=False, default=False)
I use PostgreSQL which has a native boolean type. If I then also use naming convention:
"ck": "ck_%(table_name)s_%(constraint_name)s",
Then sqlalchemy complains:
sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
If I define the column as
isPublic = Column(Boolean(create_constraint=False), nullable=False, default=False)
it works, so I think this is a bug in which sqlalchemy doesn't connect the fact that there is a native Boolean and no constraint exists actually.
Comments (26)
-
repo owner -
reporter It will probably fail when using "alembic --sql" as there's no connection then. How should it know which SQL backend (mysql, postgresql,..) is used?
-
repo owner all databases have entirely different variants of DDL so --sql mode requires that a dialect be present just like in any other case, no problem there.
not sure if I like this approach still as this means special steps need to be taken in order to read constraint.name. still haven't devised a solution to this which covers every base.
-
repo owner - attached 3067.patch
updated patch. name is deferred just for those elements
-
repo owner -
repo owner this also fixes
#3068 -
repo owner - attached 3067.patch
updated
-
repo owner - changed milestone to 0.9.7
-
repo owner Issue
#3068was marked as a duplicate of this issue. -
repo owner - changed status to resolved
- Fix bug in naming convention feature where using a check
constraint convention that includes
constraint_name
would then force all :class:.Boolean
and :class:.Enum
types to require names as well, as these implicitly create a constraint, even if the ultimate target backend were one that does not require generation of the constraint such as Postgresql. The mechanics of naming conventions for these particular constraints has been reorganized such that the naming determination is done at DDL compile time, rather than at constraint/table construction time. fixes#3067
→ <<cset d2193f53c10d>>
-
repo owner - Fix bug in naming convention feature where using a check
constraint convention that includes
constraint_name
would then force all :class:.Boolean
and :class:.Enum
types to require names as well, as these implicitly create a constraint, even if the ultimate target backend were one that does not require generation of the constraint such as Postgresql. The mechanics of naming conventions for these particular constraints has been reorganized such that the naming determination is done at DDL compile time, rather than at constraint/table construction time. fixes#3067
→ <<cset d462dbde9976>>
- Fix bug in naming convention feature where using a check
constraint convention that includes
-
repo owner - changed milestone to 0.9.8
-
repo owner - changed milestone to 0.9.7
-
repo owner regression. See
#3144 -
repo owner kind of a regression in
#3299as well. -
I noticed that SQLite runs into the exact same issue with a boolean type and a naming convention as described in the Alembic docs.
Throws the same error:
sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
Should this be opened as a new bug or not? I am using SQLAlchemy 1.0.13 on Python 3.4
-
repo owner @robvdl you actually have the opposite error; you're on SQLite which actually requires the constraint and thus the naming convention. The issue here is about errors being raised in the case when no such naming convention is required (e.g. boolean on Postgresql).
-
encountered this error on sqlalchemy 1.0.14 (with flask-sqlalchemy 2.1)
naming conventions code is copied from document http://flask-sqlalchemy.pocoo.org/2.1/config/#using-custom-metadata-and-naming-conventions and http://docs.sqlalchemy.org/en/rel_1_0/core/constraints.html#constraint-naming-conventions
with model:
class Staff(db.Model): name = db.Column(db.Unicode(16), nullable=False) email = db.Column(db.String(64), unique=True, nullable=False)
run create_all(), got:
sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
full stack: https://pastebin.mozilla.org/8890960
-
repo owner @georgexsh there's no CHECK contraint in that model. that's not the code for that stack trace.
-
@robvdl @zzzeek I can confirm that sqlalchemy 1.0.15 with SQLite works fine with a naming convention if there are no Boolean fields, but raises an exception as soon as we reintroduce the Boolean fields. A quick workaround is to use Integer instead of Boolean, if we want to stick to using a naming convention.
sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
-
repo owner @invisibleroads you need to give a name to your Boolean field or tell it to not generate a constraint. http://docs.sqlalchemy.org/en/latest/core/constraints.html?highlight=boolean#configuring-naming-for-boolean-enum-and-other-schema-types
-
We're confirming that Boolean(name='xyz') works. Thank you so much for making sqlalchemy, dogpile.cache, alembic.
-
Account Deactivated Hey, i got this issue with a Mysql DB too. the odd thing is that mysql does not support check constraints. so i don't really see the point of naming Boolean types or changing the naming convention in Mysql... it should silently pass as in pg , no ?
btw the doc is somewhat misleading here :
This convention will only be consulted for the CHECK constraint if we run against a database without a native BOOLEAN type like SQLite or MySQL.
-
repo owner @emerzh "this issue " I assume you mean you are using a CK constraint naming convention that looks for the name of the constraint as part of the name. so yeah, MySQL ignores the constraints, why use a naming convention for them ? it doesn't "pass" because SQLA goes ahead and generates CHECK on MySQL, there's no logic to omit these as they are silently accepted by MySQL (and one should really ask, why is that).
btw the doc is somewhat misleading here
you need to be more specific
-
Account Deactivated hi, thanks for your reply :)
yes i got the naming convention exception while trying to use a boolean.
I totaly agree with you, the comportement on mysql is wierd and mysql should either fail or accept the condition and do something. I also understant that sqlalchemy generate the check even if the database just "ignore it".
but having to name something that does not exists seems a bit weird.
-
repo owner but having to name something that does not exists seems a bit weird.
absolutely. so again, why does your naming convention have a "ck" entry in it?
- Log in to comment
naming is obviously taking awhile to get off the ground. Stick with the workaround for now, this is sort of a behavioral enhancement IMHO, the attached patch defers out the naming until compile time. But I'm not sure if this throws off existing use cases, 1.0 for now...