1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
Collections
===========
Documents of the same type are grouped together and stored in the database as collections. The X DevAPI uses Collection objects to store and retrieve documents.
Creating collections
--------------------
In order to create a new collection call the :func:`mysqlx.Schema.create_collection()` function from a :class:`mysqlx.Schema` object. It returns a Collection object that can be used right away, for example to insert documents into the collection.
Optionally, the argument ``reuse_existing`` can be set to ``True`` to prevent an error being generated if a collection with the same name already exists.
.. code-block:: python
import mysqlx
# Connect to server on localhost
session = mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': 'mike',
'password': 's3cr3t!'
})
schema = session.get_schema('test')
# Create 'my_collection' in schema
schema.create_collection('my_collection', reuse_existing=True)
Schema validation
~~~~~~~~~~~~~~~~~
Optionally, the argument ``validation`` can be set to create a server-side document validation schema. This argument should be a :class:`dict`, which includes a ``schema`` key matching a valid `JSON schema <https://json-schema.org/>`_ definition. You should also include the ``level`` key to effectively enable (`STRICT`) or disable (`OFF`) it.
.. code-block:: python
validation = {
"level": "STRICT",
"schema": {
"id": "http://json-schema.org/geo",
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "Longitude and Latitude Values",
"description": "A geographical coordinate",
"required": ["latitude", "longitude"],
"type": "object",
"properties": {
"latitude": {
"type": "number",
"minimum": -90,
"maximum": 90
},
"longitude": {
"type": "number",
"minimum": -180,
"maximum": 180
}
},
}
}
# Create 'my_collection' in schema with a schema validation
schema.create_collection('my_collection', validation=validation)
When trying to insert a document that violates the schema definition for the collection, an error is thrown.
Modifying collections
---------------------
To enable a JSON schema validation on an existing collection (or to update it if already exists), you can use :func:`mysqlx.Schema.modify_collection()` function.
.. code-block:: python
# Using the same 'validation' dictionary used above, we can
# modify 'my_collection' to include a schema validation
schema.modify_collection('my_collection', validation=validation)
Using Collection patch (:func:`mysqlx.ModifyStatement.patch()`)
---------------------------------------------------------------
First we need to get a session and a schema.
.. code-block:: python
import mysqlx
# Connect to server on localhost
session = mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': 'mike',
'password': 's3cr3t!'
})
schema = session.get_schema('test')
Next step is create a sample collection and add some sample data.
.. code-block:: python
# Create 'collection_GOT' in schema
schema.create_collection('collection_GOT')
# Get 'collection_GOT' from schema
collection = schema.get_collection('collection_GOT')
collection.add(
{"name": "Bran", "family_name": "Stark", "age": 18,
"parents": ["Eddard Stark", "Catelyn Stark"]},
{"name": "Sansa", "family_name": "Stark", "age": 21,
"parents": ["Eddard Stark", "Catelyn Stark"]},
{"name": "Arya", "family_name": "Stark", "age": 20,
"parents": ["Eddard Stark", "Catelyn Stark"]},
{"name": "Jon", "family_name": "Snow", "age": 30},
{"name": "Daenerys", "family_name": "Targaryen", "age": 30},
{"name": "Margaery", "family_name": "Tyrell", "age": 35},
{"name": "Cersei", "family_name": "Lannister", "age": 44,
"parents": ["Tywin Lannister, Joanna Lannister"]},
{"name": "Tyrion", "family_name": "Lannister", "age": 48,
"parents": ["Tywin Lannister, Joanna Lannister"]},
).execute()
This example shows how to add a new field to a matching documents in a
collection, in this case the new field name will be ``_is`` with the value
of ``young`` for those documents with ``age`` field equal or smaller than 21 and
the value ``old`` for documents with ``age`` field value greater than 21.
.. code-block:: python
collection.modify("age <= 21").patch(
'{"_is": "young"}').execute()
collection.modify("age > 21").patch(
'{"_is": "old"}').execute()
for doc in mys.collection.find().execute().fetch_all():
if doc.age <= 21:
assert(doc._is == "young")
else:
assert(doc._is == "old")
This example shows how to add a new field with an array value.
The code will add the field "parents" with the value of
``["Mace Tyrell", "Alerie Tyrell"]``
to documents whose ``family_name`` field has value ``Tyrell``.
.. code-block:: python
collection.modify('family_name == "Tyrell"').patch(
{"parents": ["Mace Tyrell", "Alerie Tyrell"]}).execute()
doc = collection.find("name = 'Margaery'").execute().fetch_all()[0]
assert(doc.parents == ["Mace Tyrell", "Alerie Tyrell"])
This example shows how to add a new field ``dragons`` with a JSON document as
value.
.. code-block:: python
collection.modify('name == "Daenerys"').patch('''
{"dragons":{"drogon": "dark grayish with red markings",
"Rhaegal": "green with bronze markings",
"Viserion": "creamy white, with gold markings",
"count": 3}}
''').execute()
doc = collection.find("name = 'Daenerys'").execute().fetch_all()[0]
assert(doc.dragons == {"count": 3,
"drogon": "dark grayish with red markings",
"Rhaegal": "green with bronze markings",
"Viserion": "creamy white, with gold markings"})
This example uses the previews one to show how to remove of the nested field
``Viserion`` on ``dragons`` field and at the same time how to update the value of
the ``count`` field with a new value based in the current one.
.. note:: In the :func:`mysqlx.ModifyStatement.patch()` all strings are considered literals,
for expressions the usage of the :func:`mysqlx.expr()` is required.
.. code-block:: python
collection.modify('name == "Daenerys"').patch(mysqlx.expr('''
JSON_OBJECT("dragons", JSON_OBJECT("count", $.dragons.count -1,
"Viserion", Null))
''')).execute()
doc = mys.collection.find("name = 'Daenerys'").execute().fetch_all()[0]
assert(doc.dragons == {'count': 2,
'Rhaegal': 'green with bronze markings',
|
