Frederic Descamps, profile picture
Uploaded byFrederic Descamps
962 views

PyConX - Python & MySQL 8.0 Document Store

The document discusses the integration of Python with MySQL 8.0, specifically focusing on its document store capabilities. It highlights MySQL's native support for JSON data, flexible schema, and the new X DevAPI for working with both SQL and NoSQL data. Additionally, it includes migration examples, connector information, and outlines how to perform CRUD operations.

Downloaded 20 times
1 / 104
2 / 104
3 / 104
4 / 104
5 / 104
6 / 104
7 / 104
8 / 104
9 / 104
10 / 104
11 / 104
12 / 104
13 / 104
14 / 104
15 / 104
16 / 104
17 / 104
18 / 104
19 / 104
20 / 104
21 / 104
22 / 104
23 / 104
24 / 104
25 / 104
26 / 104
27 / 104
28 / 104
29 / 104
30 / 104
31 / 104
32 / 104
33 / 104
34 / 104
35 / 104
36 / 104
37 / 104
38 / 104
39 / 104
40 / 104
41 / 104
42 / 104
43 / 104
44 / 104
45 / 104
46 / 104
47 / 104
48 / 104
49 / 104
50 / 104
51 / 104
52 / 104
53 / 104
54 / 104
55 / 104
56 / 104
57 / 104
58 / 104
59 / 104
60 / 104
61 / 104
62 / 104
63 / 104
64 / 104
65 / 104
66 / 104
67 / 104
68 / 104
69 / 104
70 / 104
71 / 104
72 / 104
73 / 104
74 / 104
75 / 104
76 / 104
77 / 104
78 / 104
79 / 104
80 / 104
81 / 104
82 / 104
83 / 104
84 / 104
85 / 104
86 / 104
87 / 104
88 / 104
89 / 104
90 / 104
91 / 104
92 / 104
93 / 104
94 / 104
95 / 104
96 / 104
97 / 104
98 / 104
99 / 104
100 / 104
101 / 104
102 / 104
103 / 104
104 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 1/104 1 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 2/104 2 / 1042 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 3/104   Safe Harbor Statement 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 and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 3 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 4/104 about me - http://about.me/lefred Who am I ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 4 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 5/104 Frédéric Descamps @lefred MySQL Evangelist Hacking MySQL since 3.23 devops believer living in Belgium 🇧🇪 http://lefred.be   Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 5 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 6/104 MySQL and JSON How MySQL works with JSON Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 6 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 7/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 7 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 8/104 8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 8 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 9/104 8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 9 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 10/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 10 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 11/104 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 28 JSON functions Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 11 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 12/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 12 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 13/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 13 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 14/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 14 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 15/104 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 [prefix]**suffix path beginning with prefix and ending with suffix Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 15 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 16/104 MySQL Document Store the Solution Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 16 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 17/104 Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema flexible JSON Document Store No SQL required No need to define all possible attributes, 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 17 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 18/104 X Protocol Connectors Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 18 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 19/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 19 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 20/104 PY JS SQL > MySQL 8.0 Upgrade Checker Auto Completion & Command History Output Formats (table, json, tabs) Prompt Themes Batch Execution 5.7 8.0 JS Document Store X dev API SQL CLI InnoDB Cluster importJSON JSJS Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 20 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 21/104 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 21 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 22/104 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 22 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 23/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 23 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 24/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 24 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 25/104 Let´s query Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 25 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 26/104 Let´s query That´s too much records to show here... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 26 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 27/104 Let´s query That´s too much records to show here... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 27 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 28/104 Let´s query That´s too much records to show here... Let´s add a limit to it Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 28 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 29/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 29 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 30/104 Some more examples Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 30 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 31/104 Some more examples Let´s add a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 31 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 32/104 Using IN... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 32 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 33/104 Syntax slightly different than MongoDB Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 33 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 34/104 Syntax slightly different than MongoDB > db.restaurants. nd({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name": 1,"cuisine": 1, "borough": 1}).limit(2) { "borough" : "Queens", "cuisine" : "French", "name" : "La Baraka Restaurant" } { "borough" : "Queens", "cuisine" : "French", "name" : "Air France Lounge" } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 34 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 35/104 CRUD operations Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 35 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 36/104 CRUD operations for collections Add a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 36 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 37/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 37 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 38/104 CRUD operations for collections Modify a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 38 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 39/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 39 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 40/104 CRUD operations for collections Remove a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 40 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 41/104 CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 41 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 42/104 MySQL Document Store Objects Summary Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 42 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 43/104 All you need to know is here: https://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 43 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 44/104 MySQL Connector Python The Python connector for MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 44 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 45/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 45 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 46/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 46 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 47/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 47 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 48/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 48 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 49/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 49 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 50/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 50 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 51/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support All you need to know is here: https://dev.mysql.com/doc/connector-python/en/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 51 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 52/104 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 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 52 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 53/104   MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL DATABASES = { 'default': { 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 53 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 54/104 MySQL Connector/Python 8 - installation easiest, use pip: use a MySQL repository for your distribution (fedora, ubuntu, debian, ...) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 54 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 55/104 MySQL Connector/Python 8 - installation (2) or go to https://dev.mysql.com/downloads/connector/python/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 55 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 56/104 What time is it ? Time to code ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 56 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 57/104 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('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 57 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 58/104 If it was not yet obvious, you can now use MySQL without SQL ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 58 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 59/104 OK we have Document Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 59 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 60/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 60 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 61/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 61 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 62/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 62 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 63/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 63 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 64/104 import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') 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.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''').execute() for restaurant in result.fetch_all(): print "%s - %s - %d " % (restaurant[0], restaurant[1], restaurant[2]) session.close() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 64 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 65/104 Output Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 65 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 66/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 66 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 67/104 And back to JSON - output Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 68/104 some extras... The hidden part of the iceberg Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 68 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 69/104 _id Every document has a unique identifier 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: prefix timestamp when the MySQL Server instance was started auto-increment counter Globally unique IDs Optimized for InnoDB storage Examples 00005cc17b700000000000000003 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 69 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 70/104 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 70 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 71/104 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 71 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 72/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 72 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 73/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 73 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 74/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 74 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 75/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 75 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 76/104 What time is it now ? Time for some more examples ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 77/104 More fun with The Walking Dead Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 78/104 Example: All Episodes of Season 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 79/104 Example: All First Episodes of Each Season Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 79 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 80/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 80 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 81/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 81 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 82/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 83/104 Example: Transaction Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 83 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 84/104 Example: Do more with SQL List the amount of episodes by season: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 84 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 85/104 Example: Do more with SQL (2) Episode statistics for each season: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 85 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 86/104 Example: Do more with SQL (3) Number of days between episodes: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 86 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 87/104 JSON Data validation It's possible to have constraints between collections (or tables): Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 87 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 88/104 JSON Data validation (2) Let's see in SQL how the 2 collections look like: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 88 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 89/104 JSON Data validation (3) Let's add one virtual column in each collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 89 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 90/104 JSON Data validation (4) Now we can index the reference and create the constraint: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 90 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 91/104 JSON Data validation (4) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 91 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 92/104 JSON Data validation (5) And now even in pure NoSQL CRUD, we can see the data validation in action: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 92 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 93/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 93 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 94/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 94 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 95/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 95 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 96/104 what do I gain ? Conclusion Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 96 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 97/104 schemaless flexible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Data integrity ACID Compliant Transactions SQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 97 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 98/104 98 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 99/104 8.0 http://lefred.be/content/top-10-reasons-for- nosql-with-mysql/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 99 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 100/104 8.0 https://www.slideshare.net/lefred.descamps/mysql- shell-the-best-dba-tool https://github.com/lefred/mysql- shell-mydba https://github.com/lefred/mysql- shell-innotop More with MySQL Shell & Python Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 100 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 101/104 MySQL Shell Reporting Framework - 8.0.16 Now it's also possible to create user-defined reports directly in the Shell. They can be written in Python and called the same way on every operating systems. No need for extra libraries ! More Info: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-reporting.html https://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/ https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 101 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 102/104 Credits Thank you Olivier Dasini for some TV Show examples: http://dasini.net/blog/2019/04/02/mysql-json-document-store/ Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 102 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 103/104 103 / 104
5/2/2019 Python & MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 104/104 Thank you ! Any Questions ? share your 💕 for MySQL on social media using @mysql #MySQL8isGreat #MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 104 / 104

More Related Content

PDF
Oracle Code Roma: NoSQL + SQL = MySQL
byFrederic Descamps
 
PDF
How to write efficient and correct MySQL recipes for you configuration manage...
byFrederic Descamps
 
PDF
MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in ...
byFrederic Descamps
 
PDF
MySQL Shell: the best DBA tool ?
byFrederic Descamps
 
PDF
MySQL Group Replication: the magic explained v.2
byFrederic Descamps
 
PDF
DataOpsbarcelona 2019: Deep dive into MySQL Group Replication... the magic e...
byFrederic Descamps
 
PDF
MySQL Shell : the best DBA tool ?
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
byFrederic Descamps
 
Oracle Code Roma: NoSQL + SQL = MySQL
byFrederic Descamps
 
How to write efficient and correct MySQL recipes for you configuration manage...
byFrederic Descamps
 
MySQL New York Meetup: MySQL 8.0 Document Store - How to mix NoSQL & SQL in ...
byFrederic Descamps
 
MySQL Shell: the best DBA tool ?
byFrederic Descamps
 
MySQL Group Replication: the magic explained v.2
byFrederic Descamps
 
DataOpsbarcelona 2019: Deep dive into MySQL Group Replication... the magic e...
byFrederic Descamps
 
MySQL Shell : the best DBA tool ?
byFrederic Descamps
 
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
byFrederic Descamps
 

What's hot

PDF
MySQL 8.0 Document Store - how to mix NoSQL & SQL in MySQL 8.0
byFrederic Descamps
 
PDF
MySQL : State of the Dolphin May 2019
byFrederic Descamps
 
PDF
MySQL Shell - the best DBA tool ?
byFrederic Descamps
 
PDF
Percona Live Europe 2018 MySQL Group Replication... the magic explained
byFrederic Descamps
 
PPTX
PHP, The X DevAPI, and the MySQL Document Store -- Benelux PHP Confernece 2019
byDave Stokes
 
PDF
Python and MySQL 8.0 Document Store
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster - Advanced Configuration & Operations
byFrederic Descamps
 
PDF
How to operate MySQL InnoDB Cluster with MySQL Shell
byFrederic Descamps
 
PDF
OpenExpo Virtual Experience 2020: MySQL 8.0 Document Store - Discovery of a n...
byFrederic Descamps
 
PDF
State of the Dolphin 2020 - 25th Anniversary of MySQL with 8.0.20
byFrederic Descamps
 
PDF
MySQL Database Service Webinar - Installing WordPress in OCI with MDS
byFrederic Descamps
 
PDF
MySQL 8.0 : High Availability Solution for Everybody
byFrederic Descamps
 
PDF
MySQL Shell: the best DBA tool !
byFrederic Descamps
 
PDF
MySQL Group Replication: Handling Network Glitches - Best Practices
byFrederic Descamps
 
PDF
Oracle Open World Middle East - MySQL 8 a Giant Leap for SQL
byFrederic Descamps
 
PDF
MySQL Document Store - How to replace a NoSQL database by MySQL without effor...
byFrederic Descamps
 
PDF
MySQL Tech Café #8: MySQL 8.0 for Python Developers
byFrederic Descamps
 
PDF
MySQL 8.0 InnoDB Cluster - Easiest Tutorial
byFrederic Descamps
 
PDF
FOSDEM MySQL & Friends Devroom, February 2018 MySQL Point-in-Time Recovery l...
byFrederic Descamps
 
PDF
MySQL Document Store - when SQL & NoSQL live together... in peace!
byFrederic Descamps
 
MySQL 8.0 Document Store - how to mix NoSQL & SQL in MySQL 8.0
byFrederic Descamps
 
MySQL : State of the Dolphin May 2019
byFrederic Descamps
 
MySQL Shell - the best DBA tool ?
byFrederic Descamps
 
Percona Live Europe 2018 MySQL Group Replication... the magic explained
byFrederic Descamps
 
PHP, The X DevAPI, and the MySQL Document Store -- Benelux PHP Confernece 2019
byDave Stokes
 
Python and MySQL 8.0 Document Store
byFrederic Descamps
 
MySQL InnoDB Cluster - Advanced Configuration & Operations
byFrederic Descamps
 
How to operate MySQL InnoDB Cluster with MySQL Shell
byFrederic Descamps
 
OpenExpo Virtual Experience 2020: MySQL 8.0 Document Store - Discovery of a n...
byFrederic Descamps
 
State of the Dolphin 2020 - 25th Anniversary of MySQL with 8.0.20
byFrederic Descamps
 
MySQL Database Service Webinar - Installing WordPress in OCI with MDS
byFrederic Descamps
 
MySQL 8.0 : High Availability Solution for Everybody
byFrederic Descamps
 
MySQL Shell: the best DBA tool !
byFrederic Descamps
 
MySQL Group Replication: Handling Network Glitches - Best Practices
byFrederic Descamps
 
Oracle Open World Middle East - MySQL 8 a Giant Leap for SQL
byFrederic Descamps
 
MySQL Document Store - How to replace a NoSQL database by MySQL without effor...
byFrederic Descamps
 
MySQL Tech Café #8: MySQL 8.0 for Python Developers
byFrederic Descamps
 
MySQL 8.0 InnoDB Cluster - Easiest Tutorial
byFrederic Descamps
 
FOSDEM MySQL & Friends Devroom, February 2018 MySQL Point-in-Time Recovery l...
byFrederic Descamps
 
MySQL Document Store - when SQL & NoSQL live together... in peace!
byFrederic Descamps
 

Similar to PyConX - Python & MySQL 8.0 Document Store

PDF
20190915_MySQL開発最新動向
byMachiko Ikoma
 
PPTX
Python And The MySQL X DevAPI - PyCaribbean 2019
byDave Stokes
 
PDF
20180921_DOAG_BigDataDays_OracleSpatialandPython_kpatenge
byKarin Patenge
 
PDF
20190713_MySQL開発最新動向
byMachiko Ikoma
 
PDF
MySQL Document Store (Oracle Code Warsaw 2018)
byVittorio Cioe
 
PPTX
PHP, The X DevAPI, and the MySQL Document Store Presented January 23rd, 20...
byDave Stokes
 
PDF
MySQL Router - Explore The Secrets (MySQL Belgian Days 2024)
byMiguel Araújo
 
PDF
Node.js and Oracle Database: New Development Techniques
byChristopher Jones
 
PDF
Introduction to Apache NiFi 1.11.4
byTimothy Spann
 
PDF
MySQL Day Paris 2018 - What’s New in MySQL 8.0 ?
byOlivier DASINI
 
PDF
Marcin Szałowicz - MySQL Workbench
byWomen in Technology Poland
 
PDF
Oracle Open World 2018 / Code One : MySQL 8.0 Document Store
byFrederic Descamps
 
ODP
OrientDB for real & Web App development
byLuca Garulli
 
PDF
MySQL 8.0 Introduction to NoSQL + SQL
byManuel Contreras
 
PDF
Database@Home - Data Driven : Loading, Indexing, and Searching with Text and ...
byTammy Bednar
 
PDF
クラウドのコストを大幅削減！事例から見るクラウド間移行の効果(Oracle Cloudウェビナーシリーズ: 2020年7月8日)
byオラクルエンジニア通信
 
PDF
RMOUG MySQL 5.7 New Features
byDave Stokes
 
PDF
2016-07-06-openphacts-docker
byStian Soiland-Reyes
 
PPTX
A Step by Step Introduction to the MySQL Document Store
byDave Stokes
 
PDF
Mysql8for blr usercamp
byMysql User Camp
 
20190915_MySQL開発最新動向
byMachiko Ikoma
 
Python And The MySQL X DevAPI - PyCaribbean 2019
byDave Stokes
 
20180921_DOAG_BigDataDays_OracleSpatialandPython_kpatenge
byKarin Patenge
 
20190713_MySQL開発最新動向
byMachiko Ikoma
 
MySQL Document Store (Oracle Code Warsaw 2018)
byVittorio Cioe
 
PHP, The X DevAPI, and the MySQL Document Store Presented January 23rd, 20...
byDave Stokes
 
MySQL Router - Explore The Secrets (MySQL Belgian Days 2024)
byMiguel Araújo
 
Node.js and Oracle Database: New Development Techniques
byChristopher Jones
 
Introduction to Apache NiFi 1.11.4
byTimothy Spann
 
MySQL Day Paris 2018 - What’s New in MySQL 8.0 ?
byOlivier DASINI
 
Marcin Szałowicz - MySQL Workbench
byWomen in Technology Poland
 
Oracle Open World 2018 / Code One : MySQL 8.0 Document Store
byFrederic Descamps
 
OrientDB for real & Web App development
byLuca Garulli
 
MySQL 8.0 Introduction to NoSQL + SQL
byManuel Contreras
 
Database@Home - Data Driven : Loading, Indexing, and Searching with Text and ...
byTammy Bednar
 
クラウドのコストを大幅削減！事例から見るクラウド間移行の効果(Oracle Cloudウェビナーシリーズ: 2020年7月8日)
byオラクルエンジニア通信
 
RMOUG MySQL 5.7 New Features
byDave Stokes
 
2016-07-06-openphacts-docker
byStian Soiland-Reyes
 
A Step by Step Introduction to the MySQL Document Store
byDave Stokes
 
Mysql8for blr usercamp
byMysql User Camp
 

More from Frederic Descamps

PDF
MySQL Innovation & Cloud Day - Document Store avec MySQL HeatWave Database Se...
byFrederic Descamps
 
PDF
MySQL Day Roma - MySQL Shell and Visual Studio Code Extension
byFrederic Descamps
 
PDF
RivieraJUG - MySQL Indexes and Histograms
byFrederic Descamps
 
PDF
RivieraJUG - MySQL 8.0 - What's new for developers.pdf
byFrederic Descamps
 
PDF
MySQL User Group NL - MySQL 8
byFrederic Descamps
 
PDF
State of the Dolphin - May 2022
byFrederic Descamps
 
PDF
Percona Live 2022 - MySQL Shell for Visual Studio Code
byFrederic Descamps
 
PDF
Percona Live 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
PDF
Percona Live 2022 - MySQL Architectures
byFrederic Descamps
 
PDF
LinuxFest Northwest 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
PDF
Open Source 101 2022 - MySQL Indexes and Histograms
byFrederic Descamps
 
PDF
Pi Day 2022 - from IoT to MySQL HeatWave Database Service
byFrederic Descamps
 
PDF
Confoo 2022 - le cycle d'une instance MySQL
byFrederic Descamps
 
PDF
FOSDEM 2022 MySQL Devroom: MySQL 8.0 - Logical Backups, Snapshots and Point-...
byFrederic Descamps
 
PDF
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
PDF
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
PDF
State of The Dolphin - May 2021
byFrederic Descamps
 
PDF
MySQL Shell for DBAs
byFrederic Descamps
 
PDF
Deploying Magento on OCI with MDS
byFrederic Descamps
 
PDF
MySQL Router REST API
byFrederic Descamps
 
MySQL Innovation & Cloud Day - Document Store avec MySQL HeatWave Database Se...
byFrederic Descamps
 
MySQL Day Roma - MySQL Shell and Visual Studio Code Extension
byFrederic Descamps
 
RivieraJUG - MySQL Indexes and Histograms
byFrederic Descamps
 
RivieraJUG - MySQL 8.0 - What's new for developers.pdf
byFrederic Descamps
 
MySQL User Group NL - MySQL 8
byFrederic Descamps
 
State of the Dolphin - May 2022
byFrederic Descamps
 
Percona Live 2022 - MySQL Shell for Visual Studio Code
byFrederic Descamps
 
Percona Live 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
Percona Live 2022 - MySQL Architectures
byFrederic Descamps
 
LinuxFest Northwest 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
Open Source 101 2022 - MySQL Indexes and Histograms
byFrederic Descamps
 
Pi Day 2022 - from IoT to MySQL HeatWave Database Service
byFrederic Descamps
 
Confoo 2022 - le cycle d'une instance MySQL
byFrederic Descamps
 
FOSDEM 2022 MySQL Devroom: MySQL 8.0 - Logical Backups, Snapshots and Point-...
byFrederic Descamps
 
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
State of The Dolphin - May 2021
byFrederic Descamps
 
MySQL Shell for DBAs
byFrederic Descamps
 
Deploying Magento on OCI with MDS
byFrederic Descamps
 
MySQL Router REST API
byFrederic Descamps
 

Recently uploaded

PPTX
Cloud Metrics: The Cost-Value Equation (All Things Open 2025)
byEric D. Schabell
 
PDF
Readying Enterprise Networks for Artificial Intelligence
byEnterprise Management Associates
 
PDF
Supercharge your JVM performance with Project Leyden and Spring Boot
byAmmbra
 
PDF
Comprehensive Analysis of OpenAI's Strategic Transformation at DevDay 2025.pdf
byAlex Doan
 
PDF
Mapping Your Ecosystem: Uniting Content and Data across Systems
byRustici Software
 
PDF
Master Deck: GraphSummit Bengaluru (Oct 7)
byNeo4j
 
PDF
Phishing for Answers: A Tech Filler Quiz.pdf
byConquiztadors- the Quiz Society of Sri Venkateswara College
 
PPTX
UiPath Platform: Architecture & Context [1/3]
bysuhanisingh58689
 
PDF
What Is Agentic AI and How Is It Transforming ERP Systems?
byScalaCode
 
PDF
Certified Kubernetes Security Specialist (CKS): Unit 5
byVICTOR MAESTRE RAMIREZ
 
PDF
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
PDF
Session 1 - Agentic Automation Building the Enterprise Agent of Tomorrow
byDianaGray10
 
PDF
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 
PDF
Towards efficient vision representation and coding standards for superior emb...
byTouradj Ebrahimi
 
PDF
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
PDF
Session 2 - Specialized AI Associate Series: Orchestrator Deep-Dive and UiPa...
byDianaGray10
 
PPTX
SIH Presentation praniawar2o25 cse.pptx
bysamikshamatkar91
 
PDF
Web Mapping 101: Creating Dynamic Web Maps with Geospatial Data
bySafe Software
 
PPTX
TechVerse Kent (Combining Tech, AI, Innovation and Creativity)
byKritarthDevli
 
PDF
A SEA of Energy Efficiency Opportunities!
byMemoori
 
Cloud Metrics: The Cost-Value Equation (All Things Open 2025)
byEric D. Schabell
 
Readying Enterprise Networks for Artificial Intelligence
byEnterprise Management Associates
 
Supercharge your JVM performance with Project Leyden and Spring Boot
byAmmbra
 
Comprehensive Analysis of OpenAI's Strategic Transformation at DevDay 2025.pdf
byAlex Doan
 
Mapping Your Ecosystem: Uniting Content and Data across Systems
byRustici Software
 
Master Deck: GraphSummit Bengaluru (Oct 7)
byNeo4j
 
Phishing for Answers: A Tech Filler Quiz.pdf
byConquiztadors- the Quiz Society of Sri Venkateswara College
 
UiPath Platform: Architecture & Context [1/3]
bysuhanisingh58689
 
What Is Agentic AI and How Is It Transforming ERP Systems?
byScalaCode
 
Certified Kubernetes Security Specialist (CKS): Unit 5
byVICTOR MAESTRE RAMIREZ
 
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
Session 1 - Agentic Automation Building the Enterprise Agent of Tomorrow
byDianaGray10
 
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 
Towards efficient vision representation and coding standards for superior emb...
byTouradj Ebrahimi
 
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
Session 2 - Specialized AI Associate Series: Orchestrator Deep-Dive and UiPa...
byDianaGray10
 
SIH Presentation praniawar2o25 cse.pptx
bysamikshamatkar91
 
Web Mapping 101: Creating Dynamic Web Maps with Geospatial Data
bySafe Software
 
TechVerse Kent (Combining Tech, AI, Innovation and Creativity)
byKritarthDevli
 
A SEA of Energy Efficiency Opportunities!
byMemoori
 

PyConX - Python & MySQL 8.0 Document Store

  • 1.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 1/104 1 / 104
  • 2.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 2/104 2 / 1042 / 104
  • 3.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 3/104   Safe Harbor Statement 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 and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 3 / 104
  • 4.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 4/104 about me - http://about.me/lefred Who am I ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 4 / 104
  • 5.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 5/104 Frédéric Descamps @lefred MySQL Evangelist Hacking MySQL since 3.23 devops believer living in Belgium 🇧🇪 http://lefred.be   Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 5 / 104
  • 6.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 6/104 MySQL and JSON How MySQL works with JSON Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 6 / 104
  • 7.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 7/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 7 / 104
  • 8.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 8/104 8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 8 / 104
  • 9.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 9/104 8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 9 / 104
  • 10.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 10/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 10 / 104
  • 11.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 11/104 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 28 JSON functions Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 11 / 104
  • 12.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 12/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 12 / 104
  • 13.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 13/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 13 / 104
  • 14.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 14/104 8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 14 / 104
  • 15.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 15/104 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 [prefix]**suffix path beginning with prefix and ending with suffix Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 15 / 104
  • 16.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 16/104 MySQL Document Store the Solution Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 16 / 104
  • 17.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 17/104 Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema flexible JSON Document Store No SQL required No need to define all possible attributes, 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 17 / 104
  • 18.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 18/104 X Protocol Connectors Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 18 / 104
  • 19.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 19/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 19 / 104
  • 20.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 20/104 PY JS SQL > MySQL 8.0 Upgrade Checker Auto Completion & Command History Output Formats (table, json, tabs) Prompt Themes Batch Execution 5.7 8.0 JS Document Store X dev API SQL CLI InnoDB Cluster importJSON JSJS Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 20 / 104
  • 21.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 21/104 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 21 / 104
  • 22.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 22/104 Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 22 / 104
  • 23.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 23/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 23 / 104
  • 24.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 24/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 24 / 104
  • 25.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 25/104 Let´s query Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 25 / 104
  • 26.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 26/104 Let´s query That´s too much records to show here... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 26 / 104
  • 27.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 27/104 Let´s query That´s too much records to show here... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 27 / 104
  • 28.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 28/104 Let´s query That´s too much records to show here... Let´s add a limit to it Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 28 / 104
  • 29.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 29/104 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 29 / 104
  • 30.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 30/104 Some more examples Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 30 / 104
  • 31.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 31/104 Some more examples Let´s add a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 31 / 104
  • 32.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 32/104 Using IN... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 32 / 104
  • 33.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 33/104 Syntax slightly different than MongoDB Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 33 / 104
  • 34.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 34/104 Syntax slightly different than MongoDB > db.restaurants. nd({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name": 1,"cuisine": 1, "borough": 1}).limit(2) { "borough" : "Queens", "cuisine" : "French", "name" : "La Baraka Restaurant" } { "borough" : "Queens", "cuisine" : "French", "name" : "Air France Lounge" } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 34 / 104
  • 35.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 35/104 CRUD operations Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 35 / 104
  • 36.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 36/104 CRUD operations for collections Add a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 36 / 104
  • 37.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 37/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 37 / 104
  • 38.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 38/104 CRUD operations for collections Modify a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 38 / 104
  • 39.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 39/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 39 / 104
  • 40.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 40/104 CRUD operations for collections Remove a document Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 40 / 104
  • 41.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 41/104 CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 41 / 104
  • 42.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 42/104 MySQL Document Store Objects Summary Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 42 / 104
  • 43.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 43/104 All you need to know is here: https://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 43 / 104
  • 44.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 44/104 MySQL Connector Python The Python connector for MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 44 / 104
  • 45.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 45/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 45 / 104
  • 46.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 46/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 46 / 104
  • 47.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 47/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 47 / 104
  • 48.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 48/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 48 / 104
  • 49.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 49/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 49 / 104
  • 50.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 50/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 50 / 104
  • 51.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 51/104 NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support All you need to know is here: https://dev.mysql.com/doc/connector-python/en/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 51 / 104
  • 52.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 52/104 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 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 52 / 104
  • 53.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 53/104   MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL DATABASES = { 'default': { 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 53 / 104
  • 54.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 54/104 MySQL Connector/Python 8 - installation easiest, use pip: use a MySQL repository for your distribution (fedora, ubuntu, debian, ...) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 54 / 104
  • 55.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 55/104 MySQL Connector/Python 8 - installation (2) or go to https://dev.mysql.com/downloads/connector/python/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 55 / 104
  • 56.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 56/104 What time is it ? Time to code ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 56 / 104
  • 57.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 57/104 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('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 57 / 104
  • 58.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 58/104 If it was not yet obvious, you can now use MySQL without SQL ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 58 / 104
  • 59.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 59/104 OK we have Document Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 59 / 104
  • 60.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 60/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 60 / 104
  • 61.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 61/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 61 / 104
  • 62.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 62/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 62 / 104
  • 63.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 63/104 NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 63 / 104
  • 64.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 64/104 import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') 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.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''').execute() for restaurant in result.fetch_all(): print "%s - %s - %d " % (restaurant[0], restaurant[1], restaurant[2]) session.close() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 64 / 104
  • 65.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 65/104 Output Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 65 / 104
  • 66.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 66/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 66 / 104
  • 67.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 67/104 And back to JSON - output Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 104
  • 68.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 68/104 some extras... The hidden part of the iceberg Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 68 / 104
  • 69.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 69/104 _id Every document has a unique identifier 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: prefix timestamp when the MySQL Server instance was started auto-increment counter Globally unique IDs Optimized for InnoDB storage Examples 00005cc17b700000000000000003 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 69 / 104
  • 70.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 70/104 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 70 / 104
  • 71.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 71/104 Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 71 / 104
  • 72.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 72/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 72 / 104
  • 73.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 73/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 73 / 104
  • 74.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 74/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 74 / 104
  • 75.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 75/104 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 75 / 104
  • 76.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 76/104 What time is it now ? Time for some more examples ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 104
  • 77.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 77/104 More fun with The Walking Dead Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 104
  • 78.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 78/104 Example: All Episodes of Season 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 104
  • 79.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 79/104 Example: All First Episodes of Each Season Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 79 / 104
  • 80.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 80/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 80 / 104
  • 81.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 81/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 81 / 104
  • 82.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 82/104 Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 104
  • 83.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 83/104 Example: Transaction Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 83 / 104
  • 84.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 84/104 Example: Do more with SQL List the amount of episodes by season: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 84 / 104
  • 85.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 85/104 Example: Do more with SQL (2) Episode statistics for each season: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 85 / 104
  • 86.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 86/104 Example: Do more with SQL (3) Number of days between episodes: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 86 / 104
  • 87.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 87/104 JSON Data validation It's possible to have constraints between collections (or tables): Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 87 / 104
  • 88.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 88/104 JSON Data validation (2) Let's see in SQL how the 2 collections look like: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 88 / 104
  • 89.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 89/104 JSON Data validation (3) Let's add one virtual column in each collection: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 89 / 104
  • 90.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 90/104 JSON Data validation (4) Now we can index the reference and create the constraint: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 90 / 104
  • 91.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 91/104 JSON Data validation (4) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 91 / 104
  • 92.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 92/104 JSON Data validation (5) And now even in pure NoSQL CRUD, we can see the data validation in action: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 92 / 104
  • 93.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 93/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 93 / 104
  • 94.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 94/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 94 / 104
  • 95.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 95/104 JSON Data validation (6) - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 95 / 104
  • 96.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 96/104 what do I gain ? Conclusion Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 96 / 104
  • 97.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 97/104 schemaless flexible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Data integrity ACID Compliant Transactions SQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 97 / 104
  • 98.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 98/104 98 / 104
  • 99.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 99/104 8.0 http://lefred.be/content/top-10-reasons-for- nosql-with-mysql/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 99 / 104
  • 100.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 100/104 8.0 https://www.slideshare.net/lefred.descamps/mysql- shell-the-best-dba-tool https://github.com/lefred/mysql- shell-mydba https://github.com/lefred/mysql- shell-innotop More with MySQL Shell & Python Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 100 / 104
  • 101.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 101/104 MySQL Shell Reporting Framework - 8.0.16 Now it's also possible to create user-defined reports directly in the Shell. They can be written in Python and called the same way on every operating systems. No need for extra libraries ! More Info: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-reporting.html https://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/ https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 101 / 104
  • 102.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 102/104 Credits Thank you Olivier Dasini for some TV Show examples: http://dasini.net/blog/2019/04/02/mysql-json-document-store/ Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 102 / 104
  • 103.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 103/104 103 / 104
  • 104.
    5/2/2019 Python &MySQL 8.0 Document Store file:///home/fred/ownCloud/Presentations/ORACLE/PyconX/Python e MySQL 8.0 Document Store/Python e MySQL 8.0 Document Store.html#49 104/104 Thank you ! Any Questions ? share your 💕 for MySQL on social media using @mysql #MySQL8isGreat #MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 104 / 104