[sqlalchemy] Re: [Proposal] Some simple high-level feature to set connection timeouts

[Don Nillo]

Yup. Sure. I meant "query timeouts", not "connection timeouts". 
May be a good option could be like this:

connection.execute(stmt, timeout=MAX_TIME)

Anyway, thanks.
(Admins, please, feel free to delete this conversation. I'm ashamed for not 
using search properly. Sorry for that)

On Monday, January 10, 2022 at 8:22:27 PM UTC+3 Jonathan Vanasco wrote:

> SQLAlchemy supports connection timeouts to establish a connection already.
>
> SQLAlchemy does not, and can not, support query timeouts.  This is 
> possible with some python database drivers, but very rare.  In every Python 
> database program/library query timeouts are typically handled on the 
> database server, and almost never on Python.  You can use SQLAlchemy's 
> engine events to emit sql that will set/clear a timeout on the database 
> server.
>
> This has come up many times in the past, and there are several threads in 
> the group history that explain the details and offer solutions.
>
> On Monday, January 10, 2022 at 4:52:49 AM UTC-5 donnill...@gmail.com 
> wrote:
>
>> Hi!
>> Sorry for bothering, I have not enough skills to contribute yet (
>> But...
>> I think it would be great to have some feature to easily set connection 
>> timeouts in SQLAlchemy 2.0.
>> The reason is I faced a problem where I was unable to cancel 
>> some erroneous time-consuming queries with SQLAlchemy Core. And I guess, I 
>> am not the only one.
>> My straightforward nooby solution so far is this:
>>
>> from threading import Timer
>>
>> with engine.connect() as connection:
>>   timeout = Timer(MAX_EXECUTION_TIME, lambda: 
>> connection.connection.connection.cancel())
>>   timeout.start()
>>   r = connection.execute(stmt).freeze() # I just love FrozenResult)
>>   timeout.cancel()
>>
>> The bad thing this is dialect-specific and works only due to cancel() 
>> method in psycopg2
>> I was also trying to benefit from handling sqlalchemy.events but failed...
>> One of my intentions was to modify before_execute() method to catch 
>> unintended cartesian product queries and raise error instead of throwing 
>> warning.
>> Unfortunately, at the moment this feels like too low-level for me.
>>
>> What I wish to have is something like this:
>>
>> with engine.connect(timeout=MAX_EXECUTION_TIME) as connection:
>>   r = connection.execute(stmt)
>>
>> I hope somebody smart enough could seize time to think about it.
>> This would make me happy. 
>>
>> Thanks in advance!
>>
>>
>>
>> [image: thanks-in-advance.png]
>>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/9a5db8d3-5014-4d90-b7f7-bb0ea5942ba1n%40googlegroups.com.

Re: [sqlalchemy] Turn off connections invalidation functionality

[Mike Bayer]

On Mon, Jan 10, 2022, at 1:30 PM, Anupama Goparaju wrote:
> Thanks. Looks like this works but we need to set it for every exception 
> scenario. Just confirming if the exception_context is thread safe to set the 
> attribute.

yes this all happens local to the execute() function call, is not exposed to 
threads


> Also, does this setting prevent pool invalidation on all sort of exceptions 
> related to connections?

yes, the pathway by which getting an exception would lead to invalidating the 
connection is blocked by this event handler.   I'm not sure why you'd want to 
set that in all cases as there are legitimate "disconnect" error scenarios 
(such as database was stopped and restarted), unless you are doing something 
with the DBAPI connection that allows it to recover by itself, I guess.

> 
> @event.listens_for(Engine, *'handle_error'*)
> def receive_handle_error(exception_context):
>  if exception_context.invalidate_pool_on_disconnect:
>  exception_context.invalidate_pool_on_disconnect = False
> 
> On Thursday, January 6, 2022 at 10:03:55 AM UTC-8 Anupama Goparaju wrote:
>> Thanks, i will give it a try.
>> 
>> On Thursday, January 6, 2022 at 5:23:09 AM UTC-8 Mike Bayer wrote:
>>> __
>>> I can't guarantee that overriding private methods is safe, no.
>>> 
>>> there's a public API to disable errors resulting in invalidation, I suggest 
>>> you use that.
>>> 
>>> 
>>> 
>>> On Wed, Jan 5, 2022, at 7:35 PM, Anupama Goparaju wrote:
 I haven't read the response before and tried to skip the lib logic by 
 overriding the function below in my child call extending the NullPool to 
 do nothing. Is this safe to do?
 
 def _invalidate(self, connection, exception=None, _checkin=True):
   pass
 
 On Wednesday, January 5, 2022 at 4:33:01 PM UTC-8 Anupama Goparaju wrote:
> Great, thanks for the info.
> 
> On Friday, November 26, 2021 at 9:30:02 AM UTC-8 Mike Bayer wrote:
>> __
>> I've spent some time thinking about what might be being asked here.
>> the only thing I can think of is that when a particular database 
>> connection is found to be in what we call a "disconnect" state, the 
>> connection is invalidated, so that the connection will reconnect and 
>> make a new connection.   But also, this operation will typically assume 
>> the "disconnect" condition is that the database was restarted, or some 
>> other network condition has probably made all the connections that are 
>> pooled also invalid.   So the entire pool will be invalidated in this 
>> case as well.
>> 
>> Why someone might want to turn that off is if they are getting lots of 
>> invalidated connections for some other reason and they are not able to 
>> solve that problem, so they'd like the pool to not be invalidated 
>> totally.  This means that if the database is restarted, and for example 
>> you have 20 pooled connections, you will in a high-request environment 
>> get up to 20 server errors unless pool_pre_ping is turned on so that the 
>> connections are refreshed one at at time.
>> 
>> To disable the pool invalidation upon receipt of a single connection 
>> shown to be in a disconnect, implement the handle_error event: 
>> https://docs.sqlalchemy.org/en/14/core/events.html#sqlalchemy.events.ConnectionEvents.handle_error
>>  and then set invalidate_pool_on_disconnect to False: 
>> https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=invalidate_pool_on_disconnect#sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect
>>  .
>> 
>> 
>> 
>> On Fri, Nov 26, 2021, at 11:51 AM, Mike Bayer wrote:
>>> Im not sure if I understand the question?   if you don't call 
>>> .invalidate(), then the connection is not invalidated.
>>> 
>>> what does "turn off" mean ?
>>> 
>>> 
>>> 
>>> On Fri, Nov 26, 2021, at 11:17 AM, Anupama Goparaju wrote:
 Hi,
 
 Is there a way to safely turn off connection invalidation 
 functionality (based on invalidation time set, all the connections 
 created prior to the timestamp are invalidated) in sqlalchemy?
 
 https://github.com/Noethys/Connecthys/blob/master/connecthys/lib/sqlalchemy/pool.py#L574
 
 Thanks,
 Anupama
 
 
 -- 
 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 

Re: [sqlalchemy] Turn off connections invalidation functionality

[Anupama Goparaju]

Thanks. Looks like this works but we need to set it for every exception 
scenario. Just confirming if the exception_context is thread safe to set 
the attribute.
Also, does this setting prevent pool invalidation on all sort of exceptions 
related to connections?

@event.listens_for(Engine, 'handle_error')
def receive_handle_error(exception_context):
 if exception_context.invalidate_pool_on_disconnect:
 exception_context.invalidate_pool_on_disconnect = False


On Thursday, January 6, 2022 at 10:03:55 AM UTC-8 Anupama Goparaju wrote:

> Thanks, i will give it a try.
>
> On Thursday, January 6, 2022 at 5:23:09 AM UTC-8 Mike Bayer wrote:
>
>> I can't guarantee that overriding private methods is safe, no.
>>
>> there's a public API to disable errors resulting in invalidation, I 
>> suggest you use that.
>>
>>
>>
>> On Wed, Jan 5, 2022, at 7:35 PM, Anupama Goparaju wrote:
>>
>> I haven't read the response before and tried to skip the lib logic by 
>> overriding the function below in my child call extending the NullPool to do 
>> nothing. Is this safe to do?
>>
>> def _invalidate(self, connection, exception=None, _checkin=True):
>>   pass
>>
>> On Wednesday, January 5, 2022 at 4:33:01 PM UTC-8 Anupama Goparaju wrote:
>>
>> Great, thanks for the info.
>>
>> On Friday, November 26, 2021 at 9:30:02 AM UTC-8 Mike Bayer wrote:
>>
>>
>> I've spent some time thinking about what might be being asked here.
>> the only thing I can think of is that when a particular database connection 
>> is found to be in what we call a "disconnect" state, the connection is 
>> invalidated, so that the connection will reconnect and make a new 
>> connection.   But also, this operation will typically assume the 
>> "disconnect" condition is that the database was restarted, or some other 
>> network condition has probably made all the connections that are pooled 
>> also invalid.   So the entire pool will be invalidated in this case as well.
>>
>> Why someone might want to turn that off is if they are getting lots of 
>> invalidated connections for some other reason and they are not able to 
>> solve that problem, so they'd like the pool to not be invalidated totally.  
>> This means that if the database is restarted, and for example you have 20 
>> pooled connections, you will in a high-request environment get up to 20 
>> server errors unless pool_pre_ping is turned on so that the connections are 
>> refreshed one at at time.
>>
>> To disable the pool invalidation upon receipt of a single connection 
>> shown to be in a disconnect, implement the handle_error event: 
>> https://docs.sqlalchemy.org/en/14/core/events.html#sqlalchemy.events.ConnectionEvents.handle_error
>>  
>> and then set invalidate_pool_on_disconnect to False: 
>> https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=invalidate_pool_on_disconnect#sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect
>>  
>> .
>>
>>
>>
>> On Fri, Nov 26, 2021, at 11:51 AM, Mike Bayer wrote:
>>
>> Im not sure if I understand the question?   if you don't call 
>> .invalidate(), then the connection is not invalidated.
>>
>> what does "turn off" mean ?
>>
>>
>>
>> On Fri, Nov 26, 2021, at 11:17 AM, Anupama Goparaju wrote:
>>
>> Hi,
>>
>> Is there a way to safely turn off connection invalidation functionality 
>> (based on invalidation time set, all the connections created prior to the 
>> timestamp are invalidated) in sqlalchemy?
>>
>>
>> https://github.com/Noethys/Connecthys/blob/master/connecthys/lib/sqlalchemy/pool.py#L574
>>
>> Thanks,
>> Anupama
>>
>>
>> -- 
>> 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+...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/07c76e07-17d4-4cea-9e42-1965ad80a06fn%40googlegroups.com
>>  
>> 
>> .
>>
>>
>>
>> -- 
>> 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+...@googlegroups.com.
>>
>> To view this discussion on the web visit 
>> 

[sqlalchemy] Re: [Proposal] Some simple high-level feature to set connection timeouts

[Jonathan Vanasco]

SQLAlchemy supports connection timeouts to establish a connection already.

SQLAlchemy does not, and can not, support query timeouts.  This is possible 
with some python database drivers, but very rare.  In every Python database 
program/library query timeouts are typically handled on the database 
server, and almost never on Python.  You can use SQLAlchemy's engine events 
to emit sql that will set/clear a timeout on the database server.

This has come up many times in the past, and there are several threads in 
the group history that explain the details and offer solutions.

On Monday, January 10, 2022 at 4:52:49 AM UTC-5 donnill...@gmail.com wrote:

> Hi!
> Sorry for bothering, I have not enough skills to contribute yet (
> But...
> I think it would be great to have some feature to easily set connection 
> timeouts in SQLAlchemy 2.0.
> The reason is I faced a problem where I was unable to cancel 
> some erroneous time-consuming queries with SQLAlchemy Core. And I guess, I 
> am not the only one.
> My straightforward nooby solution so far is this:
>
> from threading import Timer
>
> with engine.connect() as connection:
>   timeout = Timer(MAX_EXECUTION_TIME, lambda: 
> connection.connection.connection.cancel())
>   timeout.start()
>   r = connection.execute(stmt).freeze() # I just love FrozenResult)
>   timeout.cancel()
>
> The bad thing this is dialect-specific and works only due to cancel() 
> method in psycopg2
> I was also trying to benefit from handling sqlalchemy.events but failed...
> One of my intentions was to modify before_execute() method to catch 
> unintended cartesian product queries and raise error instead of throwing 
> warning.
> Unfortunately, at the moment this feels like too low-level for me.
>
> What I wish to have is something like this:
>
> with engine.connect(timeout=MAX_EXECUTION_TIME) as connection:
>   r = connection.execute(stmt)
>
> I hope somebody smart enough could seize time to think about it.
> This would make me happy. 
>
> Thanks in advance!
>
>
>
> [image: thanks-in-advance.png]
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/82b228ef-338f-4876-9cc4-4eb998dc04fan%40googlegroups.com.

[sqlalchemy] RE: builtins.NoneType' is not mapped

[Clive Swan]

Greetings,

I am getting the following error, any suggestions would be welcomed.
When running:  

deleteVersionStamp self.session.delete(rec)   File 
“\Externals\win32\Lib\site-packages\sqlalchemy\orm\scoping.py”, line 162, 

in do return getattr(self.registry(), name)(*args, **kwargs)   File " 
\Externals\win32\Lib\site-packages\sqlalchemy\orm\session.py", line 2014, 

in delete util.raise_(   File " 
\Externals\win32\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in 
raise_ raise exception sqlalchemy.orm.exc.UnmappedInstanceError: Class 
'builtins.NoneType' is not mapped

Class 'builtins.NoneType' is not mapped 
Is this a permissions error or something else??

Thanks,
Clive

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/27e4ccfc-236e-4758-a013-63cd8cc03c4dn%40googlegroups.com.

[sqlalchemy] Re: [Proposal] Some simple high-level feature to set connection timeouts

[Don Nillo]

UPD: found out cx_Oracle also has Connection.cancel() method. May be things 
are not that bad

On Monday, January 10, 2022 at 12:52:49 PM UTC+3 Don Nillo wrote:

> Hi!
> Sorry for bothering, I have not enough skills to contribute yet (
> But...
> I think it would be great to have some feature to easily set connection 
> timeouts in SQLAlchemy 2.0.
> The reason is I faced a problem where I was unable to cancel 
> some erroneous time-consuming queries with SQLAlchemy Core. And I guess, I 
> am not the only one.
> My straightforward nooby solution so far is this:
>
> from threading import Timer
>
> with engine.connect() as connection:
>   timeout = Timer(MAX_EXECUTION_TIME, lambda: 
> connection.connection.connection.cancel())
>   timeout.start()
>   r = connection.execute(stmt).freeze() # I just love FrozenResult)
>   timeout.cancel()
>
> The bad thing this is dialect-specific and works only due to cancel() 
> method in psycopg2
> I was also trying to benefit from handling sqlalchemy.events but failed...
> One of my intentions was to modify before_execute() method to catch 
> unintended cartesian product queries and raise error instead of throwing 
> warning.
> Unfortunately, at the moment this feels like too low-level for me.
>
> What I wish to have is something like this:
>
> with engine.connect(timeout=MAX_EXECUTION_TIME) as connection:
>   r = connection.execute(stmt)
>
> I hope somebody smart enough could seize time to think about it.
> This would make me happy. 
>
> Thanks in advance!
>
>
>
> [image: thanks-in-advance.png]
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c086eeea-cc4c-41de-ac7c-7f90811c63a6n%40googlegroups.com.

[sqlalchemy] Re: Create association of three or more tables

[fco...@gmail.com]

Hi,

A colleague helped me to write the relationship() part, the working code 
with relationships is now :

class UserOrgRole(Base):
__tablename__ = "user_org_role"

user_id = Column(ForeignKey("user.id", ondelete="CASCADE"), primary_key=True
)
org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True)
role_id = Column(ForeignKey("role.id", ondelete="CASCADE"), primary_key=True
)

user = relationship("User", back_populates="orgsroles")
org = relationship("Org", back_populates="usersroles")
role = relationship("Role", back_populates="usersorgs")

class User(Base):
__tablename__ = "user"

id = Column(Integer, primary_key=True)
username = Column(Text, index=True, nullable=False)
fullname = Column(Text, nullable=False)
account_type = Column(Text, nullable=False)

orgsroles = relationship("UserOrgRole", back_populates="user")

class Org(Base):
__tablename__ = "org"

id = Column(Integer, primary_key=True)
name = Column(Text, index=True, nullable=False)
slug = Column(Text, index=True, nullable=False)
created_at = Column(DateTime)

usersroles = relationship("UserOrgRole", back_populates="org")

class Role(Base):
__tablename__ = "role"

id = Column(Integer, primary_key=True)
name = Column(Text, index=True, nullable=False)

usersorgs = relationship("UserOrgRole", back_populates="role")

Have a nice week
Françoise

Le vendredi 7 janvier 2022 à 16:16:58 UTC+1, fco...@gmail.com a écrit :

> Hi all,
>
> I would like to create some association of, at least, 3 tables User, Org 
> (organisation) and Role : a User is given a Role on an Organisation.
>
> So I began writing the following snippet but I am stucked as I do not know 
> how I should write relationship() for User to be able to refer Orgs and 
> Roles, Org to refer Users and Roles ...
>
> I would like to avoid tricky code, do you have some advice on how I should 
> do ?
>
> # 
> https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object
> from datetime import datetime
> from sqlalchemy import create_engine
> from sqlalchemy import Column, ForeignKey, Integer, Text, DateTime
> from sqlalchemy.orm import declarative_base, relationship
> from sqlalchemy.orm.session import Session
>
> Base = declarative_base()
>
> class UserOrgRole(Base):
> __tablename__ = "user_org_role"
>
> user_id = Column(
> ForeignKey("user.id", ondelete="CASCADE"), primary_key=True
> )
> org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True
> )
> # Does it make sense ?
> role_id = Column(
> ForeignKey("role.id", ondelete="CASCADE"), primary_key=True
> )
>
> # I do not know what the relationship could back populate, org or role ???
> user = relationship("User")
> org = relationship("Org")
> role = relationship("Role")
>
>
> class User(Base):
> __tablename__ = "user"
>
> id = Column(Integer, primary_key=True)
> username = Column(Text, index=True, nullable=False)
> fullname = Column(Text, nullable=False)
> account_type = Column(Text, nullable=False)
>
> def __repr__(self):
> return (
> f" f"account_type={self.account_type})>"
> )
>
>
> class Org(Base):
> __tablename__ = "org"
>
> id = Column(Integer, primary_key=True)
> name = Column(Text, index=True, nullable=False)
> slug = Column(Text, index=True, nullable=False)
> created_at = Column(DateTime)
>
> def __repr__(self):
> return (
> f" f"created_at={self.created_at})>"
> )
>
>
> class Role(Base):
> __tablename__ = "role"
>
> id = Column(Integer, primary_key=True)
> name = Column(Text, index=True, nullable=False)
>
> def __repr__(self):
> return f""
>
>
> if __name__ == "__main__":
> engine = create_engine(
> "sqlite:///association_object_ternary.db", echo=False
> )
>
> Base.metadata.drop_all(engine)
> Base.metadata.create_all(engine)
>
> with Session(engine) as session:
> # create parent, append a child via association
> u1 = User(
> username="jlondon",
> fullname="Jack London",
> account_type="member",
> )
> o1 = Org(name="o1", slug="o1", created_at=datetime.utcnow())
> owner = Role(name="owner")
>
> uor1 = UserOrgRole()
> uor1.user = u1
> uor1.org = o1
> uor1.role = owner
>
> with session.begin():
> session.add(u1)
> session.add(o1)
> session.add(owner)
>
> session.add(uor1)
>
> Thanks for your help and for this nice library which has a great 
> documentation.
> Françoise
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/771739c6-e842-4ff2-8364-be20599bbea9n%40googlegroups.com.