0
# Low-Level Operations
1
2
Direct access to MySQL C API functionality through the _mysql module, providing fine-grained control over database operations, performance optimization, and advanced features not available through the high-level interface.
3
4
## Capabilities
5
6
### Module Functions
7
8
Core functions for database connectivity and utility operations at the C API level.
9
10
```python { .api }
11
def connect(**kwargs):
12
"""
13
Create low-level database connection directly to MySQL C API.
14
15
Parameters:
16
- host (str): MySQL server hostname
17
- user (str): Username for authentication
18
- passwd (str): Password for authentication
19
- db (str): Database name to connect to
20
- port (int): TCP port number
21
- unix_socket (str): Unix socket path
22
- client_flag (int): Client connection flags
23
- And other connection parameters...
24
25
Returns:
26
_mysql.connection: Low-level connection object
27
"""
28
29
def debug(s):
30
"""
31
Set debug options for MySQL client library.
32
33
Parameters:
34
- s (str): Debug option string (e.g., 'd:t:o,/tmp/mysql.trace')
35
"""
36
37
def escape(s, dict):
38
"""
39
Escape string using character set information from connection.
40
41
Parameters:
42
- s (str): String to escape
43
- dict (dict): Character set mapping dictionary
44
45
Returns:
46
str: Escaped string safe for SQL queries
47
"""
48
49
def escape_dict(dict, dict2):
50
"""
51
Escape all string values in a dictionary.
52
53
Parameters:
54
- dict (dict): Dictionary with values to escape
55
- dict2 (dict): Character set mapping dictionary
56
57
Returns:
58
dict: Dictionary with escaped string values
59
"""
60
61
def escape_sequence(seq, dict):
62
"""
63
Escape all string values in a sequence.
64
65
Parameters:
66
- seq (sequence): Sequence with values to escape
67
- dict (dict): Character set mapping dictionary
68
69
Returns:
70
tuple: Tuple with escaped string values
71
"""
72
73
def escape_string(s):
74
"""
75
Escape string for SQL (deprecated - use escape instead).
76
77
Parameters:
78
- s (str): String to escape
79
80
Returns:
81
str: Escaped string
82
"""
83
84
def string_literal(obj):
85
"""
86
Convert Python object to SQL string literal.
87
88
Parameters:
89
- obj: Python object to convert
90
91
Returns:
92
str: SQL string literal representation
93
"""
94
95
def get_client_info():
96
"""
97
Get MySQL client library version information.
98
99
Returns:
100
str: Client library version string
101
"""
102
```
103
104
### Embedded Server Functions
105
106
Functions for managing embedded MySQL server instances.
107
108
```python { .api }
109
def server_init(args):
110
"""
111
Initialize embedded MySQL server.
112
113
Parameters:
114
- args (list): Server initialization arguments
115
116
Returns:
117
int: 0 on success, non-zero on error
118
"""
119
120
def server_end():
121
"""
122
Shutdown embedded MySQL server.
123
"""
124
125
def thread_safe():
126
"""
127
Check if MySQL client library is thread-safe.
128
129
Returns:
130
bool: True if thread-safe, False otherwise
131
"""
132
```
133
134
### Module Constants
135
136
Constants and version information from the _mysql module.
137
138
```python { .api }
139
NULL: object # Special NULL value representation
140
version_info: tuple # Module version information tuple (major, minor, patch)
141
```
142
143
### Low-Level Connection Object
144
145
Direct connection object with C API methods for maximum performance and control.
146
147
```python { .api }
148
class connection:
149
"""Low-level connection object from _mysql module."""
150
151
def affected_rows(self):
152
"""
153
Get number of rows affected by last statement.
154
155
Returns:
156
int: Number of affected rows
157
"""
158
159
def autocommit(self, flag=None):
160
"""
161
Set or get autocommit mode.
162
163
Parameters:
164
- flag (bool, optional): True to enable, False to disable, None to query
165
166
Returns:
167
bool: Current autocommit status (when flag is None)
168
"""
169
170
def get_autocommit(self):
171
"""
172
Get current autocommit status.
173
174
Returns:
175
bool: True if autocommit enabled, False otherwise
176
"""
177
178
def commit(self):
179
"""Commit current transaction."""
180
181
def rollback(self):
182
"""Rollback current transaction."""
183
184
def close(self):
185
"""Close database connection and free resources."""
186
187
def query(self, s):
188
"""
189
Execute SQL query directly.
190
191
Parameters:
192
- s (str): SQL query string
193
194
Returns:
195
int: 0 on success, non-zero on error
196
"""
197
198
def store_result(self):
199
"""
200
Store complete result set in memory.
201
202
Returns:
203
_mysql.result: Result object or None if no result set
204
"""
205
206
def use_result(self):
207
"""
208
Initialize result set retrieval (streaming mode).
209
210
Returns:
211
_mysql.result: Result object or None if no result set
212
"""
213
214
def next_result(self):
215
"""
216
Move to next result set (for multiple result sets).
217
218
Returns:
219
int: 0 if more results, -1 if no more results, >0 on error
220
"""
221
222
def character_set_name(self):
223
"""
224
Get current character set name.
225
226
Returns:
227
str: Character set name
228
"""
229
230
def get_host_info(self):
231
"""
232
Get host connection information.
233
234
Returns:
235
str: Host connection description
236
"""
237
238
def get_proto_info(self):
239
"""
240
Get protocol version information.
241
242
Returns:
243
int: Protocol version number
244
"""
245
246
def get_server_info(self):
247
"""
248
Get server version information.
249
250
Returns:
251
str: Server version string
252
"""
253
254
def info(self):
255
"""
256
Get information about last query.
257
258
Returns:
259
str: Query information string or None
260
"""
261
262
def insert_id(self):
263
"""
264
Get last AUTO_INCREMENT value inserted.
265
266
Returns:
267
int: Last insert ID
268
"""
269
270
def kill(self, pid):
271
"""
272
Kill MySQL server thread.
273
274
Parameters:
275
- pid (int): Thread ID to kill
276
277
Returns:
278
int: 0 on success, non-zero on error
279
"""
280
281
def ping(self):
282
"""
283
Check if connection is alive.
284
285
Returns:
286
int: 0 if connection alive, non-zero if dead
287
"""
288
289
def select_db(self, db):
290
"""
291
Select database for connection.
292
293
Parameters:
294
- db (str): Database name
295
296
Returns:
297
int: 0 on success, non-zero on error
298
"""
299
300
def shutdown(self):
301
"""
302
Shutdown MySQL server.
303
304
Returns:
305
int: 0 on success, non-zero on error
306
"""
307
308
def stat(self):
309
"""
310
Get server statistics.
311
312
Returns:
313
str: Server status string
314
"""
315
316
def thread_id(self):
317
"""
318
Get connection thread ID.
319
320
Returns:
321
int: Thread ID
322
"""
323
324
def warning_count(self):
325
"""
326
Get number of warnings from last operation.
327
328
Returns:
329
int: Warning count
330
"""
331
```
332
333
### Low-Level Result Object
334
335
Result object for direct access to query results with C API performance.
336
337
```python { .api }
338
class result:
339
"""Low-level result object from _mysql module."""
340
341
def data_seek(self, n):
342
"""
343
Seek to specific row number in result set.
344
345
Parameters:
346
- n (int): Row number to seek to (0-based)
347
"""
348
349
def row_seek(self, offset):
350
"""
351
Seek to row using row offset.
352
353
Parameters:
354
- offset: Row offset object from row_tell()
355
356
Returns:
357
offset: Previous row offset
358
"""
359
360
def row_tell(self):
361
"""
362
Get current row position.
363
364
Returns:
365
offset: Current row offset object
366
"""
367
368
def describe(self):
369
"""
370
Get result set field descriptions.
371
372
Returns:
373
tuple: Tuple of field description tuples
374
"""
375
376
def fetch_row(self, maxrows=1, how=1):
377
"""
378
Fetch rows from result set.
379
380
Parameters:
381
- maxrows (int): Maximum number of rows to fetch
382
- how (int): Row format (1=tuple, 2=dict)
383
384
Returns:
385
tuple: Tuple of rows in requested format
386
"""
387
388
def field_flags(self):
389
"""
390
Get field flags for all columns.
391
392
Returns:
393
tuple: Tuple of field flag integers
394
"""
395
396
def num_fields(self):
397
"""
398
Get number of fields in result set.
399
400
Returns:
401
int: Number of fields
402
"""
403
404
def num_rows(self):
405
"""
406
Get number of rows in result set.
407
408
Returns:
409
int: Number of rows
410
"""
411
```
412
413
## Usage Examples
414
415
### Direct Connection and Query
416
417
```python
418
import _mysql
419
420
# Create low-level connection
421
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
422
423
# Execute query directly
424
conn.query("SELECT id, name FROM users WHERE active = 1")
425
426
# Get result set
427
result = conn.store_result()
428
if result:
429
# Fetch all rows as tuples
430
rows = result.fetch_row(maxrows=0) # 0 means all rows
431
for row in rows:
432
print(f"User ID {row[0]}: {row[1]}")
433
434
conn.close()
435
```
436
437
### Performance-Critical Operations
438
439
```python
440
import _mysql
441
442
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
443
444
# Check connection performance
445
start_time = time.time()
446
result = conn.ping()
447
ping_time = time.time() - start_time
448
449
if result == 0:
450
print(f"Connection alive, ping time: {ping_time:.4f}s")
451
else:
452
print("Connection dead")
453
454
# Get detailed connection info
455
print(f"Server info: {conn.get_server_info()}")
456
print(f"Host info: {conn.get_host_info()}")
457
print(f"Protocol: {conn.get_proto_info()}")
458
print(f"Thread ID: {conn.thread_id()}")
459
460
conn.close()
461
```
462
463
### Batch Processing with Low-Level API
464
465
```python
466
import _mysql
467
468
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
469
470
try:
471
# Begin transaction
472
conn.autocommit(False)
473
474
# Process multiple queries efficiently
475
queries = [
476
"INSERT INTO log (message) VALUES ('Batch 1')",
477
"INSERT INTO log (message) VALUES ('Batch 2')",
478
"INSERT INTO log (message) VALUES ('Batch 3')"
479
]
480
481
for query in queries:
482
result = conn.query(query)
483
if result != 0:
484
raise Exception(f"Query failed: {query}")
485
486
# Commit batch
487
conn.commit()
488
print(f"Processed {len(queries)} queries successfully")
489
490
except Exception as e:
491
conn.rollback()
492
print(f"Batch failed: {e}")
493
494
finally:
495
conn.close()
496
```
497
498
### String Escaping and Safety
499
500
```python
501
import _mysql
502
503
# Escape potentially dangerous strings
504
user_input = "'; DROP TABLE users; --"
505
escaped = _mysql.escape_string(user_input)
506
print(f"Original: {user_input}")
507
print(f"Escaped: {escaped}")
508
509
# Escape dictionary values
510
data = {
511
'name': "O'Connor",
512
'comment': 'Said "Hello world!"',
513
'sql_injection': "'; DELETE FROM users; --"
514
}
515
516
# Note: escape_dict needs connection context for proper character set handling
517
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
518
escaped_data = _mysql.escape_dict(data, conn.converter)
519
print(f"Escaped data: {escaped_data}")
520
521
conn.close()
522
```
523
524
### Advanced Result Processing
525
526
```python
527
import _mysql
528
529
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
530
531
conn.query("SELECT id, name, email, created_at FROM users")
532
result = conn.store_result()
533
534
if result:
535
# Get result metadata
536
print(f"Fields: {result.num_fields()}")
537
print(f"Rows: {result.num_rows()}")
538
539
# Get field descriptions
540
descriptions = result.describe()
541
for i, desc in enumerate(descriptions):
542
field_name, field_type, max_length, flags = desc[:4]
543
print(f"Field {i}: {field_name} (type: {field_type})")
544
545
# Fetch rows with different formats
546
result.data_seek(0) # Reset to beginning
547
548
# Fetch as tuples
549
tuple_rows = result.fetch_row(maxrows=2, how=1)
550
print(f"Tuple format: {tuple_rows}")
551
552
# Fetch as dictionaries
553
dict_rows = result.fetch_row(maxrows=2, how=2)
554
print(f"Dictionary format: {dict_rows}")
555
556
conn.close()
557
```
558
559
### Embedded Server Usage
560
561
```python
562
import _mysql
563
564
# Initialize embedded server
565
server_args = [
566
"embedded_server",
567
"--datadir=/var/lib/mysql",
568
"--language=/usr/share/mysql/english"
569
]
570
571
result = _mysql.server_init(server_args)
572
if result != 0:
573
print("Failed to initialize embedded server")
574
exit(1)
575
576
try:
577
# Connect to embedded server
578
conn = _mysql.connect(db="test")
579
580
# Use embedded server normally
581
conn.query("SHOW TABLES")
582
result = conn.store_result()
583
584
if result:
585
tables = result.fetch_row(maxrows=0)
586
for table in tables:
587
print(f"Table: {table[0]}")
588
589
conn.close()
590
591
finally:
592
# Always shutdown embedded server
593
_mysql.server_end()
594
```
595
596
## Additional Result Object Methods
597
598
Advanced methods for result set navigation and manipulation.
599
600
```python { .api }
601
class _mysql.result:
602
def data_seek(self, n):
603
"""
604
Seek to specific row number in result set.
605
606
Parameters:
607
- n (int): Row number to seek to (0-based)
608
"""
609
610
def row_seek(self, offset):
611
"""
612
Seek by offset from current position in result set.
613
614
Parameters:
615
- offset (int): Number of rows to move (can be negative)
616
"""
617
618
def row_tell(self):
619
"""
620
Get current row position in result set.
621
622
Returns:
623
int: Current row number (0-based)
624
"""
625
```
626
627
#### Result Navigation Example
628
629
```python
630
import _mysql
631
632
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
633
conn.query("SELECT id, name FROM users ORDER BY id")
634
result = conn.store_result()
635
636
if result:
637
# Get current position
638
pos = result.row_tell()
639
print(f"Current position: {pos}")
640
641
# Seek to specific row
642
result.data_seek(5)
643
row = result.fetch_row()
644
if row:
645
print(f"Row 5: {row[0]}")
646
647
# Seek relative to current position
648
result.row_seek(-2) # Go back 2 rows
649
row = result.fetch_row()
650
if row:
651
print(f"Row 3: {row[0]}")
652
653
# Get new position
654
pos = result.row_tell()
655
print(f"New position: {pos}")
656
657
conn.close()
658
```
659
660
### Thread Safety Check
661
662
```python
663
import _mysql
664
import threading
665
666
def worker():
667
"""Worker function for threading test."""
668
try:
669
conn = _mysql.connect(host="localhost", user="user", passwd="pass", db="test")
670
conn.query("SELECT CONNECTION_ID()")
671
result = conn.store_result()
672
if result:
673
connection_id = result.fetch_row()[0][0]
674
print(f"Thread {threading.current_thread().name}: Connection ID {connection_id}")
675
conn.close()
676
except Exception as e:
677
print(f"Thread {threading.current_thread().name}: Error {e}")
678
679
# Check if library is thread-safe
680
if _mysql.thread_safe():
681
print("MySQL library is thread-safe")
682
683
# Create multiple threads
684
threads = []
685
for i in range(3):
686
t = threading.Thread(target=worker, name=f"Worker-{i}")
687
threads.append(t)
688
t.start()
689
690
# Wait for all threads
691
for t in threads:
692
t.join()
693
else:
694
print("MySQL library is NOT thread-safe")
695
```