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