Python and MySQL 8.0 Document Store

Python and MySQL 8.0 Document
Store
DEV5966
Frédéric Descamps
Community Manager
MySQL
September 2019
2 / 111

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

about.me/lefred
Who am I ?
4 / 111

Frédéric Descamps
@lefred
MySQL Evangelist
Managing MySQL since 3.23
devops believer
living in Belgium 🇧🇪
h ps://lefred.be
5 / 111

MySQL and JSON
How MySQL works with JSON
6 / 111

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"
}
7 / 111

8.0
MySQL has Native JSON Support
JSON data type since MySQL 5.7
8 / 111

8.0
Stored as binary object
9 / 111

8.0
Support for partial updates in MySQL 8.0
10 / 111

8.0
Support for partial updates in MySQL 8.0
30 JSON functions
11 / 111

8.0
... and support JSON Paths
$.address.coord[*]
$ the root of the document
12 / 111

8.0
$.address.coord[*]
. path leg separator
13 / 111

8.0
$.address.coord[*]
* wildcard:
14 / 111

8.0
$.address.coord[*]
* wildcard:
.* all members in the object
[*] all values in the array
[pre x]**su x path beginning with
pre x and ending with su x
15 / 111

MySQL Document Store
the Solution
16 / 111

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

X Protocol Connectors
18 / 111

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)
19 / 111

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
parallel
importTABLE
data
20 / 111

Migration from MongoDB to MySQL DS
For this example, I will use the well known restaurants collection:
21 / 111

Migration from MongoDB to MySQL DS
For this example, I will use the well known restaurants collection:
22 / 111

23 / 111

24 / 111

Let's query
25 / 111

Let's query
That's too much records to show on this slide...
26 / 111

Let's query
27 / 111

Let's query
Let's add a limit to it
28 / 111

29 / 111

Some more examples
30 / 111

Some more examples
Let's add a selection criteria:
31 / 111

Using IN...
32 / 111

Syntax slightly different than MongoDB
33 / 111

Syntax slightly different than MongoDB
34 / 111

CRUD operations
35 / 111

CRUD operations for collections
Add a document
36 / 111

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()
37 / 111

Modify a document
38 / 111

Modify a document
collection.modify('name = :name')
.bind('name', 'fred')
.set('age', 43)
.sort('name ASC')
.limit(1)
.execute()
collection.modify('name = :name')
.patch({ age: 43, active: false })
.sort('name DESC')
.limit(1)
.execute()
39 / 111

Remove a document
40 / 111

Remove a document
collection.remove('name = :name')
.sort('age ASC')
.limit(1)
.execute()
41 / 111

MySQL Document Store Objects Summary
42 / 111

All you need to know is here:
h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html
43 / 111

MySQL Connector Python
The Python connector for MySQL
44 / 111

NoSQL
SQL

MySQL Connector/Python 8
GA since April 2018
45 / 111

NoSQL
SQL

GA since April 2018
Maintained by Oracle
46 / 111

NoSQL
SQL

GA since April 2018
Dual license
47 / 111

NoSQL
SQL

GA since April 2018
Dual license
Support MySQL Server 5.5, 5.6, 5.7 and 8.0
48 / 111

NoSQL
SQL

GA since April 2018
Dual license
SQL and NoSQL support
49 / 111

NoSQL
SQL

GA since April 2018
Dual license
SQL and NoSQL support
Table and JSON Document support
50 / 111

MySQL Connector/Python 8 - 3 APIs
Choice of Three APIs
API Python Module Comment
PEP249 Python Database API mysql.connector The traditional API
C Extension API _mysql_connector Similar to the MySQL C API
MySQL X DevAPI mysqlx New in 8.0, both SQL and NoSQL

All you need to know is here: h ps://dev.mysql.com/doc/connector-python/en/
51 / 111

DATABASES = {
'default': {
'NAME': 'user_data',
'ENGINE': 'mysql.connector.django',
'USER': 'mysql_user',
'PASSWORD': 'password',
'OPTIONS': {
'autocommit': True,
},
}
}
MySQL Connect/Python and Django
MySQL Connector/Python includes also a mysql.connector.django module that provides a
Django back end for MySQL
52 / 111

MySQL Connector/Python 8 - installation
easiest, use pip:
use a MySQL repository for your distribution (fedora, ubuntu, debian, ...)
53 / 111

MySQL Connector/Python 8 - installation
or go to h ps://dev.mysql.com/downloads/connector/python/
54 / 111

What time is it ?
Time to code !
55 / 111

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()
56 / 111

If it was not yet obvious, you can now use
MySQL without SQL !
57 / 111

OK we have Document Store, CRUD and ACID
but what makes MySQL Document Store
unique ?
58 / 111

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
59 / 111

NoSQL as SQL - aggregation
60 / 111

61 / 111

62 / 111

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()
63 / 111

Output
64 / 111

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]
65 / 111

And back to JSON - output
66 / 111

some extras...
The hidden part of the iceberg
67 / 111

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

Document Store Full ACID !
It relies on the proven MySQL InnoDB's strength & robustness:
69 / 111

innodb_ ush_log_at_trx_commit = 1
70 / 111

innodb_doublewrite = ON
71 / 111

sync_binlog = 1
72 / 111

sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
73 / 111

sync_binlog = 1
transaction_isolation = REPEATABLE-READ|READ-COMMITTED|...
We do care about your data !
74 / 111

What time is it now ?
Time for some more
examples !
75 / 111

More fun with The Walking Dead
76 / 111

Example: All Episodes of Season 1
77 / 111

Example: All First Episodes of Each Season
78 / 111

Example: Speed Up Episode's Name Lookup
79 / 111

80 / 111

81 / 111

Example: Transaction
82 / 111

Example: Do more with SQL
List the amount of episodes by season:
83 / 111

Example: Do more with SQL (2)
Episode statistics for each season:
84 / 111

Example: Do more with SQL (3)
Number of days between episodes:
85 / 111

JSON Data validation
It's possible to have constraints between collections (or tables):
86 / 111

JSON Data validation (2)
Let's see in SQL how the 2 collections look like:
87 / 111

Let's add one virtual column in each collection:
88 / 111

Now we can index the reference and create the constraint:
89 / 111

90 / 111

And now even in pure NoSQL CRUD, we can see the data validation in action:
91 / 111

JSON Schema validation - NEW 8.0.16
Since MySQL 8.0.16, CHECK constraints are now supported !
92 / 111

93 / 111

94 / 111

JSON Schema validation (2) - NEW 8.0.17
New JSON_SCHEMA_VALID function:
95 / 111

New JSON_SCHEMA_VALID function:
96 / 111

Knowing why thanks to the new JSON_SCHEMA_VALIDATION_REPORT function:
97 / 111

And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT:
98 / 111

And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT:
99 / 111

what do I gain ?
Conclusion
100 / 111

schemaless
exible data structure
easy to start (CRUD)
Conclusion
This is the best of the two worlds in one product !
Data integrity
ACID Compliant
Transactions
SQL
101 / 111

8.0
http://lefred.be/content/top-10-reasons-for-
nosql-with-mysql/
102 / 111

8.0
h ps://www.slideshare.net/lefred.descamps/mysql-
shell-the-best-dba-tool
h ps://github.com/lefred/mysql-shell-
mydba
h ps://github.com/lefred/mysql-shell-
innotop
More with MySQL Shell & Python
103 / 111

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

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

Credits
Thank you Olivier Dasini for some TV Show examples:
h p://dasini.net/blog/2019/04/02/mysql-json-document-store/
Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell
106 / 111

Thank you !
108 / 111

Upgrade to MySQL 8.0
It's time to upgrade to MySQL 8.0, the
fastest MySQL adoption release ever !
110 / 111

Session Survey
Help us make the content even be er.
Please complete the session survey in
the Mobile App.
111 / 111

Python and MySQL 8.0 Document Store

More Related Content

What's hot

Similar to Python and MySQL 8.0 Document Store

More from Frederic Descamps

Recently uploaded

Python and MySQL 8.0 Document Store