Re: [sqlalchemy] Column CheckConstraint() question
On Sun, 6 May 2018, Mike Bayer wrote: the second approach is probably more common, as it's more compact. Thanks, Mike. It works well here. Best regards, Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
the second approach is probably more common, as it's more compact. On Sun, May 6, 2018 at 6:30 PM, Rich Shepard wrote: > On Sun, 6 May 2018, Mike Bayer wrote: > >> here is the correct way to construct and append the constraint: > > > Thanks, Mike. I tried following the example from the docs and could not > find what I missed. > > You provide two approaches. Is there a preference for one over the other, > perhaps based on context? > > This application, and others I plan to write, have columns restricted to > specific content for consistency and data integrity. > > Much appreciated, > > > Rich > > -- > 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. -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Sun, 6 May 2018, Mike Bayer wrote: here is the correct way to construct and append the constraint: Thanks, Mike. I tried following the example from the docs and could not find what I missed. You provide two approaches. Is there a preference for one over the other, perhaps based on context? This application, and others I plan to write, have columns restricted to specific content for consistency and data integrity. Much appreciated, Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
or more succinctly (note the comma at the end of the CheckConstraint to indicate a tuple): class Sites(Base): __tablename__ = 'locations' site_id = Column(Integer, primary_key=True) site_name = Column(String(16), nullable=False) data_type = Column(String(12), nullable=False) source = Column(String(64)) lat = Column(String(9)) lon = Column(String(9)) stream = Column(String(32)) basin = Column(String(32)) comment = Column(String) __table_args__ = ( CheckConstraint( data_type.in_( ['Biogical', 'Chemical', 'Microbial', 'Physical', 'Multiple'] ) ), ) On Sun, May 6, 2018 at 5:49 PM, Mike Bayer wrote: > here is the correct way to construct and append the constraint: > > class Sites(Base): > __tablename__ = 'locations' > > site_id = Column(Integer, primary_key=True) > site_name = Column(String(16), nullable=False) > data_type = Column(String(12), nullable=False) > source = Column(String(64)) > lat = Column(String(9)) > lon = Column(String(9)) > stream = Column(String(32)) > basin = Column(String(32)) > comment = Column(String) > > Sites.__table__.append_constraint( > CheckConstraint(Sites.data_type.in_(['Biogical', 'Chemical', > 'Microbial', 'Physical', 'Multiple'])) > ) > > > > > > On Sun, May 6, 2018 at 11:33 AM, Rich Shepard > wrote: >> On Fri, 4 May 2018, Mike Bayer wrote: >> >>> you're looking for a table-level check constraint with IN: >>> table.append_constraint( >>> CheckConstraint(table.c.data_type.in_('A', 'B', 'C')) >>> ) >> >> >> Mike, >> >> I'm missing how to properly use the above in my models.py module. >> >> For example: >> >> class Sites(Base): >> __tablename__ = 'locations' >> >> site_id = Column(Integer, primary_key=True) >> site_name = Column(String(16), nullable=False) >> data_type = Column(String(12), nullable=False) >> source = Column(String(64)) >> lat = Column(String(9)) >> lon = Column(String(9)) >> stream = Column(String(32)) >> basin = Column(String(32)) >> comment = Column(String) >> >> locations.append.constraint( >> CheckConstraint(locations.data_type_in('Biogical', 'Chemical', >> 'Microbial', 'Physical', 'Multiple')) >> ) >> >> Python shows me this error: >> >> Traceback (most recent call last): >> File "./openEDMS.py", line 18, in >> import models >> File "/home/rshepard/development/openEDMS/models.py", line 20, in >> class Sites(Base): >> File "/home/rshepard/development/openEDMS/models.py", line 37, in Sites >> CheckConstraint(locations.data_type_in('Biogical', 'Chemical', >> 'Microbial', 'Physical', 'Multiple')) >> NameError: name 'locations' is not defined >> >> If I change the table-level constraint from the tablename (locations) to >> the class name (Sites) python gives me the equivalent NameError. What syntax >> error have I made here? >> >> Regards, >> >> >> Rich >> >> -- >> 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. -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
here is the correct way to construct and append the constraint: class Sites(Base): __tablename__ = 'locations' site_id = Column(Integer, primary_key=True) site_name = Column(String(16), nullable=False) data_type = Column(String(12), nullable=False) source = Column(String(64)) lat = Column(String(9)) lon = Column(String(9)) stream = Column(String(32)) basin = Column(String(32)) comment = Column(String) Sites.__table__.append_constraint( CheckConstraint(Sites.data_type.in_(['Biogical', 'Chemical', 'Microbial', 'Physical', 'Multiple'])) ) On Sun, May 6, 2018 at 11:33 AM, Rich Shepard wrote: > On Fri, 4 May 2018, Mike Bayer wrote: > >> you're looking for a table-level check constraint with IN: >> table.append_constraint( >> CheckConstraint(table.c.data_type.in_('A', 'B', 'C')) >> ) > > > Mike, > > I'm missing how to properly use the above in my models.py module. > > For example: > > class Sites(Base): > __tablename__ = 'locations' > > site_id = Column(Integer, primary_key=True) > site_name = Column(String(16), nullable=False) > data_type = Column(String(12), nullable=False) > source = Column(String(64)) > lat = Column(String(9)) > lon = Column(String(9)) > stream = Column(String(32)) > basin = Column(String(32)) > comment = Column(String) > > locations.append.constraint( > CheckConstraint(locations.data_type_in('Biogical', 'Chemical', > 'Microbial', 'Physical', 'Multiple')) > ) > > Python shows me this error: > > Traceback (most recent call last): > File "./openEDMS.py", line 18, in > import models > File "/home/rshepard/development/openEDMS/models.py", line 20, in > class Sites(Base): > File "/home/rshepard/development/openEDMS/models.py", line 37, in Sites > CheckConstraint(locations.data_type_in('Biogical', 'Chemical', > 'Microbial', 'Physical', 'Multiple')) > NameError: name 'locations' is not defined > > If I change the table-level constraint from the tablename (locations) to > the class name (Sites) python gives me the equivalent NameError. What syntax > error have I made here? > > Regards, > > > Rich > > -- > 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. -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Sun, 6 May 2018, Rich Shepard wrote: I'm missing how to properly use the above in my models.py module. Mike, And I have read the brief description of the CHECK Contstraint in the 'Defining Constraints and Indexes' section of the docs. Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Fri, 4 May 2018, Mike Bayer wrote: you're looking for a table-level check constraint with IN: table.append_constraint( CheckConstraint(table.c.data_type.in_('A', 'B', 'C')) ) Mike, I'm missing how to properly use the above in my models.py module. For example: class Sites(Base): __tablename__ = 'locations' site_id = Column(Integer, primary_key=True) site_name = Column(String(16), nullable=False) data_type = Column(String(12), nullable=False) source = Column(String(64)) lat = Column(String(9)) lon = Column(String(9)) stream = Column(String(32)) basin = Column(String(32)) comment = Column(String) locations.append.constraint( CheckConstraint(locations.data_type_in('Biogical', 'Chemical', 'Microbial', 'Physical', 'Multiple')) ) Python shows me this error: Traceback (most recent call last): File "./openEDMS.py", line 18, in import models File "/home/rshepard/development/openEDMS/models.py", line 20, in class Sites(Base): File "/home/rshepard/development/openEDMS/models.py", line 37, in Sites CheckConstraint(locations.data_type_in('Biogical', 'Chemical', 'Microbial', 'Physical', 'Multiple')) NameError: name 'locations' is not defined If I change the table-level constraint from the tablename (locations) to the class name (Sites) python gives me the equivalent NameError. What syntax error have I made here? Regards, Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Fri, 4 May 2018, Rich Shepard wrote: alternatively, just use the backend-agnostic Enum type with native=False: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum gives you the same CHECK constraint I'll look at that. Mike, And I have read it, but don't really understand it; at least, not well enough to apply it to this first SQLAlchemy project. Thanks, Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Fri, 4 May 2018, Mike Bayer wrote: you're looking for a table-level check constraint with IN: Mike, Oh. I missed that since I write my postgres schema constraints on the column. alternatively, just use the backend-agnostic Enum type with native=False: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum gives you the same CHECK constraint I'll look at that. Do you recommend one approach over the other for a new SQLAlchemy developer? Best regards, Rich -- 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.
Re: [sqlalchemy] Column CheckConstraint() question
On Fri, May 4, 2018 at 4:49 PM, Rich Shepard wrote: > In postgres (and I believe also in sqlite3) values in a table column can > be restricted to certain values. > > In models.py the class Sites() includes this column: > > data_type = Column(String(12), nullable=False, > CheckConstraint('Biogical', 'Chemical', 'Microbial', > 'Physical', > 'Multiple')) > > but Python doesn't like this syntax: > > Traceback (most recent call last): > File "./openEDMS.py", line 18, in > import models > File "/home/rshepard/development/openEDMS/models.py", line 28 > data_type = Column(String(64), nullable=False, > CheckConstraint('Biogical', > 'Chemical', 'Microbial', > 'Physical', 'Multiple')) ^ > SyntaxError: positional argument follows keyword argument > > My web search found examples and the SA CHECK constraint description, but > none used a list of strings as acceptable values. I need to learn how to > implement this constraint as there are several model classes that use it. you're looking for a table-level check constraint with IN: table.append_constraint( CheckConstraint(table.c.data_type.in_('A', 'B', 'C')) ) alternatively, just use the backend-agnostic Enum type with native=False: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum gives you the same CHECK constraint > > Rich > > > -- > 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. -- 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.