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? On 11/18/2016 06:06 PM, mike bayer wrote: > > 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 > . > > > > > > On 11/18/2016 05:34 PM, Jonathan Rogers wrote: >> 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. >> >> >> On Friday, November 18, 2016 at 5:11:10 PM UTC-5, Mike Bayer wrote: >> >> 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 >> <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. >> >> >> >> >> >> On 11/18/2016 04:43 PM, Jonathan Rogers wrote: >> > 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()? >> > >> > -- >> > 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 >> <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 <javascript:> >> > <mailto:sqlalchemy+unsubscr...@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+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > -- Jonathan Rogers -- 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.
