[sqlalchemy] Using a table alias / defining column_property inside a (virtual) parent class…

[Dave von Umlaut]

I am trying to create a generic "versioned" SQLAlchemy table model, from 
which I can derive further models. Among other things, I need a 
*is_latest_version* column property that can be used to create appropriate 
joins… 

Following code works fine:

class Versioned(db.Model):
__abstract__ = True
id = Column(Integer, primary_key=True)
version = Column(Integer, primary_key=True)

class Child(Versioned):
pass

t = aliased(Child, name="t")
Child.is_latest_version =  db.column_property(
select([ Child.version == db.func.max(t.version) ])
.select_from(t)
.where(t.id == Child.id)
)



But I cannot figure any way to move the column property up to the parent 
class. Any variation I attempt, bumps into class mapping issues…

Closest I could think off, would be something like:

class Versioned(db.Model):
__abstract__ = True
id = Column(Integer, primary_key=True)
version = Column(Integer, primary_key=True)

@declared_attr
def is_latest_version(cls):
t = aliased(cls, name="t")
return db.column_property(
select([ cls.version == db.func.max(t.version) ])
.select_from(t)
.where(t.id == cls.id)
)

class Child(Versioned):
pass



But it throws an error:
`sqlalchemy.orm.exc.UnmappedClassError: Class 'app.models.Child' is not 
mapped`

Using quotes does not work either (query is executed with quoted fields as 
literal strings).

Is there any way to achieve this kind of column_property in a virtual class?

Also… Not sure if that's related, but I also seem unable to implement 
*is_latest_version* with a nicer join-based query (I suspect solving my 
Alias issues might help):
… FROM child c1 JOIN child c2 ON c2.id = c1.id AND c2.version > c1.version 
WHERE c2.id IS NULL


Thanks in advance for any help!
-- 
Dave

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

Re: [sqlalchemy] How to override append and remove methods used in orm.relationships

[Diego Quintana]

At the moment I've moved to other features, but I should be back to this 
somewhere in the near future. I will let you know the results.

I really appreciate your time, thanks again.

Best,

Am Donnerstag, 3. Mai 2018 10:10:47 UTC-3 schrieb Mike Bayer:
>
> On Thu, May 3, 2018 at 7:39 AM, Diego Quintana  > wrote: 
> > Thanks again for your reply 
> > 
> >> at the core is that when you remove a child from the parent in the 
> >> _remove_pets event, you want to prevent the _remove_children() event 
> >> from actually happening, I think. 
> > 
> > 
> > Yes, since it is a different usage case or flavour. I was trying to pass 
> > kwargs to the event listener directly, but it does not work. 
> > 
> >> If I remove a pet from a parent, then we remove the child from the 
> >> parent, and *only* that pet.  we dont remove other pets that might be 
> >> associated with that child. 
> > 
> > 
> > Correct, this would be case A and the idea behind it is that, if a User 
> has 
> > *access* to a Child, it should also have access to all of the Child's 
> pets. 
> > If for some reason Parent does not have access to all of them, means it 
> does 
> > not have access to the Child either. 
> > 
> > Removing a pet that has a Child that is also present in user.children 
> should 
> > trigger this, leaving the parent with, as you say, only pets minus the 
> one 
> > that was removed. It would also remove the child from the 
> parent/children 
> > relationship in the association table. 
> > 
> >> if I remove a child from the parent, then we remove *all* pets 
> >> associated with the child from that parent. 
> > 
> > 
> > Correct, and this would be case B 
> > 
> >> This seems like it's a contradiction.  I have parent p1, not referring 
> >> to child c1, but it refers to pet p1 which *does* refer to child c1, 
> >> and that is valid. There's basically two flavors of "remove child 
> >> from parent", is that right? 
> > 
> > 
> > Yes, both relationships -parent/pet and parent/child- are not seeing 
> each 
> > other, and the only binding is 
> > their relationship between child and pets. 
> > 
> > Thus, the two flavors are depending on where is that listener being 
> called 
> > from. For the case A, this is called inside the if statement in 
> > _remove_pets. Outside of this it should be always case B. 
> > 
> >> I tried to work on an implementation here which would also have to be 
> >> extremely clever but I realized I don't actually understand what this 
> >> is supposed to do.  if "remove child from parent" has two different 
> >> flavors then there needs to be all kinds of trickery to protect the 
> >> events from each other. 
> > 
> > 
> > I understand that it requires a lot of fiddling. I was trying to pass 
> kwargs 
> > to the listener directly, and parse them inside the other listener, but 
> the 
> > other listener is not receiving them. I was seeing this is on purpose 
> here , 
> > since accepting kwargs would pollute the API. Perhaps a custom event 
> > implementation? 
>
> I slowly realized it looked like you hoped that flag would pass 
> through but there's too many layers of indirection for it to work that 
> way.  the main complication here is that those "dynamic" relationships 
> require that a query runs for everything, which means everything has 
> to be in the database, which means it flushes the session very 
> aggressively (and also disabling the flush, another thing I tried, 
> means it doesn't read the contents of the collections accurately), and 
> all of that makes an already tricky operation nearly impossible 
> without it barreling into itself. 
>
> If you are committed to using the "dynamic" relationships, you can 
> always rely on emitting SQL to read from those association tables, and 
> there's a completely unorthodox way to do this which would be way more 
> efficient in most cases, and is extremely simple, just emit the DELETE 
> statements: 
>
> from sqlalchemy.orm import object_session 
>
> @sa.event.listens_for(Parent.children, 'remove') 
> def _remove_children(parent, child, initiator, *args, **kwargs): 
> object_session(parent).execute( 
> "delete from parents_pets_relationship where " 
> "parent_id=:parent_id and pet_id=:pet_id", 
> [ 
> {"parent_id": parent.id, "pet_id": pet.id} 
> for pet in child.pets 
> ] 
> ) 
>
>
> @sa.event.listens_for(Parent.pets, 'remove') 
> def _remove_pets(parent, pet, initiator, *args, **kwargs): 
> object_session(parent).execute( 
> "delete from parents_children_relationship where " 
> "parent_id=:parent_id and child_id=:child_id", 
> {"parent_id": parent.id, "child_id": pet.child.id} 
> ) 
>
> I added a second test for the other case and this works as far as I've 
> gotten it.  since we are only dealing with these standalone 
> association tables the above is pretty simple, and now you only emit 
> one query (albeit a DML query, not a