Python And The MySQL X DevAPI - PyCaribbean 2019

This presentation covers how to use the MySQL X DevAPI with the Python Programming Language, presented at the first PyCaribbean Conference, Santo Domino February 16th 2019

1. Python and the MySQL X DevAPI Dave Stokes @Stoker [email protected] https://elephantdolphin.blogspot.com / https://slideshare.net/davidmstokes

2. Safe Harbor Agreement THE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT IS INTENDED FOR INFORMATION PURPOSES ONLY, AND MAY NOT BE INCORPORATED INTO ANY CONTRACT. IT IS NOT A COMMITMENT TO DELIVER ANY MATERIAL, CODE, OR FUNCTIONALITY, AND SHOULD NOT BE RELIED UPON IN MAKING PURCHASING DECISIONS. THE DEVELOPMENT, RELEASE, AND TIMING OF ANY FEATURES OR FUNCTIONALITY DESCRIBED FOR ORACLE'S PRODUCTS REMAINS AT THE SOLE DISCRETION OF ORACLE. 2

3. Small History Lesson Here 3

4. Programmers Tasks 20 years ago Editor - vi, vim, emacs Compiler - 1 or two languages Debugger Browser (maybe) XML (maybe) HTML (growing need) Source Control System IDE (you wish!) Documentation (low priority) 4

5. Programmers Tasks 20 years ago Editor - vi, vim, emacs Compiler - 1 or two languages Debugger Browser (maybe) XML (maybe) HTML (growing need) Source Control System IDE (you wish!) Documentation (low priority) Today GIT Tests CI Small(er) stuff Containers, VMs, micro services, etc. Main language & Framework 7 or 8 JavaScript Frameworks Data Store (SQL & NoSQL) 5

6. Programmers Tasks 20 years ago Editor - vi, vim, emacs Compiler - 1 or two languages Debugger Browser (maybe) XML (maybe) HTML (growing need) Source Control System IDE (you wish!) Documentation (low priority) Today GIT Tests CI Small(er) stuff Containers, VMs, micro services, etc. Main language & Framework 7 or 8 JavaScript Frameworks Data Store (SQL & NoSQL) JSON SSH/TLS Encryption Bash & Powershell Markdown Cloud 6

7. Programmers Tasks 20 years ago Editor - vi, vim, emacs Compiler - 1 or two languages Debugger Browser (maybe) XML (maybe) HTML (growing need) Source Control System IDE (you wish!) Documentation (low priority) Today GIT Tests CI Small(er) stuff Containers, VMs, micro services, etc. Main language & Framework 7 or 8 JavaScript Frameworks Data Store (SQL & NoSQL) JSON SSH/TLS Encryption Bash & Powershell Markdown Cloud Debugger Multiple Browsers Multiple IDEs Documentation (still low priority) Mentoring (giving or receiving) More frameworks More third party libraries Embedded, Android, IOS, etc. Key management Slack Repository tools * as a Service (*aaS) Whatever the latest craze is from The Register, Inforworld, Slashdot 7

8. Programmers Tasks 20 years ago Editor - vi, vim, emacs Compiler - 1 or two languages Debugger Browser (maybe) XML (maybe) HTML (growing need) Source Control System IDE (you wish!) Documentation (low priority) Today GIT Tests CI Small(er) stuff Containers, VMs, micro services, etc. Main language & Framework 7 or 8 JavaScript Frameworks Data Store (SQL & NoSQL) JSON SSH/TLS Encryption Bash & Powershell Markdown Cloud Debugger Multiple Browsers Multiple IDEs Documentation (still low priority) Mentoring (giving or receiving) More frameworks More third party libraries Embedded, Android, IOS, etc. Key management Slack Repository tools * as a Service (*aaS) Whatever the latest craze is from The Register, Inforworld, Slashdot YAML and other markup file syntax The newest Google tool etc 8

9. 9 BTW This was the big concern of 1999!

10. Constantly Increasing Learning Curve 10

11. Impedance Mismatch 11

12. Declarative Language Buried in a OO/Procedural A big problem for many developers is that they are used to Object Oriented and/or Procedural programming languages. SQL is declarative programming language and embedding SQL in Pythin is an object-relational impedance mismatch. sql_parameterized_query = """ Update computers set ram = %s where id = %s """ ram = 20 id = 2 input = (ram, id ) cursor.execute ( sql_parameterized_query , input) connection.commit () https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/ 12

13. Other Issues UGLY Hard to have help from your IDE Extra level of complexity / opportunity to fail Badly organized data & queries

14. And Yet More Issues Relational tables need to be set up Indexes Data mutability Need to rely on a DBA (or someone who has that role) Can’t start coding much of project before data format is know ORMs -- more complexity and another layer to maintain 14

15. X DevAPI & The MySQL Document Store 15

16. So what if there was a way to ... Use schemaless JSON documents so you do not have to normalize data and code before you know the complete schema Not have to embed SQL strings in your code Use a modern programming style API Be able to use the JSON data from SQL or NoSQL - Best of both worlds 16

17. 17

18. The X DevAPI wraps powerful concepts in a simple API. A new high-level session concept enables you to write code that can transparently scale from single MySQL Server to a multiple server environment. →Read operations are simple and easy to understand . →Non-blocking, asynchronous calls follow common host language patterns . →The X DevAPI introduces a new, modern and easy-to-learn way to work with your data. Documents are stored in Collections and have their dedicated CRUD operation set. →Work with your existing domain objects or generate code based on structure definitions for strictly typed languages . →Focus is put on working with data via CRUD operations . →Modern practices and syntax styles are used to get away from traditional SQL-String-Building. 18

19. Scale from Single Server to Cluster w/o Code Change The code that is needed to connect to a MySQL document store looks a lot like the traditional MySQL connection code, but now applications can establish logical sessions to MySQL server instances running the X Plugin. Sessions are produced by the mysqlx factory, and the returned Sessions can encapsulate access to one or more MySQL server instances running X Plugin. Applications that use Session objects by default can be deployed on both single server setups and database clusters with no code changes. 19

20. var mysqlx = require ( 'mysqlx' ); // Connect to server on localhost var mySession = mysqlx . getSession ( { host : 'localhost' , port : 33060 , user : 'user' , password : ' password ' } ); var myDb = mySession . getSchema ( 'test' ); // Use the collection 'my_collection' var myColl = myDb . getCollection ( 'my_collection' ); // Specify which document to find with Collection.find() and // fetch it from the database with .execute() var myDocs = myColl . find ( 'name like :param' ). limit ( 1 ). bind ( 'param' , 'S%' ). execute (); // Print document print ( myDocs . fetchOne ()); mySession . close (); 20

21. MySQL Connector/Python Developer Guide https://dev.mysql.com/doc/connector-python/en / Requirements MySQL 5.7.12 or higher, with the X Plugin enabled Python 2.7 or >= 3.4 Protobuf C++ (version >= 2.6.0) Python Protobuf (version >= 3.0.0) 21

22. MySQL Connector/Python includes support for: Almost all features provided by MySQL Server up to and including MySQL Server version 5.7. Connector/Python 8.0 also supports X DevAPI . For documentation of the concepts and the usage of MySQL Connector/Python with X DevAPI , see X DevAPI User Guide. Converting parameter values back and forth between Python and MySQL data types, for example Python datetime and MySQL DATETIME. You can turn automatic conversion on for convenience, or off for optimal performance. All MySQL extensions to standard SQL syntax. Protocol compression, which enables compressing the data stream between the client and server. Connections using TCP/IP sockets and on Unix using Unix sockets. Secure TCP/IP connections using SSL. Self-contained driver. Connector/Python does not require the MySQL client library or any Python modules outside the standard library. 22

23. Connector/Python Version Reference Please use the latest version you can!! 23 Connector/Python Version MySQL Server Versions Python Versions Connector Status 8.0 8.0, 5.7, 5.6, 5.5 3.7, 3.6, 3.5, 3.4, 2.7 General Availability 2.2 (continues as 8.0) 5.7, 5.6, 5.5 3.5, 3.4, 2.7 Developer Milestone, No releases 2.1 5.7, 5.6, 5.5 3.5, 3.4, 2.7, 2.6 General Availability 2.0 5.7, 5.6, 5.5 3.5, 3.4, 2.7, 2.6 GA, final release on 2016-10-26 1.2 5.7, 5.6, 5.5 (5.1, 5.0, 4.1) 3.4, 3.3, 3.2, 3.1, 2.7, 2.6 GA, final release on 2014-08-22

24. Download Connector Python https://dev.mysql.com/downloads/connector/python / 24

25. Binary Installation Connector/Python installers in native package formats are available for Windows and for Unix and Unix-like systems: Windows: MSI installer package Linux: Yum repository for EL6 and EL7 and Fedora; RPM packages for Oracle Linux, Red Hat, and SuSE ; Debian packages for Debian and Ubuntu M acOS : Disk image package with PKG installer 25

26. A JSON document is a data structure composed of key-value pairs and is the fundamental structure for using MySQL as document store. This document shows that the values of keys can be simple data types, such as integers or strings, but can also contain other documents, arrays, and lists of documents. For example, the geography key's value consists of multiple key-value pairs. A JSON document is represented internally using the MySQL binary JSON object, through the JSON MySQL datatype. The most important differences between a document and the tables known from traditional relational databases are that the structure of a document does not have to be defined in advance, and a collection can contain multiple documents with different structures. Relational tables on the other hand require that their structure be defined, and all rows in the table must contain the same columns. { "GNP" : . 6 , "IndepYear" : 1967 , "Name" : "Sealand" , "_id" : "SEA" , "demographics" : { "LifeExpectancy" : 79 , "Population" : 27 }, "geography" : { "Continent" : "Europe" , "Region" : "British Islands" , "SurfaceArea" : 193 }, "government" : { "GovernmentForm" : "Monarchy" , "HeadOfState" : "Michael Bates" } } 26

27. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 27 An Example

28. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 28 The URI specifies the details of the connection Protocol Username Authentication String Lost Port The X Plugin listens at port 33060

29. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 29 Connect to a schema

30. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 30 Specify Document Collection

31. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 31 Equivilent to: SELECT name FROM my_collection WHERE name LIKE ‘S%’ LIMIT 1

32. import mysqlx # Connect to server on localhost session = mysqlx.get_session ({ 'host': 'localhost', 'port': 33060, 'user': ' myuser ', 'password': ' mypass ' }) schema = session.get_schema ('test') # Use the collection ' my_collection ' collection = schema.get_collection (' my_collection ') # Specify which document to find with Collection.find () result = collection.find ('name like : param ').bind(' param ', 'S%').limit(1).execute() # Print document docs = result.fetch_all () print('Name: {0}'.format(docs[0]['name'])) session.close () 32 Print data

33. The Emphasis is on CRUD 33 Operation form Description db. name .add() The add() method inserts one document or more documents into the named collection. db. name .find() The find() method returns some or all documents in the named collection. db. name .modify() The modify() method updates documents in the named collection. db. name .remove() The remove() method deletes one document or a list of documents from the named collection. CRUD EBNF Definitions - https://dev.mysql.com/doc/x-devapi-userguide/en/mysql-x-crud-ebnf-definitions.html

34. find() 34

35. No more messy strings SQLQuery = “SELECT * FROM people WHERE job LIKE “ . $job . “ AND age > $age ” Versus collection = $ schema . getCollection ( "people " ) result = collection . find ( 'job like :job and age > :age' ) . bind ( job, 'Butler' , age, 16 ) . execute (); 35

36. Easier to read/comprehend than SQL $result = collection .r emove ( 'age > :age_from and age < :age_to' ) . bind ( age_from , 20 , age_to , 50 ]) . limit ( 2 ) . execute (); 36 Easy to add filters like SORT, LIMIT, HAVING GROUP BY

37. Indexes on collections 37 collection . create_index ( " index_on_names " , { "fields" : [{ "field" : " family_name " , "type" : "TEXT(12)" , "required" : True }], "fields" : [{ "field" : "name" , "type" : "TEXT(12)" , "required" : True }], " type" : "INDEX " }) This creates an index using the family_name and name values

38. Transactions 38 # Start transaction session . start_transaction () collection . add ({ "name" : "Wilma" , "age" : 33 }) . execute () # Create a savepoint session . set_savepoint ( " sp " ) collection . add ({ "name" : "Barney" , "age" : 42 }) . execute () # Rollback to a savepoint session . rollback_to ( " sp " ) # Commit all operations session . commit ()

39. Got Tables? You can also use the MySQL Document Store with Relational Tables 39

40. Quick Example using a table import mysqlx from config import connect_args db = mysqlx.get_session (schema=" world_x ", ** connect_args ) schema = db.get_default_schema () mytable = schema.get_table ("city") result = mytable.select ("Name ") .where ('District = "Texas "') . sort("Name ") . execute() city = result.fetch_all () for row in city:    print(row["Name "]) 40 Compare to the SQL SELECT Name FROM city WHERE District = ‘Texas ORDER By Name;

41. Add or Remove Records 41 my_coll = db . get_collection ( ' my_collection ' ) # Add a document to ' my_collection ' my_coll . add ({ '_id' : '2' , 'name' : ' Sakila ' , 'age' : 15 }) . execute () # You can also add multiple documents at once my_coll . add ({ '_id' : '2' , 'name' : ' Sakila ' , 'age' : 15 }, { '_id' : '3' , 'name' : 'Jack' , 'age' : 15 }, { '_id' : '4' , 'name' : 'Clare' , 'age' : 37 }) . execute () # Remove the document with '_id' = '1' my_coll . remove ( '_id = 1' ) . execute ()

42. Connection Pooling 42 connection_dict = { 'host' : 'localhost' , 'port' : 33060 , 'user' : 'mike' , 'password' : 's3cr3t!' } options_dict = { 'pooling' :{ ' max_size ' : 5 , ' queue_timeout ' : 1000 }} client = mysqlx . get_client ( connection_dict , options_dict ) for _ in range( 5 ): client . get_session () Pooling options: 'enabled': (bool)), # [True | False], True by default ' max_size ': ( int ), # Maximum connections per pool " max_idle_time ": ( int )), # milliseconds that a connection will remain active # while not in use. By default 0, means infinite. " queue_timeout ": ( int ), # milliseconds a request will wait for a connection # to become available. By default 0, means infinite. Connection pooling is a technique of creating and managing a pool of connections that are ready for use, which greatly increase the performance of your applications by reducing the connection creation time.

43. What if I Don’t Want to Rewrite old queries? session . sql ( "CREATE DATABASE addressbook " ). execute (); 43

44. One of the advantages of using MySQL as a NoSQL Document store is that you can use SQL analytics on your data! 44

45. Combine CTEs, Windowing Functions, & JSON_TABLE WITH cte1 AS (SELECT doc->>\"$.name\" AS 'name', doc->>\"$.cuisine\" AS 'cuisine', (SELECT AVG(score) FROM JSON_TABLE(doc, \"$.grades[*]\" COLUMNS (score INT PATH \"$.score\")) as r ) AS avg_score FROM restaurants) SELECT *, rank() OVER (PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER by `rank`, avg_score DESC limit 10 JSON_TABLE turns unstructured JSON documents in to temporary relational tables that can be processed with SQL Windowing Function for analytics Common Table Expression make it easy to write sub-queries 45

46. 46

47. The X Plugin … is a shared object that is installed by default in MySQL 8.0 and must be loaded in 5.7 mysqlsh -u user -h localhost --classic --dba enableXProtocol Or mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so' ; It listens on port 33060 so make sure you open firewall for 3306 (old MySQL) and 33060 (X Plugin). And it supports SSL/TLS!!! 47

48. The New MySQL Shell 48 Built In JavaScript and Python interpreters let you work with you data in the MySQL Shell. Plus you get command completion, great help facilities, the ability to check for server upgrades, and the ability to administrate a InnoDB Clusters. And you can also use SQL

49. Built in JSON Bulk Loader 49

50. 50 InnoDB Cluster MySQL InnoDB cluster provides a complete high availability solution for MySQL. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover. AdminAPI removes the need to work directly with Group Replication in InnoDB clusters MySQL Router can automatically configure itself based on the cluster you deploy, connecting client applications transparently to the server instances. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary. MySQL Router detects this and forwards client applications to the new primary. Advanced users can also configure a cluster to have multiple-primaries.

51. 51

52. Questions and Answers plus Additional Resources More Info on MySQL Document Store Python Connector for X DevAPI https://dev.mysql.com/doc/dev/connector-python/8.0 / MySQL Document Store https://dev.mysql.com/doc/refman/8.0/en/document-store.html X DevAPI User Guide https://dev.mysql.com/doc/x-devapi-userguide/en/ Dev.MySQL.com for Downloads and Other Docs X DevAPI Tutorial for Sunshine PHP Python in Works https://github.com/davidmstokes/PHP-X-DevAPI [email protected] https://elephantdolphin.blogspot.com/ Slides at https://slideshare.net/davidmstokes @Stoker MySQL & JSON - A Practical Programming Guide 52