How do I detect a database timeout in python?

Question

I have some code that executes database queries, like so:

self.db_cursor = self.db_conn.cursor(buffered=False)
self.db_cursor.execute(query)

Now I want to add a timeout, so that long queries are killed. I can do this in MYSQL like this:

self.db_conn.reset_session(session_variables={'max_execution_time': 10})

(I deliberately set the timeout to be crazy short, for testing.)

How can I tell if a query timed out? I want to be able to report back to the user. There's no exception thrown, no warnings on the cursor, I can't find anything to check against.

SachaDee · Accepted Answer · 2025-03-19 15:13:43Z

0

Maybe with catching the Timeout Exception:

import mysql.connector

try:
    self.db_conn.reset_session(session_variables={'max_execution_time': 10})  # Timeout in milliseconds
    self.db_cursor = self.db_conn.cursor(buffered=False)
    
    query = "SELECT SLEEP(5)"  # Simulating a long query
    self.db_cursor.execute(query)
    
    results = self.db_cursor.fetchall()  # Or fetchone()
    print("Query executed successfully:", results)

except mysql.connector.errors.OperationalError as e:
    if "Query execution was interrupted" in str(e):
        print("Query timed out!")
    else:
        print("OperationalError:", e)

except mysql.connector.Error as e:
    print("MySQL Error:", e)

finally:
    self.db_cursor.close()

answered Mar 19 at 15:13

SachaDee

9,6833 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pecks Mar 20 at 9:18

This is what I thought (and I've tried it) but it doesn't thrown an exception. Will see if it's something to do with settings in MySQL.

Collectives™ on Stack Overflow

How do I detect a database timeout in python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related