0
# Database Connections
1
2
Database connection functionality for establishing, configuring, and managing MySQL database connections with comprehensive transaction control and connection lifecycle management.
3
4
## Capabilities
5
6
### Connection Factory Functions
7
8
Primary functions for creating database connections with flexible parameter handling.
9
10
```python { .api }
11
def connect(host=None, user=None, passwd=None, db=None, port=3306, unix_socket=None,
12
charset='latin1', sql_mode=None, read_default_file=None, conv=None,
13
use_unicode=None, client_flag=0, cursorclass=None, init_command=None,
14
connect_timeout=None, read_default_group=None, read_timeout=None,
15
write_timeout=None, compress=None, named_pipe=None, no_delay=None,
16
autocommit=False, db_type=None, **kwargs):
17
"""
18
Create a database connection.
19
20
Parameters:
21
- host (str): MySQL server hostname
22
- user (str): Username for authentication
23
- passwd (str): Password for authentication
24
- db (str): Database name to connect to
25
- port (int): TCP port number (default 3306)
26
- unix_socket (str): Unix socket path for local connections
27
- charset (str): Character set for connection (default 'latin1')
28
- sql_mode (str): SQL mode string
29
- read_default_file (str): MySQL configuration file path
30
- conv (dict): Custom type conversion dictionary
31
- use_unicode (bool): Enable Unicode support
32
- client_flag (int): Client connection flags
33
- cursorclass (class): Default cursor class for connection
34
- init_command (str): Command to execute on connection
35
- connect_timeout (int): Connection timeout in seconds
36
- autocommit (bool): Enable autocommit mode
37
38
Returns:
39
Connection: Database connection object
40
"""
41
42
# Aliases for connect function
43
Connect = connect
44
Connection = connect
45
```
46
47
### Connection Class
48
49
Main database connection class providing transaction control, cursor creation, and connection management.
50
51
```python { .api }
52
class Connection:
53
"""Database connection object providing DB API 2.0 interface."""
54
55
def __init__(self, *args, **kwargs):
56
"""Initialize database connection with parameters from connect()."""
57
58
def autocommit(self, on):
59
"""
60
Set autocommit mode.
61
62
Parameters:
63
- on (bool): True to enable autocommit, False to disable
64
"""
65
66
def begin(self):
67
"""Begin a new transaction explicitly."""
68
69
def commit(self):
70
"""Commit the current transaction."""
71
72
def rollback(self):
73
"""Rollback the current transaction."""
74
75
def cursor(self, cursorclass=None):
76
"""
77
Create a cursor object for executing queries.
78
79
Parameters:
80
- cursorclass (class, optional): Cursor class to use
81
82
Returns:
83
Cursor: New cursor object
84
"""
85
86
def close(self):
87
"""Close the database connection."""
88
89
def literal(self, o):
90
"""
91
Convert Python object to SQL literal representation.
92
93
Parameters:
94
- o: Python object to convert
95
96
Returns:
97
str: SQL literal string
98
"""
99
100
def set_character_set(self, charset):
101
"""
102
Set the character set for the connection.
103
104
Parameters:
105
- charset (str): Character set name (e.g., 'utf8', 'latin1')
106
"""
107
108
def set_sql_mode(self, sql_mode):
109
"""
110
Set SQL mode for the connection.
111
112
Parameters:
113
- sql_mode (str): SQL mode string
114
"""
115
116
def show_warnings(self):
117
"""
118
Show MySQL warnings from the last operation.
119
120
Returns:
121
tuple: Warning information
122
"""
123
124
def warning_count(self):
125
"""
126
Get the number of warnings from the last operation.
127
128
Returns:
129
int: Number of warnings
130
"""
131
132
def character_set_name(self):
133
"""
134
Get the default character set for the current connection.
135
136
Returns:
137
str: Character set name (e.g., 'latin1', 'utf8')
138
"""
139
140
def get_server_info(self):
141
"""
142
Get MySQL server version string.
143
144
Returns:
145
str: Server version information
146
"""
147
148
def get_host_info(self):
149
"""
150
Get connection host information string.
151
152
Returns:
153
str: Host connection details
154
"""
155
156
def get_proto_info(self):
157
"""
158
Get MySQL protocol version.
159
160
Returns:
161
int: Protocol version number
162
"""
163
164
def thread_id(self):
165
"""
166
Get connection thread ID.
167
168
This value can be used with kill() to terminate the connection thread.
169
170
Returns:
171
int: MySQL connection thread ID
172
"""
173
174
def stat(self):
175
"""
176
Get MySQL server statistics.
177
178
Returns:
179
str: Server status information
180
"""
181
182
def kill(self, pid):
183
"""
184
Kill a MySQL server thread.
185
186
Parameters:
187
- pid (int): Thread ID to kill
188
"""
189
190
def select_db(self, db):
191
"""
192
Change the default database for the connection.
193
194
Parameters:
195
- db (str): Database name to select
196
"""
197
198
def escape(self, obj, mapping):
199
"""
200
Escape object using connection's character set.
201
202
Parameters:
203
- obj: Object to escape
204
- mapping: Type conversion mapping
205
206
Returns:
207
str: Escaped string representation
208
"""
209
210
def escape_string(self, s):
211
"""
212
Escape string using connection's character set.
213
214
Preferred over module-level escape_string() as it handles
215
character sets properly.
216
217
Parameters:
218
- s (str): String to escape
219
220
Returns:
221
str: Escaped string
222
"""
223
224
def __enter__(self):
225
"""
226
Context manager entry.
227
228
Returns:
229
Connection: Self for context management
230
"""
231
232
def __exit__(self, exc, value, tb):
233
"""
234
Context manager exit with automatic cleanup.
235
236
Parameters:
237
- exc: Exception type
238
- value: Exception value
239
- tb: Traceback object
240
"""
241
```
242
243
### Connection Attributes
244
245
Connection objects expose various attributes from the underlying MySQL connection.
246
247
```python { .api }
248
# Connection attributes (read-only properties)
249
connection.open: int # Connection status
250
connection.server_capabilities: int # Server capability flags
251
connection.client_flag: int # Client connection flags
252
connection.client_info: str # Client library information
253
connection.host_info: str # Host connection information
254
connection.server_info: str # Server version information
255
connection.proto_info: int # Protocol version
256
connection.thread_id: int # Connection thread ID
257
```
258
259
### Utility Functions
260
261
Helper functions for connection management and error handling.
262
263
```python { .api }
264
def defaulterrorhandler(connection, cursor, errorclass, errorvalue):
265
"""
266
Default error handler for database operations.
267
268
Parameters:
269
- connection: Database connection object
270
- cursor: Cursor object where error occurred
271
- errorclass: Exception class to raise
272
- errorvalue: Exception instance or error message
273
"""
274
275
def numeric_part(s):
276
"""
277
Extract numeric part from version string.
278
279
Parameters:
280
- s (str): Version string
281
282
Returns:
283
str: Numeric portion of version string
284
"""
285
```
286
287
## Usage Examples
288
289
### Basic Connection Management
290
291
```python
292
import MySQLdb
293
294
# Connect with basic parameters
295
db = MySQLdb.connect(
296
host="localhost",
297
user="myuser",
298
passwd="mypassword",
299
db="mydatabase"
300
)
301
302
# Use connection
303
cursor = db.cursor()
304
cursor.execute("SELECT VERSION()")
305
version = cursor.fetchone()
306
print(f"MySQL version: {version[0]}")
307
308
# Clean up
309
cursor.close()
310
db.close()
311
```
312
313
### Connection with Advanced Options
314
315
```python
316
import MySQLdb
317
318
# Connect with advanced configuration
319
db = MySQLdb.connect(
320
host="localhost",
321
user="myuser",
322
passwd="mypassword",
323
db="mydatabase",
324
charset="utf8",
325
use_unicode=True,
326
autocommit=False,
327
connect_timeout=10,
328
cursorclass=MySQLdb.cursors.DictCursor
329
)
330
331
# Connection is ready with UTF-8 support and dictionary cursors
332
```
333
334
### Transaction Management
335
336
```python
337
import MySQLdb
338
339
with MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test") as db:
340
try:
341
# Begin transaction
342
db.begin()
343
344
cursor = db.cursor()
345
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
346
("John Doe", "john@example.com"))
347
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = %s", (1,))
348
349
# Commit if all operations succeed
350
db.commit()
351
print("Transaction completed successfully")
352
353
except MySQLdb.Error as e:
354
# Rollback on any error
355
db.rollback()
356
print(f"Transaction failed: {e}")
357
raise
358
```
359
360
### Context Manager Usage
361
362
```python
363
import MySQLdb
364
365
# Automatic connection cleanup
366
with MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test") as db:
367
with db.cursor() as cursor:
368
cursor.execute("SELECT * FROM users LIMIT 5")
369
for row in cursor.fetchall():
370
print(row)
371
# Cursor automatically closed
372
# Connection automatically closed
373
```