- 1 year ago
Implement connection pooling with Python for connecting to SQL Server on Windows
To implement connection pooling with Python for connecting to SQL Server on Windows, you can use the pyodbc
library along with the pyodbc.pooling
module. Here's an example:
pythonimport pyodbc
from pyodbc import pool
# Set up the connection pool
connection_pool = pool.QueuePool(
creator=pyodbc.connect,
driver="{SQL Server Native Client 11.0}",
server="your_server_name",
database="your_database_name",
trusted_connection="yes",
mincached=1, # Minimum number of connections in the pool
maxcached=10, # Maximum number of connections in the pool
maxconnections=10, # Maximum number of open connections
autocommit=True # Set autocommit to True for connection pooling
)
# Example usage
def execute_query(sql):
with connection_pool.connect() as conn:
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
return result
# Test the connection pooling
result = execute_query("SELECT * FROM your_table")
print(result)
In this example, we use the pyodbc.pool.QueuePool
class to create a connection pool. The mincached
parameter specifies the minimum number of connections to keep in the pool, and maxcached
specifies the maximum number of connections. maxconnections
sets the maximum number of open connections allowed. Adjust these values based on your requirements.
The creator
parameter is set to pyodbc.connect
, which is the function used to create new connections in the pool. You can specify the necessary connection parameters such as the driver, server, database, and authentication method.
The execute_query
function demonstrates how to use the connection pool to execute SQL queries. It acquires a connection from the pool using the with
statement and releases it automatically after the block is executed.
Make sure you have the pyodbc
library installed. You can install it using pip:
pip install pyodbc
Adjust the connection parameters in the code to match your SQL Server configuration.
By using connection pooling, you can reuse existing connections rather than creating new ones for each query, resulting in better performance and resource utilization when working with SQL Server in your Python application.