Introspecting column constraints

[Jonathan Rogers]

I have a Declarative-instrumented class with several constraints, some defined at the table level and some on a column. AFAICT, all the constraints are configured correctly because they are rendered correctly by CreateTable() when called with the class's Table instance. In order to import some data into my carefully designed model, I need to drop some constraints, then recreate them NOT VALID. I want to use the existing Table instance to execute SQL rather than typing duplicate SQL manually. However, I'm having trouble introspecting the Table instance to find a specific CheckConstraint defined on a specific column. The Table instance's "constraints" property does not contain the constraint I need. I can find a CheckConstraint instance in the column's "constraints" property but it's not bound to a Table so I can't just pass it to DropConstraint(). What is the proper way to get a CheckConstraint instance defined on a column that I can pass to DropConstraint() and AddConstraint()?

[mike bayer]

Assuming here your constraints are already defined in Python and we are
not talking about using reflection, we're talking about the CHECK
constraint objects you've already built. If they are defined for a
Table then they would be in table.constraints. If you have them on the
Column then it's true they might only be in column.constraints in that
case. If you're defining them in either place then you'd need to look
in either place. It might be best to go with all table-bound CHECK
constraints as I'm not sure it's as flexible to do add/drop with the
column-level ones. At the very least, constraints need to have an
explicit name in order to support ADD/DROP.

Alembic's docs get into this a bit here:
http://alembic.zzzcomputing.com/en/latest/naming.html where it talks
about the "naming convention" feature of SQLAlchemy that may or may not
be of use here.

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

[Jonathan Rogers]

Yes, the constraints are defined in Python and have names explicitly defined. The CheckConstraint I need to use is defined in a @declared_attr method for the column in a base class from which the mapped class inherits. I did find the CheckConstraint I need in MappedClass.__table__.c.column_name.constraints, but it is not bound to a table, so when I pass it to DropConstraint(), I get:

sqlalchemy.exc.InvalidRequestError: This constraint is not bound to a table.  Did you mean to call table.append_constraint(constraint) ?

If I pass the the CheckConstraint to MappedClass.__table__.append_constraint(), DropConstraint() works, but I don't understand why I need to explicitly add the constraint to the table.

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

The reason AddConstraint/DropConstraint need the Constraint attached to
the table is because ADD CONSTRAINT / DROP CONSTRAINT in SQL require the
table name, as these are related to ALTER TABLE <tablename>. I
recommend you use Alembic ops to emit ADD CONSTRAINT / DROP CONSTRAINT
without requiring Table logic, see
http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations
.

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

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

> <mailto:sqlalchemy+...@googlegroups.com>.

[Jonathan Rogers]

Thanks for the help and suggestions. I understand that Constraint
objects need to be bound to a Table to drop or create them. I thought
there might be a general, straightforward way to find a Constraint by
Table and name. I did find a such a way for Constraints defined on the
Table. The approach I'm using for Constraints defined on Columns within
the Table works but seems kludgey. I had thought that since
CreateTable() automatically handles both cases, there might be a better way.

Rethinking it one more time, a Constraint defined within a Column is
expected to render a column constraint within a CREATE TABLE statement.
However, SQL provides no way to add a CHECK column constraint to a
column on an existing table. Therefore, I need to drop a column
constraint and then create a table constraint with the same name and
expression. So, as you said, defining the Constraint on the Table in the
first place would be more consistent.

I had looked at Alembic but it doesn't seem to quite match my use case.
Operations.drop_constraint() does what I need, but
Operations.create_check_constraint() does not seem to be able to use an
existing CheckConstraint instance. It also doesn't seem to have support
for Postgres's NOT VALID modifier. Maybe I'm missing something or maybe
it would be easy to extend Alembic to add these features?

--
Jonathan Rogers