+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: python script is hanging while calling a procedure in database (/thread-41441.html)
python script is hanging while calling a procedure in database - prasanthi417 - Jan-16-2024
Need help in how to maintain database connection alive
My python script is connecting to Oracle DB and is calling a stored procedure( this procedure will deletes data from table).The time taken for deletion of records from the table is taking more than 2 hrs.
After that python script is not executing the next set of commands, it is getting hanged in calling the procedure
please help how to fix this
My code:
import cx_Oracle
conn_str = "username/password@host:port/service"
d_dbh = cx_Oracle.connect(conn_str)
d_cursor = d_dbh.cursor()
d_cursor.execute(procedure_name)
d_cursor.execute("select count(1) from table_name)
delete_count = d_cursor.fetchone()[0]
print(delete_count)As per the logs, the python script is hanged at calling that procedure. verified the logs from database end the records got deleted from table after 2hrs. but after 2hr the python script did not execute the next select statements.Please help if this a session time out?
RE: python script is hanging while calling a procedure in database - deanhystad - Jan-16-2024
Have you tried reconnecting?
RE: python script is hanging while calling a procedure in database - prasanthi417 - Jan-17-2024
(Jan-16-2024, 03:49 PM)deanhystad Wrote: Have you tried reconnecting?
(Jan-17-2024, 12:34 PM)prasanthi417 Wrote: Before calling the procedure I am establishing conn to database
RE: python script is hanging while calling a procedure in database - sgrey - Jan-17-2024
- What interface are you using to manage the database? Does it hang or experience slowness?
- Did you try another language, does it also hangs or just python?
- Is your table large or did you indexes get dropped? Do you have dependencies in the data? Does this procedure execute properly if you do it through your DBMS interface?
- Have you look at the driver documentation, perhaps there is a known bug? Are you using the latest version? Is your driver version compatible with you python version?
- Check your connection string. Maybe there is something there that makes it behave weirdly. Try creating it in a different way.
- What OS are you running? Do you have a firewall/antivirus thing running? Perhaps they are killing your connection, try to disable them and see what happens.
- In my experience sometimes databases can work better if you "ping" them. Try to connect, and then disconnect and reconnect. Does it make any difference? How long does it take to connect?
- Is you DB hosted on your computer? Is it on a separate disk? Maybe that disk has issues/bad sectors/is dying. Check it's health.
RE: python script is hanging while calling a procedure in database - deanhystad - Jan-17-2024
Have you tried making a new connection to the database after calling the procedure? Maybe the database dropped the old connection after 2 hours of inactivity.