MySQL Connector/Python Revealed SQL and NoSQL Data Storage Using MySQL for Python Programmers — Jesper Wisborg Krogh MySQL Connector/Python Revealed SQL and NoSQL Data Storage Using MySQL for Python Programmers Jesper Wisborg Krogh MySQL Connector/Python Revealed Jesper Wisborg Krogh Hornsby, New South Wales, Australia ISBN-13 (pbk): 978-1-4842-3693-2 ISBN-13 (electronic): 978-1-4842-3694-9 https://doi.org/10.1007/978-1-4842-3694-9 Library of Congress Control Number: 2018952522 Copyright © 2018 by Jesper Wisborg Krogh This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover designed by eStudioCalamar Cover image designed by Freepik (www.freepik.com) Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit www.apress.com/ rights-permissions. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the book's product page, located at www.apress. com/9781484236932. For more detailed information, please visit www.apress.com/source-code. Printed on acid-free paper To my wife, Ann-Margrete, and my parents. Table of Contents About the Author �������������������������������������������������������������������������������xiii About the Technical Reviewer ������������������������������������������������������������xv Acknowledgments ����������������������������������������������������������������������������xvii Introduction ���������������������������������������������������������������������������������������xix Part I: Getting Ready ��������������������������������������������������������������������1 Chapter 1: Introduction and Installation �����������������������������������������������3 Introduction �����������������������������������������������������������������������������������������������������������3 Versions �����������������������������������������������������������������������������������������������������������4 Community and Enterprise Editions ����������������������������������������������������������������5 APIs������������������������������������������������������������������������������������������������������������������6 Downloading ���������������������������������������������������������������������������������������������������������8 Installation ����������������������������������������������������������������������������������������������������������15 pip – All Platforms �����������������������������������������������������������������������������������������16 Microsoft Windows – MySQL Installer �����������������������������������������������������������20 Linux – MySQL Yum Repository ���������������������������������������������������������������������25 Verifying the Installation ��������������������������������������������������������������������������������27 MySQL Server �����������������������������������������������������������������������������������������������������29 Installation �����������������������������������������������������������������������������������������������������29 Configuration �������������������������������������������������������������������������������������������������33 v TTaabbllee ooff CCoonnTTeennTTss Creating the Application User������������������������������������������������������������������������������35 Installing the world Sample Database ����������������������������������������������������������������37 Code Examples ���������������������������������������������������������������������������������������������������41 Summary�������������������������������������������������������������������������������������������������������������43 Part II: The Legacy APIs �������������������������������������������������������������45 Chapter 2: Connecting to MySQL ��������������������������������������������������������47 Creating the Connection from Python �����������������������������������������������������������������47 Syntax ������������������������������������������������������������������������������������������������������������48 Common Connection Options ������������������������������������������������������������������������50 Connection Examples ������������������������������������������������������������������������������������52 Reconfiguration and Reconnect ��������������������������������������������������������������������55 Connection Best Practices �����������������������������������������������������������������������������57 Configuration Files ����������������������������������������������������������������������������������������������58 Alternatives to Hardcoding the Configuration ������������������������������������������������58 Using MySQL Configuration Files ������������������������������������������������������������������60 General Configuration �����������������������������������������������������������������������������������������64 Connection �����������������������������������������������������������������������������������������������������64 Character Set �������������������������������������������������������������������������������������������������71 Query Behavior ����������������������������������������������������������������������������������������������80 Warnings �������������������������������������������������������������������������������������������������������81 Summary�������������������������������������������������������������������������������������������������������������82 Chapter 3: Basic Query Execution ������������������������������������������������������83 Simple Execution ������������������������������������������������������������������������������������������������83 Executing the Query: cmd_query() ����������������������������������������������������������������85 Retrieving Rows – get_rows() �����������������������������������������������������������������������89 Automatic Conversion into Native Python Types �������������������������������������������95 vi TTaabbllee ooff CCoonnTTeennTTss Retrieving Rows – get_rows() With Limit ������������������������������������������������������98 Retrieving Rows – get_row() �����������������������������������������������������������������������101 Consuming Results ��������������������������������������������������������������������������������������104 Cursors ��������������������������������������������������������������������������������������������������������������105 Instantiation �������������������������������������������������������������������������������������������������106 MySQLCursor – Execution Flow �������������������������������������������������������������������109 MySQLCursor – Query Execution �����������������������������������������������������������������111 MySQLCursor – Properties ��������������������������������������������������������������������������115 The Dictionary and Named Tuple Cursor Subclasses ����������������������������������118 Handling User Input ������������������������������������������������������������������������������������������121 Validating the Input��������������������������������������������������������������������������������������122 Query Parameterization �������������������������������������������������������������������������������122 Prepared Statements �����������������������������������������������������������������������������������127 Summary�����������������������������������������������������������������������������������������������������������131 Chapter 4: Advanced Query Execution����������������������������������������������133 Multi-Query Execution ��������������������������������������������������������������������������������������134 Multiple Queries with Support for Results ���������������������������������������������������135 Multiple Queries Based on a Template ��������������������������������������������������������143 Extended Inserts ������������������������������������������������������������������������������������������148 Buffered Results �����������������������������������������������������������������������������������������������151 Stored Procedures ��������������������������������������������������������������������������������������������156 Loading Data Using a CSV File ��������������������������������������������������������������������������162 Loading a Server-Side File ��������������������������������������������������������������������������163 Loading an Application-Side File �����������������������������������������������������������������164 Load Data Example ��������������������������������������������������������������������������������������165 Connection Properties ���������������������������������������������������������������������������������������170 Transactions ������������������������������������������������������������������������������������������������������175 vii TTaabbllee ooff CCoonnTTeennTTss Default Database �����������������������������������������������������������������������������������������������188 Time Zones ��������������������������������������������������������������������������������������������������������190 Other Connection Utility Methods ���������������������������������������������������������������������196 Connection Methods ������������������������������������������������������������������������������������198 Server Information Methods ������������������������������������������������������������������������204 Column Information ������������������������������������������������������������������������������������������206 Field Types ���������������������������������������������������������������������������������������������������207 MySQL Column Flags �����������������������������������������������������������������������������������209 The C Extension ������������������������������������������������������������������������������������������������214 The mysql�connector�connect() Function �����������������������������������������������������215 The _mysql_connector Module �������������������������������������������������������������������218 Summary�����������������������������������������������������������������������������������������������������������221 Chapter 5: Connection Pooling and Failover �������������������������������������223 Connection Pooling – Background ��������������������������������������������������������������������223 The pooling�MySQLConnectionPool Class ���������������������������������������������������224 The pooling�PooledMySQLConnection Class ������������������������������������������������226 Configuration Options ����������������������������������������������������������������������������������227 Using Connection Pools ������������������������������������������������������������������������������������229 Creating a Connection Pool �������������������������������������������������������������������������229 Using Connection Pool Connections ������������������������������������������������������������231 Executing Queries ����������������������������������������������������������������������������������������239 Reconfiguring the Connections �������������������������������������������������������������������241 Connection Failover ������������������������������������������������������������������������������������������244 Failover Configuration ���������������������������������������������������������������������������������245 Coding for Failover ��������������������������������������������������������������������������������������248 Failover Example �����������������������������������������������������������������������������������������250 Summary�����������������������������������������������������������������������������������������������������������256 viii TTaabbllee ooff CCoonnTTeennTTss Part III: The X DevAPI ���������������������������������������������������������������257 Chapter 6: The X DevAPI �������������������������������������������������������������������259 The MySQL X Plugin ������������������������������������������������������������������������������������������261 The mysqlx Module �������������������������������������������������������������������������������������������263 Creating a Session ��������������������������������������������������������������������������������������������267 Passing Individual Options ��������������������������������������������������������������������������268 Passing an URI ���������������������������������������������������������������������������������������������272 Connection Examples ����������������������������������������������������������������������������������274 Working with the Session ���������������������������������������������������������������������������������275 Transactions ������������������������������������������������������������������������������������������������275 Other Session Methods �������������������������������������������������������������������������������276 Schemas �����������������������������������������������������������������������������������������������������������277 Schema Manipulation ����������������������������������������������������������������������������������278 Other Schema Methods and Properties �������������������������������������������������������284 Schema Example �����������������������������������������������������������������������������������������286 CRUD Arguments �����������������������������������������������������������������������������������������289 Documents ��������������������������������������������������������������������������������������������������289 Document ID ������������������������������������������������������������������������������������������������291 Condition �����������������������������������������������������������������������������������������������������292 Fields �����������������������������������������������������������������������������������������������������������293 Statements ��������������������������������������������������������������������������������������������������������294 Results ��������������������������������������������������������������������������������������������������������������298 result�Result ������������������������������������������������������������������������������������������������299 result�DocResult and result�RowResult �������������������������������������������������������300 result�SqlResult �������������������������������������������������������������������������������������������302 Summary�����������������������������������������������������������������������������������������������������������304 ix TTaabbllee ooff CCoonnTTeennTTss Chapter 7: The MySQL Document Store ��������������������������������������������305 The MySQL Document Store �����������������������������������������������������������������������������305 Workflow �����������������������������������������������������������������������������������������������������������307 Collections ��������������������������������������������������������������������������������������������������������309 Collection Manipulation �������������������������������������������������������������������������������310 Other Collection Methods and Properties ����������������������������������������������������330 Queries – CRUD �������������������������������������������������������������������������������������������������331 CRUD: Create �����������������������������������������������������������������������������������������������������333 CRUD: Read �������������������������������������������������������������������������������������������������������339 CRUD: Update ����������������������������������������������������������������������������������������������������346 Replacing Documents ����������������������������������������������������������������������������������347 Modifying Documents ����������������������������������������������������������������������������������350 CRUD: Delete �����������������������������������������������������������������������������������������������������366 Summary�����������������������������������������������������������������������������������������������������������370 Chapter 8: SQL Tables �����������������������������������������������������������������������371 Workflow �����������������������������������������������������������������������������������������������������������371 NoSQL API for SQL Tables����������������������������������������������������������������������������������373 Table and View Objects ��������������������������������������������������������������������������������374 Table Queries �����������������������������������������������������������������������������������������������377 CRUD: Create �����������������������������������������������������������������������������������������������378 CRUD: Read ��������������������������������������������������������������������������������������������������382 CRUD: Update ����������������������������������������������������������������������������������������������387 CRUD: Delete �����������������������������������������������������������������������������������������������390 SQL Statements ������������������������������������������������������������������������������������������������393 Executing SQL Statements ��������������������������������������������������������������������������394 Queries with Multiple Result Sets ���������������������������������������������������������������397 Summary�����������������������������������������������������������������������������������������������������������400 x
Description: