CODEWITHSUNDEEP
python-3.xoracle-databasesqlalchemycx-oracle
Liviu
Liviu

Reputation: 1093

Get access to SQLAlchemy connection socket to prevent firewall timeouts

I am having some issues with my database connections. They seem to be abruptly severed by some firewall in the network and a nice way to fix this at the TCP level seems to be to configure keepalive for a particular TCP socket. The problem is I have no idea how and if it is possible to gain access to the connections managed by SQLAlchemy. I am using it to connect to an Oracle database. This is enabled by the use of cx_Oracle and Oracle's Instant Client which is also installed.

Upvotes: 0

Views: 715

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10641

Use the SQL*Net option EXPIRE_TIME as documented in the cx_Oracle doc. EXPIRE_TIME can be used to prevent firewalls from terminating idle connections and to adjust keepalive timeouts. The general recommendation for EXPIRE_TIME is to use a value that is slightly less than half of the termination period.

The details:

  • With 18c client libraries it can be added as (EXPIRE_TIME=n) to the DESCRIPTION section of a connect descriptor (in a full connect descriptor string in the app, or in the tnsnames.ora file).
  • With 19c client libraries it can be used via Easy Connect: host/service?expire_time=n.
  • With 21c client libraries it can be used in a client-side (i.e. Python machine) sqlnet.ora.

With older client versions you can use ENABLE=BROKEN instead.

Also consider using a connection pool with SQLAlchemy, since this will do some checks and automatically re-establish connections if they have been killed. Pools are also great for performance in heavily used, long running apps.

Upvotes: 1

Related Questions