0
# Database Connections and Pooling
1

2
Core connection functionality providing MySQL database connectivity with support for connection pooling, failover configurations, SSL/TLS encryption, and various authentication methods.
3

4
## Capabilities
5

6
### Basic Connection
7

8
Creates a standard MySQL connection using pure Python implementation or optional C extension.
9

10
```python { .api }
11
def connect(**config):
12
    """
13
    Create a MySQL connection.
14
    
15
    Parameters:
16
    - user (str): MySQL username
17
    - password (str): MySQL password
18
    - host (str): MySQL server hostname (default: 'localhost')
19
    - port (int): MySQL server port (default: 3306)
20
    - database (str): Database name to connect to
21
    - charset (str): Character set for the connection (default: 'utf8')
22
    - use_pure (bool): Use pure Python implementation (default: True)
23
    - ssl_disabled (bool): Disable SSL/TLS (default: False)
24
    - autocommit (bool): Enable autocommit mode (default: False)
25
    - time_zone (str): Session time zone
26
    - connect_timeout (int): Connection timeout in seconds
27
    - read_timeout (int): Read timeout in seconds
28
    - write_timeout (int): Write timeout in seconds
29
    - compress (bool): Enable connection compression
30
    - unix_socket (str): Unix socket file path
31
    - auth_plugin (str): Authentication plugin name
32
    - collation (str): Connection collation
33
    - sql_mode (str): SQL mode for the session
34
    - init_command (str): Command to execute on connection
35
    - use_unicode (bool): Return Unicode strings (default: True)
36
    
37
    Returns:
38
    MySQLConnection or CMySQLConnection: Database connection object
39
    
40
    Raises:
41
    Error: Connection failed or invalid parameters
42
    """
43
```
44

45
**Usage Example:**
46

47
```python
48
import mysql.connector
49

50
# Basic connection
51
connection = mysql.connector.connect(
52
    user='myuser',
53
    password='mypassword',
54
    host='localhost',
55
    database='mydatabase'
56
)
57

58
# Connection with SSL
59
connection = mysql.connector.connect(
60
    user='myuser',
61
    password='mypassword',
62
    host='myserver.com',
63
    database='mydatabase',
64
    ssl_cert='/path/to/client-cert.pem',
65
    ssl_key='/path/to/client-key.pem',
66
    ssl_ca='/path/to/ca-cert.pem'
67
)
68
```
69

70
### Connection Classes
71

72
Core connection implementations providing database connectivity and transaction management.
73

74
```python { .api }
75
class MySQLConnection:
76
    """
77
    Pure Python MySQL connection implementation.
78
    
79
    Provides complete MySQL protocol implementation without external dependencies.
80
    Supports all MySQL features including prepared statements, multiple result sets,
81
    and comprehensive error handling.
82
    """
83
    
84
    def __init__(self, **config):
85
        """Initialize connection with configuration parameters"""
86
    
87
    def connect(self, **config):
88
        """Establish connection to MySQL server"""
89
    
90
    def close(self):
91
        """Close the connection to MySQL server"""
92
    
93
    def commit(self):
94
        """Commit current transaction"""
95
    
96
    def rollback(self):
97
        """Rollback current transaction"""
98
    
99
    def cursor(self, buffered=None, raw=None, prepared=None, 
100
              cursor_class=None, dictionary=None, named_tuple=None):
101
        """
102
        Create a cursor for executing SQL statements.
103
        
104
        Parameters:
105
        - buffered (bool): Buffer all results immediately
106
        - raw (bool): Return raw data without conversion
107
        - prepared (bool): Use prepared statements
108
        - cursor_class: Custom cursor class
109
        - dictionary (bool): Return rows as dictionaries
110
        - named_tuple (bool): Return rows as named tuples
111
        
112
        Returns:
113
        MySQLCursor: Cursor object for SQL operations
114
        """
115
    
116
    def start_transaction(self, consistent_snapshot=False, isolation_level=None, readonly=None):
117
        """
118
        Start a transaction.
119
        
120
        Parameters:
121
        - consistent_snapshot (bool): Use consistent snapshot
122
        - isolation_level (str): Transaction isolation level
123
        - readonly (bool): Read-only transaction
124
        """
125
    
126
    def in_transaction(self):
127
        """Check if connection is in a transaction"""
128
    
129
    def autocommit(self, value):
130
        """Set autocommit mode"""
131
    
132
    def get_server_info(self):
133
        """Get MySQL server version information"""
134
    
135
    def get_server_version(self):
136
        """Get MySQL server version as tuple"""
137
    
138
    def ping(self, reconnect=True, attempts=1, delay=0):
139
        """
140
        Ping the MySQL server.
141
        
142
        Parameters:
143
        - reconnect (bool): Attempt to reconnect if connection lost
144
        - attempts (int): Number of ping attempts
145
        - delay (int): Delay between attempts
146
        """
147
    
148
    def reset_session(self, user_variables=None, session_variables=None):
149
        """Reset session state"""
150
    
151
    def set_charset_collation(self, charset=None, collation=None):
152
        """Set character set and collation"""
153

154
class CMySQLConnection(MySQLConnection):
155
    """
156
    C extension-based MySQL connection (optional).
157
    
158
    Provides better performance through C implementation while maintaining
159
    the same interface as MySQLConnection. Available only when C extension
160
    is compiled and installed.
161
    """
162
```
163

164
### Connection Pooling
165

166
Manages pools of database connections for improved performance and resource utilization.
167

168
```python { .api }
169
class MySQLConnectionPool:
170
    """
171
    Connection pool for managing multiple MySQL connections.
172
    
173
    Provides automatic connection lifecycle management, connection reuse,
174
    and configurable pool sizing for optimal resource utilization.
175
    """
176
    
177
    def __init__(self, pool_size=5, pool_name=None, pool_reset_session=True, **config):
178
        """
179
        Initialize connection pool.
180
        
181
        Parameters:
182
        - pool_size (int): Maximum number of connections in pool (default: 5)
183
        - pool_name (str): Unique name for the pool
184
        - pool_reset_session (bool): Reset session variables on connection return
185
        - **config: Connection configuration parameters
186
        """
187
    
188
    def get_connection(self):
189
        """
190
        Get a connection from the pool.
191
        
192
        Returns:
193
        PooledMySQLConnection: Pooled connection wrapper
194
        
195
        Raises:
196
        PoolError: No connections available
197
        """
198
    
199
    def add_connection(self, cnx=None):
200
        """Add a connection to the pool"""
201
    
202
    def set_config(**config):
203
        """Update pool configuration"""
204

205
class PooledMySQLConnection:
206
    """
207
    Wrapper for pooled MySQL connections.
208
    
209
    Provides the same interface as MySQLConnection while managing
210
    automatic return to pool when connection is closed.
211
    """
212
    
213
    def close(self):
214
        """Return connection to pool instead of closing"""
215
    
216
    def config(self, **kwargs):
217
        """Configure the pooled connection"""
218

219
def generate_pool_name(**config):
220
    """
221
    Generate a unique pool name based on connection configuration.
222
    
223
    Parameters:
224
    - **config: Connection configuration parameters
225
    
226
    Returns:
227
    str: Generated pool name
228
    """
229
```
230

231
**Usage Example:**
232

233
```python
234
import mysql.connector
235

236
# Create connection pool
237
config = {
238
    'user': 'myuser',
239
    'password': 'mypassword',
240
    'host': 'localhost',
241
    'database': 'mydatabase',
242
    'pool_name': 'mypool',
243
    'pool_size': 10
244
}
245

246
# Get pooled connection
247
connection = mysql.connector.connect(**config)
248
cursor = connection.cursor()
249

250
# Use connection normally
251
cursor.execute("SELECT * FROM users")
252
results = cursor.fetchall()
253

254
# Connection automatically returns to pool when closed
255
cursor.close()
256
connection.close()
257
```
258

259
### Failover and High Availability
260

261
Automatic failover support for high availability MySQL configurations.
262

263
```python { .api }
264
def connect(failover=None, **config):
265
    """
266
    Create connection with failover support.
267
    
268
    Parameters:
269
    - failover (list): List of failover server configurations
270
    - **config: Primary connection configuration
271
    
272
    Returns:
273
    MySQLConnection: Connection to available server
274
    
275
    Raises:
276
    InterfaceError: No servers available
277
    """
278
```
279

280
**Usage Example:**
281

282
```python
283
import mysql.connector
284

285
# Connection with failover servers
286
failover_config = {
287
    'user': 'myuser',
288
    'password': 'mypassword',
289
    'database': 'mydatabase',
290
    'failover': [
291
        {'host': 'primary.mysql.com', 'port': 3306},
292
        {'host': 'secondary.mysql.com', 'port': 3306},
293
        {'host': 'tertiary.mysql.com', 'port': 3306}
294
    ]
295
}
296

297
connection = mysql.connector.connect(**failover_config)
298
```
299

300
### Option Files Support
301

302
Read MySQL configuration from option files (my.cnf, my.ini).
303

304
```python { .api }
305
def read_option_files(**config):
306
    """
307
    Read MySQL option files and merge with provided configuration.
308
    
309
    Parameters:
310
    - option_files (list): List of option file paths to read
311
    - option_groups (list): Option groups to read from files
312
    - **config: Additional configuration parameters
313
    
314
    Returns:
315
    dict: Merged configuration from files and parameters
316
    """
317
```
318

319
**Usage Example:**
320

321
```python
322
import mysql.connector
323

324
# Connect using option files
325
connection = mysql.connector.connect(
326
    option_files=['/etc/mysql/my.cnf', '~/.my.cnf'],
327
    option_groups=['client', 'mysql']
328
)
329
```
330

331
## Types
332

333
```python { .api }
334
ConnectionPoolConfig = {
335
    'pool_name': str,
336
    'pool_size': int,
337
    'pool_reset_session': bool
338
}
339

340
FailoverConfig = {
341
    'failover': list[dict[str, any]]
342
}
343

344
SSLConfig = {
345
    'ssl_disabled': bool,
346
    'ssl_cert': str,
347
    'ssl_key': str,
348
    'ssl_ca': str,
349
    'ssl_capath': str,
350
    'ssl_cipher': str,
351
    'ssl_verify_cert': bool,
352
    'ssl_verify_identity': bool
353
}
354
```