MySQL Tech Café #8: MySQL 8.0 for Python Developers

MySQL 8.0 for Python Developers #MySQL8isGreat Frédéric Descamps Community Manager MySQL June 2020 2 / 122

Safe Harbor The following is intended to outline our general product direction. It is intended for information purpose 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 up in making purchasing decisions. The development, release, timing and pricing of any features or functionality described for Oracle's product may change and remains at the sole discretion of Oracle Corporation. Statement in this presentation relating to Oracle's future plans, expectations, beliefs, intentions and ptospects are "forward-looking statements" and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that a ect our business is contained in Oracle's Securities and Exchange Commission (SEC) lings, including our most recent reports on Form 10-K and Form 10-Q under the heading "Risk Factors". These lings are available on the SEC's website or on Oracle's website at h p://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Copyright @ 2020 Oracle and/or its affiliates. 3 / 122

about.me/lefred Who am I ? Copyright @ 2020 Oracle and/or its affiliates. 4 / 122

Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.20 devops believer living in Belgium 🇧🇪 h ps://lefred.be Copyright @ 2020 Oracle and/or its affiliates. 5 / 122

Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.20 devops believer living in Belgium 🇧🇪 h ps://lefred.be Copyright @ 2020 Oracle and/or its affiliates. 6 / 122

MySQL Connector Python The Python connector for MySQL Copyright @ 2020 Oracle and/or its affiliates. 7 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Copyright @ 2020 Oracle and/or its affiliates. 8 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Copyright @ 2020 Oracle and/or its affiliates. 9 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Copyright @ 2020 Oracle and/or its affiliates. 10 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.6, 5.7 and 8.0 Copyright @ 2020 Oracle and/or its affiliates. 11 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.6, 5.7 and 8.0 SQL and NoSQL support Copyright @ 2020 Oracle and/or its affiliates. 12 / 122

NoSQL SQL MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.6, 5.7 and 8.0 SQL and NoSQL support Relational Tables and JSON Documents support Copyright @ 2020 Oracle and/or its affiliates. 13 / 122

MySQL Connector/Python 8 - 3 APIs Choice of Three APIs API Python Module Comment PEP249 Python Database API mysql.connector The traditional API C Extension API _mysql_connector Similar to the MySQL C API MySQL X DevAPI mysqlx New in 8.0, both SQL and NoSQL All you need to know is here: h ps://dev.mysql.com/doc/connector-python/en/ Copyright @ 2020 Oracle and/or its affiliates. 14 / 122

DATABASES = { 'default': { 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL Copyright @ 2020 Oracle and/or its affiliates. 15 / 122

DATABASES = { 'default': { 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL Currently supports Django < 3.0 Copyright @ 2020 Oracle and/or its affiliates. 16 / 122

MySQL Connector/Python 8 - installation easiest, use pip: use a MySQL repository for your distribution (fedora, ubuntu, debian, ...) Copyright @ 2020 Oracle and/or its affiliates. 17 / 122

MySQL Connector/Python 8 - installation or go to h ps://dev.mysql.com/downloads/connector/python/ Copyright @ 2020 Oracle and/or its affiliates. 18 / 122

MySQL and Python The classic way Copyright @ 2020 Oracle and/or its affiliates. 19 / 122

Using the classic protocol only import mysql.connector as mysql db = mysql.connect( host='localhost', user='fred', passwd='fred' ) cursor = db.cursor() query = "SELECT * FROM mydb.myrestaurants LIMIT 10" cursor.execute(query) records = cursor.fetchall() for record in records: print("{}, {}".format(record[0],record[1])); Copyright @ 2020 Oracle and/or its affiliates. 20 / 122

Using the classic protocol only import mysql.connector as mysql db = mysql.connect( host='localhost', user='fred', passwd='fred' ) cursor = db.cursor() query = "SELECT * FROM mydb.myrestaurants LIMIT 10" cursor.execute(query) records = cursor.fetchall() for record in records: print("{}, {}".format(record[0],record[1])); This allows connections to the classic MySQL protocol using port 3306 by default. Copyright @ 2020 Oracle and/or its affiliates. 21 / 122

Using the classic protocol only import mysql.connector as mysql db = mysql.connect( host='localhost', user='fred', passwd='fred' ) cursor = db.cursor() query = "SELECT * FROM mydb.myrestaurants LIMIT 10" cursor.execute(query) records = cursor.fetchall() for record in records: print("{}, {}".format(record[0],record[1])); This allows connections to the classic MySQL protocol using port 3306 by default. mysql.connector.errors.DatabaseError: 2007 (HY000): Protocol mismatch; server version = 11, client version = 10 Copyright @ 2020 Oracle and/or its affiliates. 22 / 122

MySQL and Python The easy way: MySQL X Protocol Copyright @ 2020 Oracle and/or its affiliates. 23 / 122

Using X Protocol import mysqlx SETTINGS = { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' } session = mysqlx.get_session(SETTINGS) query = "SELECT * FROM mydb.myrestaurants LIMIT 10" result = session.sql(query).execute() records = result.fetch_all() for record in records: print("{}, {}".format(record[0],record[1])); Copyright @ 2020 Oracle and/or its affiliates. 24 / 122

MySQL and JSON How MySQL works with JSON Copyright @ 2020 Oracle and/or its affiliates. 25 / 122

JavaScript Object Notation (JSON) { "_id": "5ad5b645f88c5bb8fe3fd337", "name": "Morris Park Bake Shop", "grades": [ { "date": "2014-03-03T00:00:00Z", "grade": "A", "score": 2 }, ], "address": { "coord": [ -73.856077, 40.848447 ], "street": "Morris Park Ave", "zipcode": "10462", "building": "1007" }, "borough": "Bronx", "cuisine": "Bakery", "restaurant_id": "30075445" } Copyright @ 2020 Oracle and/or its affiliates. 26 / 122

8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Copyright @ 2020 Oracle and/or its affiliates. 27 / 122

8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Copyright @ 2020 Oracle and/or its affiliates. 28 / 122

8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 Copyright @ 2020 Oracle and/or its affiliates. 29 / 122

8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 >30 JSON functions Copyright @ 2020 Oracle and/or its affiliates. 30 / 122

8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document Copyright @ 2020 Oracle and/or its affiliates. 31 / 122

8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator Copyright @ 2020 Oracle and/or its affiliates. 32 / 122

8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: Copyright @ 2020 Oracle and/or its affiliates. 33 / 122

8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: .* all members in the object [*] all values in the array [pre x]**su x path beginning with pre x and ending with su x Copyright @ 2020 Oracle and/or its affiliates. 34 / 122

MySQL Document Store some words about this solution Copyright @ 2020 Oracle and/or its affiliates. 35 / 122

Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema exible JSON Document Store No SQL required No need to de ne all possible a ributes, tables, etc. Uses new X DevAPI Can leverage generated column to extract JSON values into materialized columns that can be indexed for fast SQL searches. Document can be ~1GB It's a column in a row of a table It cannot exceed max_allowed_packet Allows use of modern programming styles No more embedded strings of SQL in your code Easy to read Also works with relational Tables Proven MySQL Technology Copyright @ 2020 Oracle and/or its affiliates. 36 / 122

X Protocol Connectors Copyright @ 2020 Oracle and/or its affiliates. 37 / 122

X DevAPI Developed from scratch for modern development Supports: SQL NoSQL - JSON documents NoSQL - SQL tables Uniform API across programming languages We provide connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too Supported in the MySQL Shell Full ACID support Savepoints Connection pools (as of 8.0.13) Copyright @ 2020 Oracle and/or its affiliates. 38 / 122

Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2020 Oracle and/or its affiliates. 39 / 122

Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2020 Oracle and/or its affiliates. 40 / 122

Copyright @ 2020 Oracle and/or its affiliates. 41 / 122

Let's query Copyright @ 2020 Oracle and/or its affiliates. 43 / 122

Let's query That's too much records to show on this slide... Copyright @ 2020 Oracle and/or its affiliates. 44 / 122

Let's query That's too much records to show on this slide... Copyright @ 2020 Oracle and/or its affiliates. 45 / 122

Let's query That's too much records to show on this slide... Let's add a limit to it Copyright @ 2020 Oracle and/or its affiliates. 46 / 122

Some more examples Copyright @ 2020 Oracle and/or its affiliates. 48 / 122

Some more examples Let's add a selection criteria: Copyright @ 2020 Oracle and/or its affiliates. 49 / 122

Using IN... Copyright @ 2020 Oracle and/or its affiliates. 50 / 122

Syntax slightly different than MongoDB Copyright @ 2020 Oracle and/or its affiliates. 51 / 122

Syntax slightly different than MongoDB Copyright @ 2020 Oracle and/or its affiliates. 52 / 122

CRUD operations Copyright @ 2020 Oracle and/or its affiliates. 53 / 122

CRUD operations for collections Add a document collection.add({ name: 'fred', age: 42 }) .add({ name: 'dave', age: 23 }) .execute() collection.add([ { name: 'dimo', age: 50 }, { name: 'kenny', age: 25 } ]).execute() Copyright @ 2020 Oracle and/or its affiliates. 54 / 122

CRUD operations for collections Modify a document collection.modify('name = :name') .bind('name', 'fred') .set('age', 43) .sort('name ASC') .limit(1) .execute() collection.modify('name = :name') .bind('name', 'fred') .patch({ age: 43, active: false }) .sort('name DESC') .limit(1) .execute() Copyright @ 2020 Oracle and/or its affiliates. 55 / 122

CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2020 Oracle and/or its affiliates. 56 / 122

MySQL Document Store Objects Summary Copyright @ 2020 Oracle and/or its affiliates. 57 / 122

All you need to know is here: h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2020 Oracle and/or its affiliates. 58 / 122

CRUD operations in Python MySQL Document Store Copyright @ 2020 Oracle and/or its affiliates. 59 / 122

Some coding... import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('restaurants') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2020 Oracle and/or its affiliates. 60 / 122

Some coding... import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('restaurants') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2020 Oracle and/or its affiliates. 61 / 122

If it was not yet obvious, you can now use MySQL without SQL ! Copyright @ 2020 Oracle and/or its affiliates. 62 / 122

OK we have Document Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2020 Oracle and/or its affiliates. 63 / 122

Challenge: list the best restaurant of each type of food and show the top 10, with the best one first ! don't forget that all these restaurants are just JSON documents Copyright @ 2020 Oracle and/or its affiliates. 64 / 122

NoSQL as SQL - aggregation Copyright @ 2020 Oracle and/or its affiliates. 65 / 122

import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('restaurants') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant result = session.sql('''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 docstore.restaurants) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10''').execute() for restaurant in result.fetch_all(): print "%s - %s - %d " % (restaurant[0], restaurant[1], restaurant[2]) session.close() Copyright @ 2020 Oracle and/or its affiliates. 69 / 122

Output Copyright @ 2020 Oracle and/or its affiliates. 70 / 122

COOL ! But my application consumes only JSON Copyright @ 2020 Oracle and/or its affiliates. 71 / 122

And back to JSON And of course it's possible to return the result as a JSON document: result = session.sql('''select JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT( "name", name, "cuisine", cuisine, "score", avg_score))) from (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 docstore.all_recs) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10) as t''').execute() row = result.fetch_one() print row[0] Copyright @ 2020 Oracle and/or its affiliates. 72 / 122

And back to JSON - output Copyright @ 2020 Oracle and/or its affiliates. 73 / 122

some extras... The hidden part of the iceberg Copyright @ 2020 Oracle and/or its affiliates. 74 / 122

_id Every document has a unique identi er called the document ID. It can be manually assigned when adding a document or generated and assigned to the document automatically ! Since MySQL 8.0.11, the _id is generated by the server. To get the list of automatically generated IDs use the result.getGeneratedIDs() method. The _id is made of 3 parts, all hex encoded: pre x timestamp when the MySQL Server instance was started auto-increment counter Globally unique IDs Optimized for InnoDB storage Examples 00005cc17b700000000000000003 Copyright @ 2020 Oracle and/or its affiliates. 75 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: Copyright @ 2020 Oracle and/or its affiliates. 76 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2020 Oracle and/or its affiliates. 77 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON Copyright @ 2020 Oracle and/or its affiliates. 78 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 Copyright @ 2020 Oracle and/or its affiliates. 79 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Copyright @ 2020 Oracle and/or its affiliates. 80 / 122

Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... We do care about your data ! Copyright @ 2020 Oracle and/or its affiliates. 81 / 122

What time is it now ? Time for some more examples ! Copyright @ 2020 Oracle and/or its affiliates. 82 / 122

More fun with The Walking Dead Copyright @ 2020 Oracle and/or its affiliates. 83 / 122

Example: All Episodes of Season 1 Copyright @ 2020 Oracle and/or its affiliates. 84 / 122

Example: All First Episodes of Each Season Copyright @ 2020 Oracle and/or its affiliates. 85 / 122

Example: Speed Up Episode's Name Lookup Copyright @ 2020 Oracle and/or its affiliates. 86 / 122

Example: Do more with SQL List the amount of episodes by season: Copyright @ 2020 Oracle and/or its affiliates. 90 / 122

Example: Do more with SQL (2) Episode statistics for each season: Copyright @ 2020 Oracle and/or its affiliates. 91 / 122

Example: Do more with SQL (3) Number of days between episodes: Copyright @ 2020 Oracle and/or its affiliates. 92 / 122

JSON Data validation It's possible to have constraints between collections (or tables): Copyright @ 2020 Oracle and/or its affiliates. 93 / 122

JSON Data validation (2) Let's see in SQL how the 2 collections look like: Copyright @ 2020 Oracle and/or its affiliates. 94 / 122

JSON Data validation (3) Let's add one virtual column in each collection: Copyright @ 2020 Oracle and/or its affiliates. 95 / 122

JSON Data validation (4) Now we can index the reference and create the constraint: Copyright @ 2020 Oracle and/or its affiliates. 96 / 122

JSON Data validation (5) And now even in pure NoSQL CRUD, we can see the data validation in action: Copyright @ 2020 Oracle and/or its affiliates. 98 / 122

JSON Schema validation - 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2020 Oracle and/or its affiliates. 99 / 122

JSON Schema validation (2) - 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2020 Oracle and/or its affiliates. 102 / 122

JSON Schema validation (2) - 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2020 Oracle and/or its affiliates. 103 / 122

JSON Schema validation (3) - 8.0.17 Knowing why thanks to the new JSON_SCHEMA_VALIDATION_REPORT function: Copyright @ 2020 Oracle and/or its affiliates. 104 / 122

JSON Schema validation (4) - 8.0.17 ...but when we use JSON_SCHEMA_VALID and CHECK CONSTRAINT together : Copyright @ 2020 Oracle and/or its affiliates. 106 / 122

JSON Schema validation (4) - 8.0.17 ...but when we use JSON_SCHEMA_VALID and CHECK CONSTRAINT together : Copyright @ 2020 Oracle and/or its affiliates. 107 / 122

Bye Bye Yes you can get rid of ORM Copyright @ 2020 Oracle and/or its affiliates. 110 / 122

Hello mysqlx ! Yes you can get rid of ORM Copyright @ 2020 Oracle and/or its affiliates. 111 / 122

MySQL 8.0 and for Zope & Plone users ? Copyright @ 2020 Oracle and/or its affiliates. 112 / 122

With relstorage 3 it's possible to store the full zodb to MySQL. see h ps://github.com/zodb/relstorage/ Store Zodb to MySQL Copyright @ 2020 Oracle and/or its affiliates. 113 / 122

what do I gain ? Conclusion Copyright @ 2020 Oracle and/or its affiliates. 114 / 122

Conclusion What do I gain by using MySQL as Python developer ? Data integrity ACID Compliant Transactions SQL schemaless exible data structure easy to start (CRUD) NoSQL Copyright @ 2020 Oracle and/or its affiliates. 115 / 122

8.0 http://lefred.be/content/top-10-reasons-for- nosql-with-mysql/ Copyright @ 2020 Oracle and/or its affiliates. 116 / 122

8.0 h ps://www.slideshare.net/lefred.descamps/mysql- shell-the-best-dba-tool h ps://github.com/lefred/mysql-shell- mydba h ps://github.com/lefred/mysql-shell- innotop More with MySQL Shell & Python Copyright @ 2020 Oracle and/or its affiliates. 117 / 122

MySQL Shell Reporting Framework - 8.0.16 Now it's also possible to create user-de ned reports directly in the Shell. They can be wri en in Python and called the same way on every operating systems. No need for extra libraries ! More Info: h ps://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-reporting.html h ps://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/ h ps://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-de ned-reports/ Copyright @ 2020 Oracle and/or its affiliates. 118 / 122

MySQL Shell Extentions & Plugins - 8.0.17 Since 8.0.17, MySQL Shell has been extended to allow you to write your own extensions and plugins ! More Info: h ps://mysqlserverteam.com/mysql-shell-plugins-introduction/ h ps://lefred.be/content/overview-on-mysql-shell-8-0-17-extensions-plugins-and- how-to-write-yours/ Copyright @ 2020 Oracle and/or its affiliates. 119 / 122

Credits Thank you Olivier Dasini for some TV Show examples: h p://dasini.net/blog/2019/04/02/mysql-json-document-store/ Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell Copyright @ 2020 Oracle and/or its affiliates. 120 / 122

MySQL Tech Café #8: MySQL 8.0 for Python Developers

More Related Content

What's hot

Similar to MySQL Tech Café #8: MySQL 8.0 for Python Developers

More from Frederic Descamps

Recently uploaded

MySQL Tech Café #8: MySQL 8.0 for Python Developers