Re: [sqlalchemy] Re: Custom (more restrictive) primaryjoin and deletion cascades

[mike bayer]

On 04/19/2017 10:36 AM, Adrian wrote:

Here's a MVCE-style example showing the problem I have:

|
fromsqlalchemy import*
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm import*

Base=declarative_base()


classType(Base):
__tablename__ ='types'
id =Column(Integer,primary_key=True)

def__repr__(self):
return''.format(self.id)


classBar(Base):
__tablename__ ='bars'
id =Column(Integer,primary_key=True)
deleted =Column(Boolean,nullable=False,default=False)
type_id =Column(ForeignKey('types.id'),nullable=True)
type
=relationship(Type,backref=backref('bars',primaryjoin='(Bar.type_id ==
Type.id) & ~Bar.deleted'))

def__repr__(self):
return''.format(self.id,self.type,self.deleted)


e =create_engine('postgresql:///test',echo=False)
Base.metadata.create_all(e)
s =Session(e,autoflush=False)

t =Type()
b1 =Bar(type=t)
b2 =Bar(type=t,deleted=True)
s.add(t)
s.commit()
s.delete(t)
s.flush()

|


So basically when I'm using the relationship in my code I never want
deleted items to show up. However, for cascading I still need them.
Using serverside cascades could work but if there's a way of doing that
without having to switch to serverside cascades it'd be nicer.

BTW the example doesn't work with SQLite, apparently it automatically
NULLs invalid FKs even without specifying `on delete set null` on the FK.


the general idea of SQLA emitting NULL should work on SQLite as it does 
in my example.


the treatment of items in the collection when the parent object is 
deleted is dependent on what items are actually loaded into the 
collection.   So if you set deleted=True on the related item, it won't 
be loaded in the collection and it won't get modified.


Here you'd need to build another relationship just for the purposes of 
this delete with the simpler primaryjoin condition.  Or just use ON 
DELETE SET NULL which is a lot simpler, not sure why you don't prefer that.






--
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] Re: Custom (more restrictive) primaryjoin and deletion cascades

[Adrian]

Here's a MVCE-style example showing the problem I have:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()


class Type(Base):
__tablename__ = 'types'
id = Column(Integer, primary_key=True)

def __repr__(self):
return ''.format(self.id)


class Bar(Base):
__tablename__ = 'bars'
id = Column(Integer, primary_key=True)
deleted = Column(Boolean, nullable=False, default=False)
type_id = Column(ForeignKey('types.id'), nullable=True)
type = relationship(Type, backref=backref('bars', primaryjoin='(Bar.type_id 
== Type.id) & ~Bar.deleted'))

def __repr__(self):
return ''.format(self.id, self.type, self.deleted)


e = create_engine('postgresql:///test', echo=False)
Base.metadata.create_all(e)
s = Session(e, autoflush=False)

t = Type()
b1 = Bar(type=t)
b2 = Bar(type=t, deleted=True)
s.add(t)
s.commit()
s.delete(t)
s.flush()



So basically when I'm using the relationship in my code I never want 
deleted items to show up. However, for cascading I still need them.
Using serverside cascades could work but if there's a way of doing that 
without having to switch to serverside cascades it'd be nicer.

BTW the example doesn't work with SQLite, apparently it automatically NULLs 
invalid FKs even without specifying `on delete set null` on the FK.

-- 
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] Custom (more restrictive) primaryjoin and deletion cascades

[mike bayer]

Here's an MCVE.   Works fine as long as "Abstract.is_deleted" is in fact 
False.  if it's true, then it won't mark it, because it's not in the 
collection to be updated.


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class ContributionType(Base):
__tablename__ = 'contributiontype'
id = Column(Integer, primary_key=True)


class Abstract(Base):
__tablename__ = 'abstract'
id = Column(Integer, primary_key=True)
is_deleted = Column(Boolean)

contrib_type_id = Column(ForeignKey('contributiontype.id'))
contrib_type = relationship(
'ContributionType', lazy=True, foreign_keys=contrib_type_id,
backref=backref(
'proposed_abstracts',
primaryjoin='(Abstract.contrib_type_id == 
ContributionType.id) & (~Abstract.is_deleted)',

lazy=True)
)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)


s = Session(e)

cont = ContributionType()
abst = Abstract(contrib_type=cont, is_deleted=0) # if you set this to 
'1', it doesn't get deleted

s.add_all([cont, abst])
s.commit()

s.delete(cont)
s.commit()

assert abst.contrib_type_id is None



On 04/19/2017 09:26 AM, mike bayer wrote:



On 04/19/2017 07:27 AM, Adrian wrote:

I have this relationship which adds a
`ContributionType.proposed_abstracts` backref that contains only
abstracts not flagged as deleted.

contrib_type = relationship(
'ContributionType', lazy=True, foreign_keys=contrib_type_id,
backref=backref('proposed_abstracts',
primaryjoin='(Abstract.contrib_type_id == ContributionType.id) &
~Abstract.is_deleted', lazy=True)
)

This works perfectly fine but unfortunately a
`session.delete(some_contribution_type)` now does not NULL out the
contrib_type_id of an abstract that
has been flagged as deleted.


can you clarify what "now" means?  what's the version that "worked" ?



Is there any way to use different join criteria for deletion cascades
and for just accessing the relationship? Or do I need to hook into the
before_delete
event for this?

--
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] Custom (more restrictive) primaryjoin and deletion cascades

[mike bayer]

On 04/19/2017 07:27 AM, Adrian wrote:

I have this relationship which adds a
`ContributionType.proposed_abstracts` backref that contains only
abstracts not flagged as deleted.

contrib_type = relationship(
'ContributionType', lazy=True, foreign_keys=contrib_type_id,
backref=backref('proposed_abstracts',
primaryjoin='(Abstract.contrib_type_id == ContributionType.id) &
~Abstract.is_deleted', lazy=True)
)

This works perfectly fine but unfortunately a
`session.delete(some_contribution_type)` now does not NULL out the
contrib_type_id of an abstract that
has been flagged as deleted.


can you clarify what "now" means?  what's the version that "worked" ?



Is there any way to use different join criteria for deletion cascades
and for just accessing the relationship? Or do I need to hook into the
before_delete
event for this?

--
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] Idle in transaction connections can not be returned to pool

[mike bayer]

On 04/18/2017 10:19 PM, JinRong Cai wrote:

Hi:


SQLA is used in my python project. And in my code, I have the green
thread used. And the green thread was killed in some of my code. After
the the thread was killed, and the transaction was just began. And the
database connection was still in postgresql server side with status
"idle in transaction". This caused my connection was exhausted in the pool.

Traceback (most recent call last): File
"/usr/lib/python2.7/dist-packages/eventlet/hubs/hub.py", line 457, in
fire_timers timer() File
"/usr/lib/python2.7/dist-packages/eventlet/hubs/timer.py", line 58, in
*call* cb(/args, *kw) File
"/usr/lib/python2.7/dist-packages/eventlet/greenthread.py", line 214, in
main result = function(*args, *kwargs) File "/tmp/test.py", line 48, in
operate_db query = session.query(Test).all() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2588,
in all return list(self) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2736,
in *iter* return self._execute_and_instances(context) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2749,
in _execute_and_instances close_with_result=True) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2740,
in _connection_from_session /*kw) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 905,
in connection execution_options=execution_options) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 910,
in _connection_for_bind engine, execution_options) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 334,
in _connection_for_bind conn = bind.contextual_connect() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2039,
in contextual_connect self._wrap_pool_connect(self.pool.connect, None),
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
2074, in _wrap_pool_connect return fn() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 376, in
connect return _ConnectionFairy._checkout(self) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 714, in
_checkout fairy = _ConnectionRecord.checkout(pool) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 480, in
checkout rec = pool._do_get() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 1054, in
_do_get (self.size(), self.overflow(), self._timeout)) TimeoutError:
QueuePool limit of size 2 overflow 0 reached, connection timed out,
timeout 30


Questions:

1. When will the connection returned to the pool?

 In my understanding, when the rollback/commit was listened, the
connection was returned.

>
> 2. Will the connection returned to the pool if my green thread was 
killed?

>

if you kill a greenlet, that doesn't imply any action with the 
connection pool.  You should catch GreenletExit in your greenlet that 
expects to be killed and end the transaction.





From the code, we can see the connection object seems still in the pool
with active status.

Also there is no greenexit exception here , if just kill the green
thread safely.


why not ?Python doesn't have many options for control flow.  if a 
series statements is to be interrupted, there needs to be an exception. 
This is exactly how "kill" works:


http://eventlet.net/doc/modules/greenthread.html#eventlet.greenthread.kill

"Terminates the target greenthread by raising an exception into it. 
Whatever that greenthread might be doing; be it waiting for I/O or 
another primitive, it sees an exception right away."








3. Will the connection returned to the pool if the db transaction(id in
transaction) was rolled back by my
database(idle_in_transaction_session_timeout).


no because there is no event system in the Python DBAPI that would alert 
something in SQLAlchemy to the fact that the connection status has 
changed.  You must invoke code to make anything happen.



If not, how can we handle

the connection whose status is in "idle in transaction"?


you need to roll back transactions when you no longer need them.



I also kill the database transaction process in PG server, but the
connection still not returned to the pool.


this will have no effect because there is no event generated from the 
server to the client.






The code was updated with Monkey patching.

Thanks.



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

[sqlalchemy] Custom (more restrictive) primaryjoin and deletion cascades

[Adrian]

I have this relationship which adds a `ContributionType.proposed_abstracts` 
backref that contains only abstracts not flagged as deleted.

contrib_type = relationship(
'ContributionType', lazy=True, foreign_keys=contrib_type_id,
backref=backref('proposed_abstracts', 
primaryjoin='(Abstract.contrib_type_id == ContributionType.id) & 
~Abstract.is_deleted', lazy=True)
)

This works perfectly fine but unfortunately a 
`session.delete(some_contribution_type)` now does not NULL out the 
contrib_type_id of an abstract that
has been flagged as deleted.

Is there any way to use different join criteria for deletion cascades and 
for just accessing the relationship? Or do I need to hook into the 
before_delete
event for this?

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