In this article, we will discuss how to change the SQLite connection timeout when connecting from Python.
What is a connection timeout and what causes it?
A connection timeout is an error that occurs when it takes too long for a server to respond to a user’s request.
Connection timeouts usually occur when there are multiple active connections to a database and one of them is performing an operation that involves modification of the data stored. In that case, the other connections have to wait until that operation is done before they can perform their own operations. When that waiting time crosses the time limit, it causes a connection timeout.
Changing the Connection Timeout value
This is a sample python code snippet that uses the sqlite3 package to create and connect to a database and then output its version.
Approach
- First, the inbuilt sqlite3 package is imported.
- Next, we use the connect method of the connector class to connect to the database, passing its name as a parameter.
- After that, using the cursor object of the connector class, we have to create a cursor instance that can execute our queries.
- The execute method then executes the query and returns the result.
- The results are then extracted from the cursor by using the fetchall method.
- And finally, irrespective of whether the query was executed successfully or not, both the database cursor and connection has to be closed.
Python3
# import moduleimport sqlite3try: # establish connection sqliteConnection = sqlite3.connect('sqlite.db') # create cursor object cursor = sqliteConnection.cursor() print('Database Initialization and Connection successful') # display version query = 'select sqlite_version();' cursor.execute(query) # get data record = cursor.fetchall() print(f'SQLite Version - {record}') cursor.close()except sqlite3.Error as error: print('Error occurred - ', error) finally: # If the connection was established then close it if sqliteConnection: sqliteConnection.close() print('SQLite Connection closed') |
Output:
Normally, the above code snippet would work perfectly fine. But when the database is already being used by another process, it’d have to wait until that process’s query is resolved before executing its own query. If that waiting time crosses the connection timeout value, then that results in a Connection Timeout.
The default value for connection timeout is 5 seconds. But it can be changed in the connect() method itself. It accepts an optional parameter timeout which accepts the value for connection timeout in seconds. The code snippet has now been modified to have a connection timeout of 20 seconds.
Python3
# import moduleimport sqlite3try: # establish connection sqliteConnection = sqlite3.connect('sqlite.db', timeout=20) # create cursor object cursor = sqliteConnection.cursor() print('Database Initialization and Connection successful') # display version query = 'select sqlite_version();' cursor.execute(query) # get data record = cursor.fetchall() print(f'SQLite Version - {record}') cursor.close()except sqlite3.Error as error: print('Error occurred - ', error)finally: # If the connection was established then # close it if sqliteConnection: sqliteConnection.close() print('SQLite Connection closed') |
Output:
In the above example, the python program will be connected to the SQLite database for 20 seconds as the timeout parameter in connect() method is assigned to 20. In this way, one can change SQLite connection timeout when connecting from python.
