Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

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

[Mike Bayer]

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

[Rich Shepard]

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

[Mike Bayer]

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

[Mike Bayer]

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

[Rich Shepard]

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

[Rich Shepard]

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

[Rich Shepard]

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

[Mike Bayer]

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.

[sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

  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.

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.