Python And The MySQL X DevAPI - PyCaribbean 2019

davestokes 720 views 52 slides Feb 16, 2019
Slide 1
Slide 1 of 52
Slide 1
1
Slide 2
2
Slide 3
3
Slide 4
4
Slide 5
5
Slide 6
6
Slide 7
7
Slide 8
8
Slide 9
9
Slide 10
10
Slide 11
11
Slide 12
12
Slide 13
13
Slide 14
14
Slide 15
15
Slide 16
16
Slide 17
17
Slide 18
18
Slide 19
19
Slide 20
20
Slide 21
21
Slide 22
22
Slide 23
23
Slide 24
24
Slide 25
25
Slide 26
26
Slide 27
27
Slide 28
28
Slide 29
29
Slide 30
30
Slide 31
31
Slide 32
32
Slide 33
33
Slide 34
34
Slide 35
35
Slide 36
36
Slide 37
37
Slide 38
38
Slide 39
39
Slide 40
40
Slide 41
41
Slide 42
42
Slide 43
43
Slide 44
44
Slide 45
45
Slide 46
46
Slide 47
47
Slide 48
48
Slide 49
49
Slide 50
50
Slide 51
51
Slide 52
52

About This Presentation

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


Slide Content

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

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

Small History Lesson Here 3

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

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

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

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

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 BTW This was the big concern of 1999!

Constantly Increasing Learning Curve 10

Impedance Mismatch 11

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

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

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

X DevAPI & The MySQL Document Store 15

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

find() 34

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

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

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

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 ()

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

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;

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 ()

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.

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

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

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

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

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

Built in JSON Bulk Loader 49

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

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