MySQL Connector/Python Revealed (eBook)

Jesper Wisborg Krogh is a member of the Oracle MySQL Support team and has spoken on several occasions at Oracle OpenWorld. He has a Ph.D. in computational chemistry before changing to work with MySQL and other software development in 2006. Jesper lives in Sydney, Australia, and enjoys spending time outdoors walking, traveling, and reading. His areas of expertise include MySQL Cluster, MySQL Enterprise Backup, and the Performance and sys schemas. He is an active author in the Oracle Knowledge Base, and regularly blogs on MySQL topics.

Table of Contents 5
About the Author 13
About the Technical Reviewer 14
Acknowledgments 15
Introduction 16
Part I: Getting Ready 21
Chapter 1: Introduction and Installation 22
Introduction 22
Versions 23
Community and Enterprise Editions 24
APIs 25
Downloading 27
Installation 34
pip – All Platforms 35
Microsoft Windows – MySQL Installer 39
Linux – MySQL Yum Repository 44
Verifying the Installation 46
MySQL Server 48
Installation 48
Configuration 52
Creating the Application User 54
Installing the world Sample Database 56
Code Examples 60
Summary 62
Part II: The Legacy APIs 63
Chapter 2: Connecting to MySQL 64
Creating the Connection from Python 64
Syntax 65
Common Connection Options 67
Connection Examples 69
Reconfiguration and Reconnect 72
Connection Best Practices 74
Configuration Files 75
Alternatives to Hardcoding the Configuration 75
Using MySQL Configuration Files 77
General Configuration 81
Connection 81
Character Set 88
Query Behavior 97
Warnings 98
Summary 99
Chapter 3: Basic Query Execution 100
Simple Execution 100
Executing the Query: cmd_query() 102
Retrieving Rows – get_rows() 106
Automatic Conversion into Native Python Types 112
Retrieving Rows – get_rows() With Limit 115
Retrieving Rows – get_row() 118
Consuming Results 121
Cursors 122
Instantiation 123
MySQLCursor – Execution Flow 126
MySQLCursor – Query Execution 128
MySQLCursor – Properties 132
column_names 133
description 134
lastrowid 134
rowcount 134
statement 135
with_rows 135
The Dictionary and Named Tuple Cursor Subclasses 135
Handling User Input 138
Validating the Input 139
Query Parameterization 139
Prepared Statements 144
Summary 148
Chapter 4: Advanced Query Execution 150
Multi-Query Execution 151
Multiple Queries with Support for Results 152
Connection - cmd_query_iter() 153
Cursor – execute() 157
Multiple Queries Based on a Template 160
Extended Inserts 165
Buffered Results 168
Stored Procedures 173
Loading Data Using a CSV File 179
Loading a Server-Side File 180
Loading an Application-Side File 181
Load Data Example 182
Connection Properties 187
Transactions 192
Default Database 205
Time Zones 207
Other Connection Utility Methods 213
Connection Methods 215
cmd_change_user() 215
cmd_reset_connection() and reset_session() 217
is_connected() 218
ping() 219
Server Information Methods 221
Column Information 223
Field Types 224
MySQL Column Flags 226
The C Extension 231
The mysql.connector.connect() Function 232
The _mysql_connector Module 235
Summary 238
Chapter 5: Connection Pooling and Failover 239
Connection Pooling – Background 239
The pooling.MySQLConnectionPool Class 240
The pooling.PooledMySQLConnection Class 242
Configuration Options 243
Using Connection Pools 245
Creating a Connection Pool 245
Using Connection Pool Connections 247
Using the mysql.connector.connect() Function 247
Using the get_connection() Method 251
Executing Queries 255
Reconfiguring the Connections 257
Connection Failover 260
Failover Configuration 261
Coding for Failover 264
Failover Example 266
Summary 272
Part III: The X DevAPI 273
Chapter 6: The X DevAPI 274
The MySQL X Plugin 276
The mysqlx Module 278
Creating a Session 282
Passing Individual Options 283
Passing an URI 287
Connection Examples 289
Working with the Session 290
Transactions 290
Other Session Methods 291
Schemas 292
Schema Manipulation 293
Creating a Schema 295
Retrieving the Default Schema 296
Retrieving a Schema by Name 298
Dropping a Schema 299
Other Schema Methods and Properties 299
Schema Example 301
CRUD Arguments 304
Documents 304
Document ID 306
Condition 307
Fields 308
Statements 309
Results 313
result.Result 314
result.DocResult and result.RowResult 315
result.SqlResult 317
Summary 319
Chapter 7: The MySQL Document Store 320
The MySQL Document Store 320
Workflow 322
Collections 324
Collection Manipulation 325
Creating a Collection 331
Retrieving a Single Collection 334
Retrieving All Collections in a Schema 335
Dropping a Collection 336
Creating Indexes 337
Dropping Indexes 344
Other Collection Methods and Properties 345
Queries – CRUD 346
CRUD: Create 348
CRUD: Read 354
CRUD: Update 361
Replacing Documents 362
Modifying Documents 365
set() and unset() 367
array_append() and array_insert() 370
patch() 375
CRUD: Delete 381
Summary 385
Chapter 8: SQL Tables 386
Workflow 386
NoSQL API for SQL Tables 388
Table and View Objects 389
Table Queries 392
CRUD: Create 393
CRUD: Read 397
CRUD: Update 402
CRUD: Delete 405
SQL Statements 408
Executing SQL Statements 409
Queries with Multiple Result Sets 412
Summary 415
Part IV: Error Handling and Troubleshooting 417
Chapter 9: Error Handling 418
Warnings, Errors, and Strict Modes in MySQL Server 419
Treating Note Level Messages as Warnings 419
Strict Modes 421
The MySQL Error Log 423
Warning and Error Handling 424
Configuration 424
Fetching Warnings After cmd_query() 428
Fetching Warnings with Cursors 434
Fetching Warnings with the X DevAPI 437
MySQL Error Numbers and SQL States 439
MySQL Error Numbers 440
SQL States 442
Exception Classes 445
Built-In Classes 446
Mapping Errors to Exception Classes 450
Custom Exceptions 451
Locking Issues 454
What to Do When Things Go Wrong 457
Severity 458
Impact 458
Frequency 459
Retriable 459
Effort 463
Summary 463
Chapter 10: Troubleshooting 465
Troubleshooting Steps 465
Checking Warnings 466
Determining the SQL Statement 467
cursor.statement 468
mysqlx SelectStatement.get_sql() 468
Using the Performance Schema 469
Retrieving Raw Data 474
Reading the MySQL Connector/Python Source Code 475
Changing the Implementation 476
MySQL Server Logs 476
The Error Log 477
The General Query Log 478
The Slow Query Log 480
The Binary Log 481
Tools for Debugging 482
MySQL Shell 482
PyCharm 490
Troubleshooting Examples 499
Unread Result Found 499
Data Too Long or Out of Range Value 502
Data Changes Are Lost 505
The Used Command Is Not Allowed with This MySQL Version 507
Bulk Changes Causes Corruption or Errors 508
Unsupported Argument When Creating the Connection 509
Aborted Connections in the MySQL Server Error Log 510
Locking Issues 511
Summary 515
Index 517