0
# MySQL Connector/Python
1

2
A comprehensive Python database driver for MySQL servers that provides both synchronous and asynchronous connectivity options. It implements the Python Database API Specification v2.0 (PEP 249) and includes a high-performance C extension for improved speed, making it suitable for enterprise applications, web frameworks, data analysis tools, and any Python application requiring robust MySQL database connectivity.
3

4
## Package Information
5

6
- **Package Name**: mysql-connector-python
7
- **Language**: Python
8
- **Installation**: `pip install mysql-connector-python`
9
- **Requires**: Python 3.9-3.13
10
- **License**: GNU GPLv2 (with FOSS License Exception)
11

12
## Core Imports
13

14
```python { .api }
15
import mysql.connector
16
```
17

18
For async operations:
19

20
```python { .api }
21
import mysql.connector.aio
22
```
23

24
## Basic Usage
25

26
```python
27
import mysql.connector
28

29
# Connect to MySQL database
30
connection = mysql.connector.connect(
31
    host='localhost',
32
    user='username',
33
    password='password',
34
    database='mydatabase'
35
)
36

37
# Create cursor and execute query
38
cursor = connection.cursor()
39
cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
40
results = cursor.fetchall()
41

42
# Clean up
43
cursor.close()
44
connection.close()
45
```
46

47
## Architecture
48

49
MySQL Connector/Python is organized around several key components:
50

51
- **Connection Management**: Core connection classes with optional C extension optimization
52
- **Cursor System**: Multiple cursor types for different data access patterns  
53
- **Connection Pooling**: Efficient connection pool management for high-performance applications
54
- **Authentication**: Comprehensive plugin system supporting all MySQL authentication methods
55
- **Error Handling**: Complete exception hierarchy matching MySQL server and client errors
56
- **Type Conversion**: Robust bidirectional conversion between Python and MySQL data types
57

58
## Core Connection Management
59

60
Establish and manage database connections with comprehensive configuration options.
61

62
```python { .api }
63
from typing import Any, List, Optional, Sequence, Tuple, Union
64

65
# Main connection function
66
def connect(**kwargs) -> Union[MySQLConnection, PooledMySQLConnection]:
67
    """Create connection to MySQL server with optional pooling."""
68
    pass
69

70
# Connection classes
71
class MySQLConnection:
72
    """Main synchronous connection class implementing DB-API 2.0."""
73
    pass
74

75
class CMySQLConnection:
76
    """C Extension optimized connection class (when available)."""
77
    pass
78

79
# Query Attributes API
80
def query_attrs() -> List[Tuple[str, Any]]: pass
81
def query_attrs_append(value: Tuple[str, Any]) -> None: pass
82
def query_attrs_remove(name: str) -> Any: pass
83
def query_attrs_clear() -> None: pass
84

85
# Server Information API
86
def get_server_info() -> Optional[str]: pass
87
def get_server_version() -> Optional[Tuple[int, ...]]: pass
88

89
# Security and State API
90
def is_secure() -> bool: pass
91
def have_next_result() -> bool: pass
92
def set_client_flags(flags: Union[int, Sequence[int]]) -> int: pass
93
```
94

95
Key connection parameters:
96
- `host`: MySQL server host (default: 'localhost')
97
- `port`: MySQL server port (default: 3306)
98
- `user`: Username for authentication
99
- `password`: Password for authentication
100
- `database`: Database name to connect to
101
- `charset`: Connection character set (default: 'utf8mb4')
102
- `use_unicode`: Enable Unicode support (default: True)
103
- `ssl_disabled`: Disable SSL/TLS (default: False)
104
- `autocommit`: Enable autocommit mode (default: False)
105

106
[Connection Management](./connection.md)
107

108
## Cursor Operations
109

110
Execute SQL statements and retrieve results with various cursor types optimized for different use cases.
111

112
```python { .api }
113
# Standard cursors
114
class MySQLCursor:
115
    """Standard cursor for executing SQL statements."""
116
    def execute(self, operation: str, params: Optional[Sequence] = None) -> None: pass
117
    def fetchone(self) -> Optional[Tuple]: pass
118
    def fetchall(self) -> List[Tuple]: pass
119
    def fetchmany(self, size: int = 1) -> List[Tuple]: pass
120

121
class MySQLCursorDict:
122
    """Dictionary cursor returning results as dictionaries."""
123
    def fetchone(self) -> Optional[Dict]: pass
124
    def fetchall(self) -> List[Dict]: pass
125

126
class MySQLCursorPrepared:
127
    """Prepared statement cursor for repeated execution."""
128
    pass
129
```
130

131
Available cursor types:
132
- `MySQLCursor`: Standard tuple-based results
133
- `MySQLCursorBuffered`: Buffered results for immediate fetching
134
- `MySQLCursorDict`: Dictionary-based results with column names as keys
135
- `MySQLCursorRaw`: Raw results without automatic type conversion
136
- `MySQLCursorPrepared`: Prepared statements for repeated execution
137

138
[Cursor Operations](./cursors.md)
139

140
## Connection Pooling
141

142
Manage connection pools for high-performance applications with automatic connection lifecycle management.
143

144
```python { .api }
145
class MySQLConnectionPool:
146
    """Connection pool manager with configurable size and behavior."""
147
    def __init__(self, pool_name: str, pool_size: int = 5, **kwargs): pass
148
    def get_connection(self) -> PooledMySQLConnection: pass
149
    def close(self) -> None: pass
150

151
class PooledMySQLConnection:
152
    """Pooled connection wrapper that returns to pool on close."""
153
    pass
154

155
def connect(**kwargs) -> PooledMySQLConnection:
156
    """Create pooled connection when pool parameters provided."""
157
    pass
158
```
159

160
Pool configuration parameters:
161
- `pool_name`: Unique identifier for the connection pool
162
- `pool_size`: Maximum number of connections in pool (default: 5)
163
- `pool_reset_session`: Reset session variables on connection reuse (default: True)
164
- `pool_timeout`: Maximum time to wait for available connection
165

166
[Connection Pooling](./pooling.md)
167

168
## Asynchronous Operations
169

170
Perform database operations asynchronously using asyncio with full async/await support.
171

172
```python { .api }
173
import mysql.connector.aio
174

175
async def connect(**kwargs) -> MySQLConnection:
176
    """Create async connection to MySQL server."""
177
    pass
178

179
class MySQLConnection:
180
    """Async connection class with asyncio support."""
181
    async def connect(self) -> None: pass
182
    async def disconnect(self) -> None: pass
183
    def cursor(self, **kwargs) -> MySQLCursor: pass
184

185
class MySQLCursor:
186
    """Async cursor for executing SQL statements."""
187
    async def execute(self, operation: str, params: Optional[Sequence] = None) -> None: pass
188
    async def fetchone(self) -> Optional[Tuple]: pass
189
    async def fetchall(self) -> List[Tuple]: pass
190
```
191

192
[Async Operations](./async.md)
193

194
## Error Handling
195

196
Comprehensive exception hierarchy for robust error handling and debugging.
197

198
```python { .api }
199
# Base exception classes
200
class Error(Exception):
201
    """Base exception class for all MySQL Connector errors."""
202
    pass
203

204
class DatabaseError(Error):
205
    """Database-related errors."""
206
    pass
207

208
class OperationalError(DatabaseError):
209
    """Operation-related errors (connection, queries)."""
210
    pass
211

212
class ProgrammingError(DatabaseError):
213
    """Programming errors (SQL syntax, parameter issues)."""
214
    pass
215

216
class IntegrityError(DatabaseError):
217
    """Database constraint violations."""
218
    pass
219

220
class DataError(DatabaseError):
221
    """Data processing errors."""
222
    pass
223

224
def custom_error_exception(**kwargs) -> Type[Error]:
225
    """Create custom error exceptions."""
226
    pass
227
```
228

229
[Error Handling](./errors.md)
230

231
## Data Types and Conversion
232

233
Handle data type conversion between Python and MySQL with comprehensive type support.
234

235
```python { .api }
236
# DB-API 2.0 type constructors
237
def Date(year: int, month: int, day: int) -> datetime.date: pass
238
def Time(hour: int, minute: int, second: int) -> datetime.time: pass
239
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int) -> datetime.datetime: pass
240
def Binary(string: bytes) -> bytes: pass
241

242
# Timestamp constructors from Unix time
243
def DateFromTicks(ticks: float) -> datetime.date: pass
244
def TimeFromTicks(ticks: float) -> datetime.time: pass
245
def TimestampFromTicks(ticks: float) -> datetime.datetime: pass
246

247
# Type objects for type comparison
248
STRING: Type
249
BINARY: Type
250
NUMBER: Type
251
DATETIME: Type
252
ROWID: Type
253

254
# Custom types
255
class HexLiteral:
256
    """Represents MySQL hexadecimal literals."""
257
    def __init__(self, value: Union[str, bytes]): pass
258
```
259

260
[Data Types and Conversion](./types.md)
261

262
## Constants and Enumerations
263

264
MySQL-specific constants for field types, flags, character sets, and configuration.
265

266
```python { .api }
267
class FieldType:
268
    """MySQL field type constants."""
269
    DECIMAL: int
270
    TINY: int
271
    SHORT: int
272
    LONG: int
273
    FLOAT: int
274
    DOUBLE: int
275
    NULL: int
276
    TIMESTAMP: int
277
    LONGLONG: int
278
    INT24: int
279
    DATE: int
280
    TIME: int
281
    DATETIME: int
282
    YEAR: int
283
    VARCHAR: int
284
    BIT: int
285
    JSON: int
286
    NEWDECIMAL: int
287
    ENUM: int
288
    SET: int
289
    BLOB: int
290
    STRING: int
291
    GEOMETRY: int
292

293
class FieldFlag:
294
    """MySQL field flag constants."""
295
    NOT_NULL: int
296
    PRI_KEY: int
297
    UNIQUE_KEY: int
298
    MULTIPLE_KEY: int
299
    BLOB: int
300
    UNSIGNED: int
301
    ZEROFILL: int
302
    BINARY: int
303
    ENUM: int
304
    AUTO_INCREMENT: int
305
    TIMESTAMP: int
306
    SET: int
307

308
class ClientFlag:
309
    """MySQL client capability flags."""
310
    pass
311

312
class CharacterSet:
313
    """MySQL character set utilities."""
314
    pass
315
```
316

317
[Constants and Enumerations](./constants.md)
318

319
## Authentication and Security
320

321
Support for all MySQL authentication methods including enterprise security features.
322

323
```python { .api }
324
# Authentication plugins available:
325
# - mysql_native_password
326
# - caching_sha2_password  
327
# - sha256_password
328
# - mysql_clear_password
329
# - authentication_kerberos_client
330
# - authentication_ldap_sasl_client
331
# - authentication_oci_client
332
# - authentication_webauthn_client
333

334
# SSL/TLS configuration
335
ssl_config = {
336
    'ssl_cert': '/path/to/client-cert.pem',
337
    'ssl_key': '/path/to/client-key.pem', 
338
    'ssl_ca': '/path/to/ca-cert.pem',
339
    'ssl_verify_cert': True,
340
    'ssl_verify_identity': True
341
}
342
```
343

344
[Authentication and Security](./auth.md)
345

346
## Utilities and Advanced Features
347

348
Low-level utilities, configuration management, and advanced functionality.
349

350
```python { .api }
351
# Binary data utilities
352
def intread(buf: bytes) -> int: pass
353
def int1store(value: int) -> bytes: pass
354
def int2store(value: int) -> bytes: pass
355
def int4store(value: int) -> bytes: pass
356
def int8store(value: int) -> bytes: pass
357

358
# String utilities  
359
def read_lc_string(buf: bytes) -> Tuple[bytes, int]: pass
360
def read_string(buf: bytes, end: bytes, charset: str) -> str: pass
361

362
# Platform utilities
363
def get_platform() -> str: pass
364

365
# Configuration
366
class MySQLOptionsParser:
367
    """MySQL option file parser for my.cnf files."""
368
    def read_option_files(self, files: List[str]) -> Dict: pass
369
```
370

371
[Utilities and Advanced Features](./utilities.md)