mysql-connector-python3 - Man Page

This is the recommended way to install Connector/Python.

Make sure you have a recent pip version installed on your system. If your system already has pip installed, you might need to update it. Or you can use the standalone pip installer.

shell> pip install mysql-connector-python

To use the MSI Installer, launch it and follow the prompts in the screens it presents to install Connector/Python in the location of your choosing.

You must have the MySQL Yum repository on your system's repository list. To make sure that your Yum repository is up-to-date, use this command:

shell> sudo yum update mysql-community-release

Then install Connector/Python as follows:

shell> sudo yum install mysql-connector-python

To install a Connector/Python RPM package (denoted here as PACKAGE.rpm), use this command:

shell> rpm -i PACKAGE.rpm

To install a Connector/Python Debian package (denoted here as PACKAGE.deb), use this command:

shell> dpkg -i PACKAGE.deb

Download the .dmg file and install Connector/Python by opening it and double clicking the resulting .pkg file.

As of Connector/Python 2.2.3, source distributions include a C++ Extension, that interfaces with a MySQL server with the X Plugin enabled using Protobuf as data interchange format.

To build Connector/Python C++ Extension for Protobuf, you must satisfy the following prerequisites:

• A C/C++ compiler, such as gcc
• Protobuf C++ (version >= 3.6.1)
• Python development files
• MySQL Connector/C or MySQL Server installed, including development files to compile the optional C Extension that interfaces with the MySQL C client library

To install Connector/Python from a tar archive, download the latest version (denoted here as <version>), and execute these commands:

shell> tar xzf mysql-connector-python-<version>.tar.gz
shell> cd mysql-connector-python-<version>.tar.gz
shell> python setup.py install --with-protobuf-include-dir=<protobuf-include-dir> --with-protobuf-lib-dir=<protobuf-lib-dir> --with-protoc=<protoc-binary>

To include the C Extension that interfaces with the MySQL C client library, add the --with-mysql-capi option:

shell> python setup.py install --with-protobuf-include-dir=<protobuf-include-dir> --with-protobuf-lib-dir=<protobuf-lib-dir> --with-protoc=<protoc-binary> --with-mysql-capi=<mysql-capi>

The argument to --with-mysql-capi is the path to the installation directory of either MySQL Connector/C or MySQL Server, or the path to the mysql_config command.

To see all options and commands supported by setup.py, use this command:

shell> python setup.py --help

A simple python script using this library follows:

import mysqlx

# Connect to server on localhost
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
})

schema = session.get_schema('test')

# Use the collection 'my_collection'
collection = schema.get_collection('my_collection')

# Specify which document to find with Collection.find()
result = collection.find('name like :param').bind('param', 'S%').limit(1).execute()

# Print document
docs = result.fetch_all()
print('Name: {0}'.format(docs[0]['name']))

session.close()

After importing the mysqlx module, we have access to the mysqlx.get_session() function which takes a dictionary object or a connection string with the connection settings. 33060 is the port which the X DevAPI Protocol uses by default. This function returns a mysqlx.Session object on successful connection to a MySQL server, which enables schema management operations, as well as access to the full SQL language if needed.

session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
})

SSL is activated by default. The mysqlx.get_session() will throw an error if the server doesn't support SSL. To disable SSL, ssl-mode must be manually set to disabled. The mysqlx.SSLMode contains the following SSL Modes: REQUIRED, DISABLED, VERIFY_CA, VERIFY_IDENTITY. Strings ('required', 'disabled', 'verify_ca', 'verify_identity') can also be used to specify the ssl-mode option. It is case-insensitive.

SSL is not used if the mode of connection is a Unix Socket since it is already considered secure.

If ssl-ca option is not set, the following SSL Modes are allowed:

• REQUIRED is set by default.
• DISABLED connects to the MySQL Server without SSL.

If ssl-ca option is set, only the following SSL Modes are allowed:

• VERIFY_CA validates the server Certificate with the CA Certificate.

• VERIFY_IDENTITY verifies the common name on the server Certificate and the hostname.

  session = mysqlx.get_session('mysqlx://root:@localhost:33060?ssl-mode=verify_ca&ssl-ca=(/path/to/ca.cert)')
  session = mysqlx.get_session({
      'host': 'localhost',
      'port': 33060,
      'user': 'root',
      'password': '',
      'ssl-mode': mysqlx.SSLMode.VERIFY_CA,
      'ssl-ca': '/path/to/ca.cert'
  })

The connection settings accepts a connect timeout option connect-timeout, which should be a non-negative integer that defines a time frame in milliseconds. The timeout will assume a default value of 10000 ms (10s) if a value is not provided. And can be disabled if it's value is set to 0, and in that case, the client will wait until the underlying socket (platform-dependent) times-out.

session = mysqlx.get_session('mysqlx://root:@localhost:33060?connect-timeout=5000')

Connector/Python has a C extension for Protobuf message serialization, this C extension is enabled by default if available. It can be disabled by setting the use-pure option to True.

session = mysqlx.get_session('mysqlx://root:@localhost:33060?use-pure=true')
session = mysqlx.get_session(host='localhost', port=33060, user='root', password='', use_pure=True)
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'root',
    'password': '',
    'use-pure': True
})

NOTE:

The urllib.parse.quote function should be used to quote special characters for user and password when using a connection string in the mysqlx.get_session() function.

from urllib.parse import quote
session = mysqlx.get_session('mysqlx://root:{0}@localhost:33060?use-pure=true'
                             ''.format(quote('pass?!#%@/')))

The mysqlx.Session.get_schema() method returns a mysqlx.Schema object. We can use this mysqlx.Schema object to access collections and tables. X DevAPI's ability to chain all object constructions, enables you to get to the schema object in one line. For example:

schema = mysqlx.get_session().get_schema('test')

This object chain is equivalent to the following, with the difference that the intermediate step is omitted:

session = mysqlx.get_session()
schema = session.get_schema('test')

The connection settings accepts a default schema option schema, which should be a valid name for a preexisting schema in the server.

session = mysqlx.get_session('mysqlx://root:@localhost:33060/my_schema')
# or
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'root',
    'password': '',
    'schema': 'my_schema'
})

NOTE:

The default schema provided must exists in the server otherwise it will raise an error at connection time.

This way the session will use the given schema as the default schema, which can be retrieved by mysqlx.Session.get_default_schema() and also allows to run SQL statements without specifying the schema name:

session = mysqlx.get_session('mysqlx://root:@localhost:33060/my_schema')
my_schema = session.get_default_schema()
assert my_test_schema.get_name() == 'my_schema'
session.sql('CREATE TABLE Pets(name VARCHAR(20))').execute()
# instead of 'CREATE TABLE my_schema.Pets(name VARCHAR(20))'
res = session.sql('SELECT * FROM Pets').execute().fetch_all()
# instead of 'SELECT * FROM my_schema.Pets'

In the following example, the mysqlx.get_session() function is used to open a session. We then get the reference to test schema and create a collection using the mysqlx.Schema.create_collection() method of the mysqlx.Schema object.

# Connecting to MySQL and working with a Session
import mysqlx

# Connect to a dedicated MySQL server
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')

# Get 'my_collection' from schema
collection = schema.get_collection('my_collection')

The next step would be to run Crud operations on a collection which belongs to a particular schema. Once we have the mysqlx.Schema object, we can use mysqlx.Schema.get_collection() to obtain a reference to the collection on which we can perform operations like add() or remove().

my_coll = db.get_collection('my_collection')

# Add a document to 'my_collection'
my_coll.add({'_id': '2', 'name': 'Sakila', 'age': 15}).execute()

# You can also add multiple documents at once
my_coll.add({'_id': '2', 'name': 'Sakila', 'age': 15},
            {'_id': '3', 'name': 'Jack', 'age': 15},
            {'_id': '4', 'name': 'Clare', 'age': 37}).execute()

# Remove the document with '_id' = '1'
my_coll.remove('_id = 1').execute()

assert(3 == my_coll.count())

Parameter binding is also available as a chained method to each of the Crud operations. This can be accomplished by using a placeholder string with a : as a prefix and binding it to the placeholder using the bind() method.

my_coll = db.get_collection('my_collection')
my_coll.remove('name = :data').bind('data', 'Sakila').execute()

If you are using a DNS server with service discovery utility that supports mapping SRV records, you can use the mysqlx+srv scheme or dns-srv connection option and Connector/Python will automatically resolve the available server addresses described by those SRV records.

NOTE:

session = mysqlx.get_session('mysqlx://root:@foo.abc.com')
# or
session = mysqlx.get_session({
    'host': 'foo.abc.com',
    'user': 'root',
    'password': '',
    'dns-srv': True
})

For instance, given the following SRV records by a DNS server at the foo.abc.com endpoint, the servers would be in the following priority: foo2.abc.com, foo1.abc.com, foo3.abc.com, foo4.abc.com.

Record                    TTL   Class    Priority Weight Port  Target
_mysqlx._tcp.foo.abc.com. 86400 IN SRV   0        5      33060 foo1.abc.com
_mysqlx._tcp.foo.abc.com. 86400 IN SRV   0        10     33060 foo2.abc.com
_mysqlx._tcp.foo.abc.com. 86400 IN SRV   10       5      33060 foo3.abc.com
_mysqlx._tcp.foo.abc.com. 86400 IN SRV   20       5      33060 foo4.abc.com

The desired TLS versions to use during the connection van be specified while getting the session with the use of tls-versions option and in addition the TLS ciphers can also be specified with the tls-ciphersuites option.

session = mysqlx.get_session('mysqlx://root:@127.0.0.1:33060?tls-versions=[TLSv1.1,TLSv1.2]&tls-ciphersuites=[DHE-RSA-AES256-SHA]&ssl-mode=required')
# or
session = mysqlx.get_session({
    'host': '127.0.0.1',
    'user': 'root',
    'password': '',
    'tls-versions"': ["TLSv1.1", "TLSv1.2"],
    'tls-ciphersuites': ["DHE-RSA-AES256-SHA"],
})
res = session.sql("SHOW STATUS LIKE 'Mysqlx_ssl_version'").execute().fetch_all()
print("Mysqlx_ssl_version: {}".format(res[0].get_string('Value')))
res = session.sql("SHOW STATUS LIKE 'Mysqlx_ssl_cipher'").execute().fetch_all()
print("Mysqlx_ssl_cipher: {}".format(res[0].get_string('Value')))
session.close()

From the given list of TLS versions, the highest supported version will be selected for the connection, given as result:

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.

In order to create a new collection call the mysqlx.Schema.create_collection() function from a 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.

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)

Optionally, the argument validation can be set to create a server-side document validation schema. This argument should be a dict, which includes a schema key matching a valid JSON schema definition. You should also include the level key to effectively enable (STRICT) or disable (OFF) it.

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.

To enable a JSON schema validation on an existing collection (or to update it if already exists), you can use mysqlx.Schema.modify_collection() function.

# Using the same 'validation' dictionary used above, we can
# modify 'my_collection' to include a schema validation
schema.modify_collection('my_collection', validation=validation)

First we need to get a session and a schema.

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.

# 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.

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.

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.

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:

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',

Connection Routers is a technique used for connecting to one of multiple hosts using connection failover, which attempts to connect to the next endpoint if the current endpoint is not available before raising an error. For this technique, define multiple hosts by specifying a URI-like string containing multiple hosts, ports, and an optional priority or using the routers option when invoking mysqlx.get_client().

This technique enables the connector to perform automatic connection failover selection when an endpoint are not available. When multiple endpoints are available, the chosen server used for the session depends on the priority option. If a priority is set, it must be defined for each endpoint. The available endpoint with the highest priority is prioritized first. If not specified, a randomly available endpoint is used.

Here's an example of how to specify multiple hosts URI-like string and without priority when calling the mysqlx.get_client(). The URI-like connection string is formatted as:

import mysqlx

connection_str = 'mysqlx://root:@[(address=unreachable_host),(address=127.0.0.1:33060)]?connect-timeout=2000'
options_string = '{}'  # An empty document

client = mysqlx.get_client(connection_str, options_string)
session = client.get_session()

# (...)

session.close()
client.close()

The multiple hosts can also be given as a list with the routers in the connection settings:

import mysqlx

routers = [
    {"host": "unreachable_host"},  # default port is 33060
    {"host": "127.0.0.1", "port": 33060},
]

connection_dict = {
    'routers': routers,
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
    'connect_timeout': 2000,
}
options_dict = {}  # empty dict object

client = mysqlx.get_client(connection_dict, options_dict)
session = client.get_session()

# (...)

session.close()
client.close()

The above examples have two hosts but many more hosts and ports can be defined, and it's important to understand that the supplied MySQL user and password supplied (in either the URI-like string or in the user and password options) applies to all of the possible endpoints. Therefore the same MySQL account must exist on each of the endpoints.

NOTE:

Because of the failover, a connection attempt for establishing a connection on all the given hosts will occur before an error is raised, so using the connect_timeout option is recommended when a large number of hosts could be down. The order for the connection attempts occur randomly unless the priority option is defined.

NOTE:

The connect_timeout option's value must be a positive integer.

Specifying multiple hosts with a priority in the URI-like string is formatted as such:

import mysqlx

connection_str = 'mysqlx://root:@[(address=unreachable_host, priority=100),(address=127.0.0.1:33060, priority=90)]?connect-timeout=2000'
options_string = '{}'  # An empty dictionary object

client = mysqlx.get_client(connection_str, options_string)
session = client.get_session()

# (...)

session.close()
client.close()

Specifying multiple hosts with a priority in the connection settings is formatted as such:

import mysqlx

routers = [{"host": "unreachable_host", "priority": 100},  # default port is 33060
           {"host": "127.0.0.1", "port": 33060, "priority": 90}
]

connection_dict = {
    'routers': routers,
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!',
    'connect_timeout': 2000
}
options_dict = {}

client = mysqlx.get_client(connection_dict, options_dict)
session = client.get_session()

# (...)

session.close()
client.close()

NOTE:

Valid values for the priority option are values 1 to 100`, where 100 is the highest priority value. Priority determines the connection order with highest priority value being first. If priority is given for one host, then a priority value must be given for all the hosts.

The Routers technique can be combined with the pooling technique by passing a pooling configuration for mysqlx.Client. Set the pooling options by passing a dict or a JSON document string in the second parameter.

The following example uses the same MySQL as in previous examples, but with different hostnames to emulate two other servers, and the options_dict is a dictionary with the settings for each pool. Notice that with max_size option set to 5, we can get up to 10 sessions because a connection pool is created for each server with 5 connections.

import mysqlx

routers = [{"host": "localhost", "priority": 100},  # default port is 33060
           {"host": "127.0.0.1", "port": 33060, "priority": 90}
]

connection_dict = {
    'routers': routers,
    'port': 33060,
    'user': 'root',
    'password': '',
    'connect_timeout':2000
}

options_dict = {'pooling':{'max_size': 5, 'queue_timeout': 1000}}

client = mysqlx.get_client(connection_dict, options_dict)

# We can get 5 sessions from each pool.

for n in range(5):
    print(f"session: {n}")
    session = client.get_session()
    res = session.sql("select connection_id()").execute().fetch_all()
    for row in res:
        print(f"connection id: {row[0]}")

for n in range(5):
    print(f"session: {n}")
    session = client.get_session()
    res = session.sql("select connection_id()").execute().fetch_all()
    for row in res:
        print(f"connection id: {row[0]}")

The output:

session: 0
connection id: 603
session: 1
connection id: 604
session: 2
connection id: 605
session: 3
connection id: 606
session: 4
connection id: 607
session: 0
connection id: 608
session: 1
connection id: 609
session: 2
connection id: 610
session: 3
connection id: 611
session: 4
connection id: 612

The following example shows using Multi-host and failover while using a pool. In this example, the “unreachable_host” has higher priority than the second host "127.0.0.1", so the connection is attempted to "unreachable_host" first but it will fail. However, this does not raise an error and the connection attempt to the host "127.0.0.1" that's available will succeed. However, an error is raised when the pool is maxed out.

import mysqlx

routers = [{"host": "unreachable_host", "priority": 100},
           {"host": "127.0.0.1", "port": 33060, "priority": 90}
]

connection_dict = {
    'routers': routers,
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!',
    'connect_timeout': 2000
}

options_dict = {'pooling':{'max_size': 5, 'queue_timeout': 1000}}

client = mysqlx.get_client(connection_dict, options_dict)

for n in range(5):
     print(f"session: {n}")
     session = client.get_session()
     res = session.sql("select connection_id()").execute().fetch_all()
     for row in res:
         print(f"connection id: {row[0]}")

# Since the "unreachable_host" is unavailable and the max_size option for
# the pools is set to 5, we can only get 5 sessions prior to get an error.
# By requiring another session a mysqlx.errors.PoolError error is raised.
client.get_session()  # This line raises an PoolError

The code above will give an output similar to the following:

session: 0
connection id: 577
session: 1
connection id: 578
session: 2
connection id: 579
session: 3
connection id: 580
session: 4
connection id: 581

mysqlx.errors.PoolError: Unable to connect to any of the target hosts: [
    pool: 127.0.0.1_33060_... error: pool max size has been reached
    pool: unreachable_host_33060_... error: Cannot connect to host: [Errno 11001] getaddrinfo failed
]

Connection pooling is a technique of creating and managing a pool of connections that are ready for use, which greatly increase the performance of your applications by reducing the connection creation time.

The way of using connection pooling in Connector/Python with the X Protocol, is by calling the mysqlx.get_client() function as follows:

import mysqlx

connection_str = 'mysqlx://mike:s3cr3t!@localhost:33060'
options_string = '{}'  # An empty document

client = mysqlx.get_client(connection_str, options_string)
session = client.get_session()

# (...)

session.close()
client.close()

The connection settings and options can also be a dict:

import mysqlx

connection_dict = {
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
}
options_dict = {}

client = mysqlx.get_client(connection_dict, options_dict)
session = client.get_session()

# (...)

session.close()
client.close()

All sessions created by mysqlx.Client.get_session() have a pooled connection, which after being closed by mysqlx.Session.close() returns to the pool of connections, so it can be reused.

Until now we didn't supply any configuration for mysqlx.Client. We can set the pooling options by passing a dict or a JSON document string in the second parameter.

The available options for the mysqlx.Client are:

options = {
    'pooling': {
        'enabled': (bool), # [True | False], True by default
        'max_size': (int), # Maximum connections per pool
        "max_idle_time": (int), # milliseconds that a connection will remain active
                                # while not in use. By default 0, means infinite.
        "queue_timeout": (int), # milliseconds a request will wait for a connection
                                # to become available. By default 0, means infinite.
    }
}

To disable pooling in the client we can set the enabled option to False:

client = mysqlx.get_client(connection_str, {'pooling':{'enabled': False}})

To define the pool maximum size we can set the max_size in the pooling options. In the following example 'max_size': 5 sets 5 as the maximum number of connections allowed in the pool.

connection_dict = {
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
}
options_dict = {'pooling':{'max_size': 5, 'queue_timeout': 1000}}

client = mysqlx.get_client(connection_dict, options_dict)

for _ in range(5):
    client.get_session()

# This will raise a pool error:
# mysqlx.errors.PoolError: pool max size has been reached
client.get_session()

The queue_timeout sets the maximum number of milliseconds a request will wait for a connection to become available. The default value is 0 (zero) and means infinite.

The following example shows the usage of threads that will have to wait for a session to become available:

import mysqlx
import time
import random

from threading import Thread

connection_dict = {
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
}

options_dict = {'pooling':{'max_size': 6, 'queue_timeout':5000}}

schema_name = 'test'
collection_name = 'collection_test04'

def job(client, worker_number):
    """This method keeps the tasks for a thread.

       Args:
           client (Client): to get the sessions to interact with the server.
           worker_number (int): the id number for the worker.
    """
    rand = random.Random()
    worker_name = "Worker_{}".format(worker_number)
    print("starting Worker: {} \n".format(worker_name))

    # Take a nap before do the job, (gets a chance to other thread to start)
    time.sleep(rand.randint(0,9)/10)

    # Get a session from client
    session1 = client.get_session()

    # Get a schema to work on
    schema = session1.get_schema(schema_name)

    # Get the collection to put some documents in
    collection = schema.get_collection(collection_name)

    # Add 10 documents to the collection
    for _ in range(10):
        collection.add({'name': worker_name}).execute()

    # close session
    session1.close()
    print("Worker: {} finish\n".format(worker_name))

def call_workers(client, job_thread, workers):
    """Create threads and start them.

       Args:
           client (Client): to get the sessions.
           job_thread (method): the method to run by each thread.
           workers (int): the number of threads to create.
    """
    workers_list = []
    for n in range(workers):
        workers_list.append(Thread(target=job_thread, args=[client, n]))
    for worker in workers_list:
        worker.start()

# Get a client to manage the sessions
client = mysqlx.get_client(connection_dict, options_dict)

# Get a session to create an schema and a collection
session1 = client.get_session()

schema = session1.create_schema(schema_name)
collection = schema.create_collection(collection_name)

# Close the session to have another free connection
session1.close()

# Invoke call_workers with the client, the method to run by the thread and
# the number of threads, on this example 18 workers
call_workers(client, job, 18)

# Give some time for the workers to do the job
time.sleep(10)

session1 = client.get_session()
schema = session1.get_schema(schema_name)

collection = schema.get_collection(collection_name)

print(collection.find().execute().fetch_all())

The output of the last print will look like the following:

[{'_id': '00005b770c7f0000000000000389', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038a', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038b', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038c', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038d', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038e', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f000000000000038f', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f0000000000000390', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f0000000000000391', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f0000000000000392', 'name': 'Worker_2'}, \
 {'_id': '00005b770c7f0000000000000393', 'name': 'Worker_1'}, \
 {'_id': '00005b770c7f0000000000000394', 'name': 'Worker_4'}, \
 {'_id': '00005b770c7f0000000000000395', 'name': 'Worker_1'}, \
 {'_id': '00005b770c7f0000000000000396', 'name': 'Worker_4'}, \
 {'_id': '00005b770c7f0000000000000397', 'name': 'Worker_7'}, \
 {'_id': '00005b770c7f0000000000000398', 'name': 'Worker_1'}, \
 {'_id': '00005b770c7f0000000000000399', 'name': 'Worker_4'}, \
 {'_id': '00005b770c7f000000000000039a', 'name': 'Worker_7'}, \
 {'_id': '00005b770c7f000000000000039b', 'name': 'Worker_1'}, \
 {'_id': '00005b770c7f000000000000039c', 'name': 'Worker_4'}, \
 {'_id': '00005b770c7f000000000000039d', 'name': 'Worker_7'}, \
 {'_id': '00005b770c7f000000000000039e', 'name': 'Worker_1'}, \
 {'_id': '00005b770c7f000000000000039f', 'name': 'Worker_8'}, \
 {'_id': '00005b770c7f00000000000003a0', 'name': 'Worker_4'}, \
 {'_id': '00005b770c7f00000000000003a1', 'name': 'Worker_7'}, \
 ... \
 {'_id': '00005b770c7f000000000000043c', 'name': 'Worker_9'}]

The 18 workers took random turns to add their documents to the collection, sharing only 6 active connections given by 'max_size': 6 in the options_dict given to the client instance at the time was created on mysqlx.get_client(connection_dict, options_dict)().

The SAVEPOINT statement sets a named transaction allowing parts of a transaction to be rolled back before COMMIT.

Assuming the existence of test_schema.test_collection collection.

[{
    "_id": 1,
    "name": "Fred",
    "age": 21
}]

Get the collection object.

session = mysqlx.get_session("root:@localhost:33060")
schema = session.get_schema("test_schema")
collection = schema.get_collection("test_collection")

A savepoint name can be provided to create a transaction savepoint, which can later be used to rollback.

A mysqlx.OperationalError will be raised if the savepoint name is an invalid string or if a nonexistent savepoint is being used in mysqlx.Session.rollback_to().

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
session.set_savepoint("sp")

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Rollback to a savepoint
session.rollback_to("sp")

assert(2 == collection.count())

# Commit all operations
session.commit()

If a savepoint name is not provided, mysqlx.Session.release_savepoint() will return a generated savepoint name.

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
savepoint = session.set_savepoint()

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Rollback to a savepoint
session.rollback_to(savepoint)

assert(2 == collection.count())

# Commit all operations
session.commit()

A mysqlx.OperationalError will be raised if a nonexistent savepoint is being used in mysqlx.Session.release_savepoint().

# Start transaction
session.start_transaction()

collection.add({"name": "Wilma", "age": 33}).execute()
assert(2 == collection.count())

# Create a savepoint
session.set_savepoint("sp")

collection.add({"name": "Barney", "age": 42}).execute()
assert(3 == collection.count())

# Release a savepoint
session.release_savepoint("sp")

assert(3 == collection.count())

# Commit all operations
session.commit()

Collection indexes can be created using one or more fields. The method used to create these indexes is mysqlx.Collection.create_index() and the following sections describes the required arguments for the function and some examples of use.

To use the mysqlx.Collection.create_index() we need to specify the name of the index to be created and the members to be part of the index, in addition for each member we need to specify the type of data that holds the field in the document and if it is required or not. Fields marked as required must appear on each document in the collection.

{"fields": [{"field": member_path, # required str
             "type": member_type, # required str, must be a valid type
             "required": member_required, # optional, True or (default) False
             "collation": collation, # optional str only for TEXT field type
             "options": options, # optional (int) only for GEOJSON field type
             "srid": srid}, # optional (int) only for GEOJSON field type
             # {... more members,
             #      repeated as many times
             #      as needed}
             ],
 "type": type} # optional, SPATIAL or (default) INDEX

The valid types for the type field are:

• INT [UNSIGNED]
• TINYINT [UNSIGNED]
• SMALLINT [UNSIGNED]
• MEDIUMINT [UNSIGNED]
• INTEGER [UNSIGNED]
• BIGINT [UNSIGNED]
• REAL [UNSIGNED]
• FLOAT [UNSIGNED]
• DOUBLE [UNSIGNED]
• DECIMAL [UNSIGNED]
• NUMERIC [UNSIGNED]
• DATE
• TIME
• TIMESTAMP
• DATETIME
• TEXT[(length)]
• GEOJSON (extra options: options, srid)

Note: The use of type GEOJSON, requires the index type to be set to SPATIAL.

First we need to get a session and a schema.

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.

# Create 'collection_GOT' in schema
schema.create_collection('collection_GOT')

# Get 'collection_GOT' from schema
collection = schema.get_collection('collection_GOT')

The following example shows how to create a simple index with name index_age that will use a field age from the document which will hold integer values.

collection.create_index("index_age", {"fields": [{"field": "age",
                                                  "type": "INT"}],
                                      "type":"INDEX"})

The following example shows how to create a multi field index with name index_name that will use the fields family_name and name from the document that will hold small texts. This time the required member has been set to True, which means these fields are required for all the documents in this collection.

collection.create_index("index_name", {"fields": [{"field": "family_name",
                                                   "type": "TEXT(12)",
                                                   "required": True}],
                                       "fields": [{"field": "name",
                                                   "type": "TEXT(12)",
                                                   "required": True}],
                                       "type":"INDEX"})

The following example shows how to create a multi field index with name geojson_name, which will use fields with GEOJSON data, so for this will require the index type to be set to SPATIAL, that will use the fields $.geoField, $.intField, $.floatField and $.dateField. Each field hold the data that compounds the name of the file. Note that by setting SPATIAL to the index type we will require to set for each of these members required to True, which means these fields are required for all the documents in this collection.

collection.create_index("index_age",
                        {"fields": [{"field": "$.geoField",
                                     "type": "GEOJSON",
                                     "required": False, "options": 2,
                                     "srid": 4326},
                                    {"field": "$.intField", "type": "INT",
                                     "required": True},
                                    {"field": "$.floatField",
                                     "type": "FLOAT",
                                     "required": True},
                                    {"field": "$.dateField",
                                     "type": "DATE", "required": True}],
                         "type" : "SPATIAL"})

The X DevAPI supports locking matching rows, for the mysqlx.Collection.find() and mysqlx.Table.select() methods, which allows safe and transactional document/row updates on collections or tables.

There are two types of locks:

• mysqlx.ReadStatement.lock_shared() permits the transaction that holds the lock to read a row.
• mysqlx.ReadStatement.lock_exclusive() permits the transaction that holds the lock to update or delete a row.

Setup

Assuming the existence of test_schema.test_collection collection.

[{
    "_id": "1",
    "name": "Fred",
    "age": 21
 },{
    "_id": "2",
    "name": "Sakila",
    "age": 23
 },{
    "_id": "3",
    "name": "Mike",
    "age": 42
}]

Get the session and collection objects.

# client 1
session_1 = mysqlx.get_session("root:@localhost:33060")
schema_1 = session_1.get_schema("test_schema")
collection_1 = schema_1.get_collection("test_collection")

# client 2
session_2 = mysqlx.get_session("root:@localhost:33060")
schema_2 = session_2.get_schema("test_schema")
collection_2 = schema_2.get_collection("test_collection")

Shared lock

# client 1
session_1.start_transaction()
collection_1.find("_id = '1'").lock_shared().execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = '2'").lock_shared().execute()  # should return immediately
collection_2.find("_id = '1'").lock_shared().execute()  # should return immediately

# client 1
session_1.rollback()

# client 2
session_2.rollback()

Exclusive Lock

# client 1
session_1.start_transaction()
collection_1.find("_id = '1'").lock_exclusive().execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = '2'").lock_exclusive().execute()  # should return immediately
collection_2.find("_id = '1'").lock_exclusive().execute()  # session_2 should block

# client 1
session_1.rollback()  # session_2 should unblock now

# client 2
session_2.rollback()

Shared Lock after Exclusive

# client 1
session_1.start_transaction()
collection_1.find("_id = '1'").lock_exclusive().execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = '2'").lock_shared().execute()  # should return immediately
collection_2.find("_id = '1'").lock_shared().execute()  # session_2 blocks

# client 1
session_1.rollback()  # session_2 should unblock now

# client 2
session_2.rollback()

Exclusive Lock after Shared

# client 1
session_1.start_transaction()
collection_1.find("_id in ('1', '3')").lock_shared().execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = '2'").lock_exclusive().execute()  # should return immediately
collection_2.find("_id = '3'").lock_shared().execute()     # should return immediately
collection_2.find("_id = '1'").lock_exclusive().execute()  # session_2 should block

# client 1
session_1.rollback()  # session_2 should unblock now

# client 2
session_2.rollback()

If a row is locked by a transaction, a transaction that requests the same locked row must wait until the blocking transaction releases the row lock. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.

To avoid waiting for other transactions to release row locks, mysqlx.LockContention.NOWAIT and mysqlx.LockContention.SKIP_LOCKED lock contentions options may be used.

NOWAIT

A locking read that uses mysqlx.LockContention.NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.

Example of reading a share locked document using mysqlx.ReadStatement.lock_shared():

# client 1
session_1.start_transaction()
collection_1.find("_id = :id").lock_shared().bind("id", "1").execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = :id").lock_shared(mysqlx.LockContention.NOWAIT) \
            .bind("id", "1").execute()
# The execution should return immediately, no block and no error is thrown

collection_2.modify("_id = '1'").set("age", 43).execute()
# The transaction should be blocked

# client 1
session_1.commit()
# session_2 should unblock now

# client 2
session_2.rollback()

SKIP_LOCKED

A locking read that uses mysqlx.LockContention.SKIP_LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.

Example of reading a share locked document using mysqlx.ReadStatement.lock_exclusive():

# client 1
session_1.start_transaction()
collection_1.find("_id = :id").lock_shared().bind("id", "1").execute()

# client 2
session_2.start_transaction()
collection_2.find("_id = :id").lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) \
            .bind("id", "1").execute()
# The execution should return immediately, no error is thrown

# client 1
session_1.commit()

# client 2
collection_2.find("_id = :id").lock_exclusive(mysqlx.LockContention.SKIP_LOCKED) \
            .bind("id", 1).execute()
# Since commit is done in 'client 1' then the read must be possible now and
# no error is thrown
session_2.rollback()

The MySQL server stores operational details for all and each client that is connected, such attributes are called connection attributes. Some connection attributes are defined by X DevAPI itself, these can be observed in the following example.

import mysqlx

# Connect to server on localhost
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
})

On the server the connection attributes may look like the following:

mysql> SELECT ATTR_NAME, ATTR_VALUE FROM performance_schema.session_account_connect_attrs;

+-----------------+------------------------+
| ATTR_NAME       | ATTR_VALUE             |
+-----------------+------------------------+
| _pid            | 16988                  |
| program_name    | mysql                  |
| _client_name    | mysql-connector-python |
| _thread         | 17588                  |
| _client_version | 8.0.15                 |
| _client_license | GPL-2.0                |
| _os             | Win64                  |
| _platform       | x86_64                 |
+-----------------+------------------------+

The other kind of connection attributes are user specified, these can be specified by the key connection-attributes or in the form of URL attribute while getting the connection for example:

import mysqlx

mysqlx.getSession('mysqlx://mike@localhost:33060/schema?connection-attributes=[my_attribute=some_value,foo=bar]')

On the server the connection attributes may look like the following:

mysql> SELECT ATTR_NAME, ATTR_VALUE FROM performance_schema.session_account_connect_attrs;

+-----------------+------------------------+
| ATTR_NAME       | ATTR_VALUE             |
+-----------------+------------------------+
| _pid            | 16988                  |
| program_name    | mysql                  |
| _client_name    | mysql-connector-python |
| _thread         | 17588                  |
| _client_version | 8.0.15                 |
| _client_license | GPL-2.0                |
| _os             | Win64                  |
| _platform       | x86_64                 |
| foo             | bar                    |
| my_attribute    | some_value             |
+-----------------+------------------------+

NOTE:

connection attributes defined by the user can not start with the underscore ( _ ) character.

mysqlx.get_client(connection_string: str | Dict[str, Any], options_string: str | Dict[str, Any]) -> Client

Creates a Client instance with the provided connection data and settings.

Parameters

• connection_string --

  A string or a dict type object to indicate the             connection data used to connect to a MySQL server.

  The string must have the following uri format:

  cnx_str = 'mysqlx://{user}:{pwd}@{host}:{port}'
  cnx_str = ('mysqlx://{user}:{pwd}@['
             '    (address={host}:{port}, priority=n),'
             '    (address={host}:{port}, priority=n), ...]'
             '       ?[option=value]')

  And the dictionary:

  cnx_dict = {
      'host': 'The host where the MySQL product is running',
      'port': '(int) the port number configured for X protocol',
      'user': 'The user name account',
      'password': 'The password for the given user account',
      'ssl-mode': 'The flags for ssl mode in mysqlx.SSLMode.FLAG',
      'ssl-ca': 'The path to the ca.cert'
      "connect-timeout": '(int) milliseconds to wait on timeout'
  }

• options_string --

  A string in the form of a document or a dictionary             type with configuration for the client.

  Current options include:

  options = {
      'pooling': {
          'enabled': (bool), # [True | False], True by default
          'max_size': (int), # Maximum connections per pool
          "max_idle_time": (int), # milliseconds that a
              # connection will remain active while not in use.
              # By default 0, means infinite.
          "queue_timeout": (int), # milliseconds a request will
              # wait for a connection to become available.
              # By default 0, means infinite.
      }
  }

Returns

Client object.

Return type

mysqlx.Client

Added in version 8.0.13.

mysqlx.get_session(*args: Any, **kwargs: Any) -> Session

Creates a Session instance using the provided connection data.

Parameters

• *args -- Variable length argument list with the connection data used to connect to a MySQL server. It can be a dictionary or a connection string.
• **kwargs -- Arbitrary keyword arguments with connection data used to connect to the database.

Returns

Session object.

Return type

mysqlx.Session

class mysqlx.Client(connection_dict: Dict[str, Any], options_dict: Dict[str, Any] | None = None)

Bases: object

Class defining a client, it stores a connection configuration.

Parameters

• connection_dict (dict) -- The connection information to connect to a MySQL server.
• options_dict (dict) -- The options to configure this client.

Added in version 8.0.13.

close() -> None

Closes the sessions opened by this client.

get_session() -> Session

Creates a Session instance using the provided connection data.

Returns

Session object.

Return type

Session

class mysqlx.Session(settings: Dict[str, Any])

Bases: object

Enables interaction with a X Protocol enabled MySQL Product.

The functionality includes:

• Accessing available schemas.
• Schema management operations.
• Retrieval of connection information.

Parameters

settings (dict) -- Connection data used to connect to the database.

close() -> None

Closes the session.

close_connections() -> None

Closes all underliying connections as pooled connections

commit() -> None

Commits all the operations executed after a call to startTransaction().

create_schema(name: str) -> Schema

Creates a schema on the database and returns the corresponding object.

Parameters

name (string) -- A string value indicating the schema name.

drop_schema(name: str) -> None

Drops the schema with the specified name.

Parameters

name (string) -- The name of the Schema object to be retrieved.

get_connection() -> Connection

Returns the underlying connection.

Returns

The connection object.

Return type

mysqlx.connection.Connection

get_default_schema() -> Schema | None

Retrieves a Schema object from the current session by the schema name configured in the connection settings.

Returns

The Schema object with the given name at connect

time.

None: In case the default schema was not provided with the

initialization data.

Return type

mysqlx.Schema

Raises

mysqlx.ProgrammingError -- If the provided default schema
   does not exists.

get_schema(name: str) -> Schema

Retrieves a Schema object from the current session by it's name.

Parameters

name (string) -- The name of the Schema object to be retrieved.

Returns

The Schema object with the given name.

Return type

mysqlx.Schema

get_schemas() -> List[str]

Returns the list of schemas in the current session.

Returns

The list of schemas in the current session.

Return type

list

Added in version 8.0.12.

is_open() -> bool

Returns True if the session is open.

Returns

Returns True if the session is open.

Return type

bool

release_savepoint(name: str) -> None

Release a transaction savepoint with the given name.

Parameters

name (string) -- The savepoint name.

rollback() -> None

Discards all the operations executed after a call to startTransaction().

rollback_to(name: str) -> None

Rollback to a transaction savepoint with the given name.

Parameters

name (string) -- The savepoint name.

set_savepoint(name: str | None = None) -> str

Creates a transaction savepoint.

If a name is not provided, one will be generated using the uuid.uuid1() function.

Parameters

name (Optional[string]) -- The savepoint name.

Returns

The savepoint name.

Return type

string

sql(sql: str) -> SqlStatement

Creates a mysqlx.SqlStatement object to allow running the SQL statement on the target MySQL Server.

Parameters

sql (string) -- The SQL statement to be executed.

Returns

SqlStatement object.

Return type

mysqlx.SqlStatement

start_transaction() -> None

Starts a transaction context on the server.

property use_pure: bool

True to use pure Python Protobuf implementation.

Type

bool

class mysqlx.Schema(session: SessionType, name: str)

Bases: DatabaseObject

A client-side representation of a database schema. Provides access to the schema contents.

Parameters

• session (mysqlx.XSession) -- Session object.
• name (str) -- The Schema name.

am_i_real() -> Any

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

Raises

NotImplementedError -- This method must be implemented.

Deprecated since version 8.0.12: Use exists_in_database() method instead.

create_collection(name: str, reuse_existing: bool = False, validation: Dict[str, str | Dict] | None = None, **kwargs: Any) -> Collection

Creates in the current schema a new collection with the specified name and retrieves an object representing the new collection created.

Parameters

• name (str) -- The name of the collection.
• reuse_existing (bool) -- True to reuse an existing collection.
• validation (Optional[dict]) -- A dict, containing the keys level with the validation level and schema with a dict or a string representation of a JSON schema specification.

Returns

Collection object.

Return type

mysqlx.Collection

Raises

• mysqlx.ProgrammingError -- If reuse_existing is False
     and collection exists or the
     collection name is invalid.
• mysqlx.NotSupportedError -- If schema validation is not
     supported by the server.

Changed in version 8.0.21.

drop_collection(name: str) -> None

Drops a collection.

Parameters

name (str) -- The name of the collection to be dropped.

exists_in_database() -> bool

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

get_collection(name: str, check_existence: bool = False) -> Collection

Returns the collection of the given name for this schema.

Returns

Collection object.

Return type

mysqlx.Collection

get_collection_as_table(name: str, check_existence: bool = False) -> Table

Returns a a table object for the given collection

Returns

Table object.

Return type

mysqlx.Table

get_collections() -> List[Collection]

Returns a list of collections for this schema.

Returns

List of Collection objects.

Return type

list

get_connection() -> ConnectionType

Returns the underlying connection.

Returns

The connection object.

Return type

mysqlx.connection.Connection

get_name() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

get_schema() -> Schema

Returns the Schema object of this database object.

Returns

The Schema object.

Return type

mysqlx.Schema

get_session() -> SessionType

Returns the session of this database object.

Returns

The Session object.

Return type

mysqlx.Session

get_table(name: str, check_existence: bool = False) -> Table

Returns the table of the given name for this schema.

Returns

Table object.

Return type

mysqlx.Table

get_tables() -> List[Table]

Returns a list of tables for this schema.

Returns

List of Table objects.

Return type

list

get_view(name: str, check_existence: bool = False) -> View

Returns the view of the given name for this schema.

Returns

View object.

Return type

mysqlx.View

modify_collection(name: str, validation: Dict[str, str | Dict] | None = None) -> None

Modifies a collection using a JSON schema validation.

Parameters

• name (str) -- The name of the collection.
• validation (Optional[dict]) -- A dict, containing the keys level with the validation level and schema with a dict or a string representation of a JSON schema specification.

Raises

• mysqlx.ProgrammingError -- If the collection name or
     validation is invalid.
• mysqlx.NotSupportedError -- If schema validation is not
     supported by the server.

Added in version 8.0.21.

property name: str

The name of this database object.

Type

str

property schema: Schema

The Schema object.

Type

mysqlx.Schema

property session: SessionType

The Session object.

Type

mysqlx.Session

who_am_i() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

Deprecated since version 8.0.12: Use get_name() method instead.

class mysqlx.Collection(schema: Schema, name: str | bytes)

Bases: DatabaseObject

Represents a collection of documents on a schema.

Parameters

• schema (mysqlx.Schema) -- The Schema object.
• name (str) -- The collection name.

add(*values: DbDoc) -> AddStatement

Adds a list of documents to a collection.

Parameters

*values -- The document list to be added into the collection.

Returns

AddStatement object.

Return type

mysqlx.AddStatement

add_or_replace_one(doc_id: str, doc: Dict | DbDoc) -> Result

Upserts the Document matching the document ID with a new document provided.

Parameters

• doc_id (str) -- Document ID
• doc (mysqlx.DbDoc or dict) -- New Document

am_i_real() -> Any

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

Raises

NotImplementedError -- This method must be implemented.

Deprecated since version 8.0.12: Use exists_in_database() method instead.

count() -> int

Counts the documents in the collection.

Returns

The total of documents in the collection.

Return type

int

create_index(index_name: str, fields_desc: Dict[str, Any]) -> CreateCollectionIndexStatement

Creates a collection index.

Parameters

• index_name (str) -- Index name.

• fields_desc (dict) --

  A dictionary containing the fields members that constraints the index to be created. It must have the form as shown in the following:

  {"fields": [{"field": member_path,
               "type": member_type,
               "required": member_required,
               "array": array,
               "collation": collation,
               "options": options,
               "srid": srid},
               # {... more members,
               #      repeated as many times
               #      as needed}
               ],
   "type": type}

drop_index(index_name: str) -> None

Drops a collection index.

Parameters

index_name (str) -- Index name.

exists_in_database() -> bool

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

find(condition: str | None = None) -> FindStatement

Retrieves documents from a collection.

Parameters

condition (Optional[str]) -- The string with the filter expression of the documents to be retrieved.

get_connection() -> ConnectionType

Returns the underlying connection.

Returns

The connection object.

Return type

mysqlx.connection.Connection

get_name() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

get_one(doc_id: str) -> DbDoc

Returns a Document matching the Document ID.

Parameters

doc_id (str) -- Document ID

Returns

The Document matching the Document ID.

Return type

mysqlx.DbDoc

get_schema() -> Schema

Returns the Schema object of this database object.

Returns

The Schema object.

Return type

mysqlx.Schema

get_session() -> SessionType

Returns the session of this database object.

Returns

The Session object.

Return type

mysqlx.Session

modify(condition: str) -> ModifyStatement

Modifies documents based on the condition.

Parameters

condition (str) -- The string with the filter expression of the documents to be modified.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

Changed in version 8.0.12: The condition parameter is now mandatory.

property name: str

The name of this database object.

Type

str

remove(condition: str) -> RemoveStatement

Removes documents based on the condition.

Parameters

condition (str) -- The string with the filter expression of the documents to be removed.

Returns

RemoveStatement object.

Return type

mysqlx.RemoveStatement

Changed in version 8.0.12: The condition parameter is now mandatory.

remove_one(doc_id: str) -> Result

Removes a Document matching the Document ID.

Parameters

doc_id (str) -- Document ID

Returns

Result object.

Return type

mysqlx.Result

replace_one(doc_id: str, doc: Dict | DbDoc) -> Result

Replaces the Document matching the document ID with a new document provided.

Parameters

• doc_id (str) -- Document ID
• doc (mysqlx.DbDoc or dict) -- New Document

property schema: Schema

The Schema object.

Type

mysqlx.Schema

property session: SessionType

The Session object.

Type

mysqlx.Session

who_am_i() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

Deprecated since version 8.0.12: Use get_name() method instead.

class mysqlx.Table(schema: Schema, name: str | bytes)

Bases: DatabaseObject

Represents a database table on a schema.

Provides access to the table through standard INSERT/SELECT/UPDATE/DELETE statements.

Parameters

• schema (mysqlx.Schema) -- The Schema object.
• name (str) -- The table name.

am_i_real() -> Any

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

Raises

NotImplementedError -- This method must be implemented.

Deprecated since version 8.0.12: Use exists_in_database() method instead.

count() -> int

Counts the rows in the table.

Returns

The total of rows in the table.

Return type

int

delete() -> DeleteStatement

Creates a new mysqlx.DeleteStatement object.

Returns

DeleteStatement object

Return type

mysqlx.DeleteStatement

Changed in version 8.0.12: The condition parameter was removed.

exists_in_database() -> bool

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

get_connection() -> ConnectionType

Returns the underlying connection.

Returns

The connection object.

Return type

mysqlx.connection.Connection

get_name() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

get_schema() -> Schema

Returns the Schema object of this database object.

Returns

The Schema object.

Return type

mysqlx.Schema

get_session() -> SessionType

Returns the session of this database object.

Returns

The Session object.

Return type

mysqlx.Session

insert(*fields: Any) -> InsertStatement

Creates a new mysqlx.InsertStatement object.

Parameters

*fields -- The fields to be inserted.

Returns

InsertStatement object

Return type

mysqlx.InsertStatement

is_view() -> bool

Determine if the underlying object is a view or not.

Returns

True if the underlying object is a view.

Return type

bool

property name: str

The name of this database object.

Type

str

property schema: Schema

The Schema object.

Type

mysqlx.Schema

select(*fields: str) -> SelectStatement

Creates a new mysqlx.SelectStatement object.

Parameters

*fields -- The fields to be retrieved.

Returns

SelectStatement object

Return type

mysqlx.SelectStatement

property session: SessionType

The Session object.

Type

mysqlx.Session

update() -> UpdateStatement

Creates a new mysqlx.UpdateStatement object.

Returns

UpdateStatement object

Return type

mysqlx.UpdateStatement

who_am_i() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

Deprecated since version 8.0.12: Use get_name() method instead.

class mysqlx.View(schema: Schema, name: str | bytes)

Bases: Table

Represents a database view on a schema.

Provides a mechanism for creating, alter and drop views.

Parameters

• schema (mysqlx.Schema) -- The Schema object.
• name (str) -- The table name.

am_i_real() -> Any

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

Raises

NotImplementedError -- This method must be implemented.

Deprecated since version 8.0.12: Use exists_in_database() method instead.

count() -> int

Counts the rows in the table.

Returns

The total of rows in the table.

Return type

int

delete() -> DeleteStatement

Creates a new mysqlx.DeleteStatement object.

Returns

DeleteStatement object

Return type

mysqlx.DeleteStatement

Changed in version 8.0.12: The condition parameter was removed.

exists_in_database() -> bool

Verifies if this object exists in the database.

Returns

True if object exists in database.

Return type

bool

get_connection() -> ConnectionType

Returns the underlying connection.

Returns

The connection object.

Return type

mysqlx.connection.Connection

get_name() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

get_schema() -> Schema

Returns the Schema object of this database object.

Returns

The Schema object.

Return type

mysqlx.Schema

get_session() -> SessionType

Returns the session of this database object.

Returns

The Session object.

Return type

mysqlx.Session

insert(*fields: Any) -> InsertStatement

Creates a new mysqlx.InsertStatement object.

Parameters

*fields -- The fields to be inserted.

Returns

InsertStatement object

Return type

mysqlx.InsertStatement

is_view() -> bool

Determine if the underlying object is a view or not.

Returns

True if the underlying object is a view.

Return type

bool

property name: str

The name of this database object.

Type

str

property schema: Schema

The Schema object.

Type

mysqlx.Schema

select(*fields: str) -> SelectStatement

Creates a new mysqlx.SelectStatement object.

Parameters

*fields -- The fields to be retrieved.

Returns

SelectStatement object

Return type

mysqlx.SelectStatement

property session: SessionType

The Session object.

Type

mysqlx.Session

update() -> UpdateStatement

Creates a new mysqlx.UpdateStatement object.

Returns

UpdateStatement object

Return type

mysqlx.UpdateStatement

who_am_i() -> str

Returns the name of this database object.

Returns

The name of this database object.

Return type

str

Deprecated since version 8.0.12: Use get_name() method instead.

mysqlx.expr(string: str, allow_relational: bool = True) -> None

Expression parser class.

class mysqlx.Column(col_type: int, catalog: str | None = None, schema: str | None = None, table: str | None = None, original_table: str | None = None, name: str | None = None, original_name: str | None = None, length: int | None = None, collation: int | None = None, fractional_digits: int | None = None, flags: int | None = None, content_type: int | None = None)

Bases: object

Represents meta data for a table column.

Parameters

• col_type (int) -- The column type.
• catalog (str) -- The catalog.
• schema (str) -- The schema name.
• table (str) -- The table name.
• original_table (str) -- The original table name.
• name (str) -- The column name.
• original_name (str) -- The original table name.
• length (int) -- The column length,
• collation (str) -- The collation name.
• fractional_digits (int) -- The fractional digits.
• flags (int) -- The flags.
• content_type (int) -- The content type.

Changed in version 8.0.12.

property character_set_name: str

The character set name.

Added in version 8.0.12.

Type

str

property collation_name: str

The collation name.

Added in version 8.0.12.

Type

str

property column_label: str

The column label.

Added in version 8.0.12.

Type

str

property column_name: str

The column name.

Added in version 8.0.12.

Type

str

property fractional_digits: int

The column fractional digits.

Added in version 8.0.12.

Type

int

get_character_set_name() -> str

Returns the character set name.

Returns

The character set name.

Return type

str

get_collation_name() -> str

Returns the collation name.

Returns

The collation name.

Return type

str

get_column_label() -> str

Returns the column label.

Returns

The column label.

Return type

str

get_column_name() -> str

Returns the column name.

Returns

The column name.

Return type

str

get_fractional_digits() -> int

Returns the column fractional digits.

Returns

The column fractional digits.

Return type

int

get_length() -> int

Returns the column length.

Returns

The column length.

Return type

int

get_proto_type() -> int

Returns the column proto type.

Returns

The column proto type.

Return type

int

get_schema_name() -> str

Returns the schema name.

Returns

The schema name.

Return type

str

get_table_label() -> str

Returns the table label.

Returns

The table label.

Return type

str

get_table_name() -> str

Returns the table name.

Returns

The table name.

Return type

str

get_type() -> int

Returns the column type.

Returns

The column type.

Return type

int

is_bytes() -> bool

Returns True if is bytes.

Returns

Returns True if is bytes.

Return type

bool

is_number_signed() -> bool

Returns True if is a number signed.

Returns

Returns True if is a number signed.

Return type

bool

is_padded() -> bool | int

Returns True if is padded.

Returns

Returns True if is padded.

Return type

bool

property length: int

int. The column length.

Added in version 8.0.12.

property schema_name: str

The schema name.

Added in version 8.0.12.

Type

str

property table_label: str

The table label.

Added in version 8.0.12.

Type

str

property table_name: str

The table name.

Added in version 8.0.12.

Type

str

property type: int

The column type.

Added in version 8.0.12.

Type

int

class mysqlx.Row(resultset: BufferingResult | RowResult, fields: Sequence[int | float | str | bytes | Decimal | datetime | timedelta | None])

Bases: object

Represents a row element returned from a SELECT query.

Parameters

• resultset (mysqlx.SqlResult or mysqlx.RowResult) -- The result set.
• fields (list) -- The list of fields.

get_string(str_index: str) -> str

Returns the value using the column name.

Parameters

str_index (str) -- The column name.

Deprecated since version 8.0.12.

class mysqlx.Result(connection: ConnectionType | None = None, ids: List[int] | None = None)

Bases: BaseResult

Allows retrieving information about non query operations performed on the database.

Parameters

connection (mysqlx.connection.Connection) -- The Connection object. ids (list): A list of IDs.

append_warning(level: int, code: int, msg: str) -> None

Append a warning.

Parameters

• level (int) -- The warning level.
• code (int) -- The warning code.
• msg (str) -- The warning message.

get_affected_items_count() -> int

Returns the number of affected items for the last operation.

Returns

The number of affected items.

Return type

int

get_autoincrement_value() -> int

Returns the last insert id auto generated.

Returns

The last insert id.

Return type

int

get_document_id() -> int | None

Returns ID of the last document inserted into a collection.

Deprecated since version 8.0.12.

get_generated_ids() -> List[int]

Returns the generated ids.

get_generated_insert_id() -> int

Returns the generated insert id.

Deprecated since version 8.0.12.

get_warnings() -> List[Dict[str, int | str]]

Returns the warnings.

Returns

The list of warnings.

Return type

list

get_warnings_count() -> int

Returns the number of warnings.

Returns

The number of warnings.

Return type

int

set_closed(flag: bool) -> None

Sets if resultset fetch is done.

set_generated_ids(generated_ids: List[int]) -> None

Sets the generated ids.

set_generated_insert_id(generated_id: int) -> None

Sets the generated insert id.

set_rows_affected(total: int) -> None

Sets the number of rows affected.

class mysqlx.BufferingResult(connection: ConnectionType)

Bases: BaseResult

Provides base functionality for buffering result objects.

Parameters

connection (mysqlx.connection.Connection) -- The Connection object. ids (list): A list of IDs.

append_warning(level: int, code: int, msg: str) -> None

Append a warning.

Parameters

• level (int) -- The warning level.
• code (int) -- The warning code.
• msg (str) -- The warning message.

property count: int

The total of items.

Type

int

fetch_all() -> List[Row | DbDoc]

Fetch all items.

Returns

The list of items of mysqlx.DbDoc or

mysqlx.Row.

Return type

list

fetch_one() -> Row | DbDoc | None

Fetch one item.

Returns

one result item.

Return type

mysqlx.Row or mysqlx.DbDoc

get_affected_items_count() -> int

Returns the number of affected items for the last operation.

Returns

The number of affected items.

Return type

int

get_warnings() -> List[Dict[str, int | str]]

Returns the warnings.

Returns

The list of warnings.

Return type

list

get_warnings_count() -> int

Returns the number of warnings.

Returns

The number of warnings.

Return type

int

index_of(col_name: str) -> int

Returns the index of the column.

Returns

The index of the column.

Return type

int

set_closed(flag: bool) -> None

Sets if resultset fetch is done.

set_generated_ids(generated_ids: List[int]) -> None

Sets the generated ids.

set_generated_insert_id(generated_id: int) -> None

Sets the generated insert id.

set_has_data(flag: bool) -> None

Sets if result has data.

Parameters

flag (bool) -- True if result has data.

set_has_more_results(flag: bool) -> None

Sets if has more results.

Parameters

flag (bool) -- True if has more results.

set_rows_affected(total: int) -> None

Sets the number of rows affected.

class mysqlx.RowResult(connection: ConnectionType)

Bases: BufferingResult

Allows traversing the Row objects returned by a Table.select operation.

Parameters

connection (mysqlx.connection.Connection) -- The Connection object.

append_warning(level: int, code: int, msg: str) -> None

Append a warning.

Parameters

• level (int) -- The warning level.
• code (int) -- The warning code.
• msg (str) -- The warning message.

property columns: List[Column]

The list of columns.

Type

list

property count: int

The total of items.

Type

int

fetch_all() -> List[Row | DbDoc]

Fetch all items.

Returns

The list of items of mysqlx.DbDoc or

mysqlx.Row.

Return type

list

fetch_one() -> Row | DbDoc | None

Fetch one item.

Returns

one result item.

Return type

mysqlx.Row or mysqlx.DbDoc

get_affected_items_count() -> int

Returns the number of affected items for the last operation.

Returns

The number of affected items.

Return type

int

get_columns() -> List[Column]

Returns the list of columns.

Returns

The list of columns.

Return type

list

Added in version 8.0.12.

get_warnings() -> List[Dict[str, int | str]]

Returns the warnings.

Returns

The list of warnings.

Return type

list

get_warnings_count() -> int

Returns the number of warnings.

Returns

The number of warnings.

Return type

int

index_of(col_name: str) -> int

Returns the index of the column.

Returns

The index of the column.

Return type

int

set_closed(flag: bool) -> None

Sets if resultset fetch is done.

set_generated_ids(generated_ids: List[int]) -> None

Sets the generated ids.

set_generated_insert_id(generated_id: int) -> None

Sets the generated insert id.

set_has_data(flag: bool) -> None

Sets if result has data.

Parameters

flag (bool) -- True if result has data.

set_has_more_results(flag: bool) -> None

Sets if has more results.

Parameters

flag (bool) -- True if has more results.

set_rows_affected(total: int) -> None

Sets the number of rows affected.

class mysqlx.SqlResult(connection: ConnectionType)

Bases: RowResult

Represents a result from a SQL statement.

Parameters

connection (mysqlx.connection.Connection) -- The Connection object.

append_warning(level: int, code: int, msg: str) -> None

Append a warning.

Parameters

• level (int) -- The warning level.
• code (int) -- The warning code.
• msg (str) -- The warning message.

property columns: List[Column]

The list of columns.

Type

list

property count: int

The total of items.

Type

int

fetch_all() -> List[Row | DbDoc]

Fetch all items.

Returns

The list of items of mysqlx.DbDoc or

mysqlx.Row.

Return type

list

fetch_one() -> Row | DbDoc | None

Fetch one item.

Returns

one result item.

Return type

mysqlx.Row or mysqlx.DbDoc

get_affected_items_count() -> int

Returns the number of affected items for the last operation.

Returns

The number of affected items.

Return type

int

get_autoincrement_value() -> int

Returns the identifier for the last record inserted.

Returns

The identifier of the last record inserted.

Return type

str

get_columns() -> List[Column]

Returns the list of columns.

Returns

The list of columns.

Return type

list

Added in version 8.0.12.

get_warnings() -> List[Dict[str, int | str]]

Returns the warnings.

Returns

The list of warnings.

Return type

list

get_warnings_count() -> int

Returns the number of warnings.

Returns

The number of warnings.

Return type

int

has_data() -> bool

Returns True if result has data.

Returns

Returns True if result has data.

Return type

bool

Added in version 8.0.12.

index_of(col_name: str) -> int

Returns the index of the column.

Returns

The index of the column.

Return type

int

next_result() -> bool

Process the next result.

Returns

Returns True if the fetch is done.

Return type

bool

set_closed(flag: bool) -> None

Sets if resultset fetch is done.

set_generated_ids(generated_ids: List[int]) -> None

Sets the generated ids.

set_generated_insert_id(generated_id: int) -> None

Sets the generated insert id.

set_has_data(flag: bool) -> None

Sets if result has data.

Parameters

flag (bool) -- True if result has data.

set_has_more_results(flag: bool) -> None

Sets if has more results.

Parameters

flag (bool) -- True if has more results.

set_rows_affected(total: int) -> None

Sets the number of rows affected.

class mysqlx.DbDoc(value: str | Dict[str, Any])

Bases: object

Represents a generic document in JSON format.

Parameters

value (object) -- The value can be a JSON string or a dict.

Raises

ValueError -- If value type is not a basestring or dict.

as_str() -> str

Serialize mysqlx.DbDoc to a JSON formatted str.

Returns

A JSON formatted str representation of the document.

Return type

str

Added in version 8.0.16.

copy(doc_id: str | None = None) -> DbDoc

Returns a new copy of a mysqlx.DbDoc object containing the doc_id provided. If doc_id is not provided, it will be removed from new mysqlx.DbDoc object.

Parameters

doc_id (Optional[str]) -- Document ID

Returns

A new instance of DbDoc containing the _id provided

Return type

mysqlx.DbDoc

keys() -> KeysView[str]

Returns the keys.

Returns

The keys.

Return type

list

class mysqlx.Statement(target: DatabaseTargetType, doc_based: bool = True)

Bases: object

Provides base functionality for statement objects.

Parameters

• target (object) -- The target database object, it can be mysqlx.Collection or mysqlx.Table.
• doc_based (bool) -- True if it is document based.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Any

Execute the statement.

Raises

NotImplementedError -- This method must be implemented.

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

class mysqlx.FilterableStatement(target: DatabaseTargetType, doc_based: bool = True, condition: str | None = None)

Bases: Statement

A statement to be used with filterable statements.

Parameters

• target (object) -- The target database object, it can be mysqlx.Collection or mysqlx.Table.
• doc_based (Optional[bool]) -- True if it is document based (default: True).
• condition (Optional[str]) -- Sets the search condition to filter documents or records.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Any

Execute the statement.

Raises

NotImplementedError -- This method must be implemented.

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> FilterableStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> FilterableStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter documents or records.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

class mysqlx.SqlStatement(connection: ConnectionType, sql: str)

Bases: Statement

A statement for SQL execution.

Parameters

• connection (mysqlx.connection.Connection) -- Connection object.
• sql (string) -- The sql statement to be executed.

bind(*args: Any) -> SqlStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The value(s) to bind.

Returns

SqlStatement object.

Return type

mysqlx.SqlStatement

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> SqlResult

Execute the statement.

Returns

SqlResult object.

Return type

mysqlx.SqlResult

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Tuple | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property sql: str

The SQL text statement.

Type

string

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

class mysqlx.FindStatement(collection: DatabaseTargetType, condition: str | None = None)

Bases: ReadStatement

A statement document selection on a Collection.

Parameters

• collection (mysqlx.Collection) -- The Collection object.
• condition (Optional[str]) -- An optional expression to identify the documents to be retrieved. If not specified all the documents will be included on the result unless a limit is set.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> DocResult | RowResult

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

fields(*fields: str) -> FindStatement

Sets a document field filter.

Parameters

*fields -- The string expressions identifying the fields to be extracted.

Returns

FindStatement object.

Return type

mysqlx.FindStatement

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

group_by(*fields: str) -> ReadStatement

Sets a grouping criteria for the resultset.

Parameters

*fields -- The string expressions identifying the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

having(condition: str) -> ReadStatement

Sets a condition for records to be considered in agregate function operations.

Parameters

condition (string) -- A condition on the agregate functions used on the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

is_lock_exclusive() -> bool

Returns True if is EXCLUSIVE LOCK.

Returns

True if is EXCLUSIVE LOCK.

Return type

bool

is_lock_shared() -> bool

Returns True if is SHARED LOCK.

Returns

True if is SHARED LOCK.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

property lock_contention: LockContention

The lock contention value.

Type

mysqlx.LockContention

lock_exclusive(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with EXCLUSIVE LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

lock_shared(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with SHARED LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> FindStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FindStatement object.

Return type

mysqlx.FindStatement

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> FilterableStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter documents or records.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

class mysqlx.AddStatement(collection: DatabaseTargetType)

Bases: WriteStatement

A statement for document addition on a collection.

Parameters

collection (mysqlx.Collection) -- The Collection object.

add(*values: DbDoc) -> AddStatement

Adds a list of documents into a collection.

Parameters

*values -- The documents to be added into the collection.

Returns

AddStatement object.

Return type

mysqlx.AddStatement

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

get_values() -> List[int | str | DbDoc | Dict[str, Any] | List[str | int | float | ExprParser | Dict[str, Any] | None]]

Returns the list of values.

Returns

The list of values.

Return type

list

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

is_upsert() -> bool

Returns True if it's an upsert.

Returns

True if it's an upsert.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

upsert(value: bool = True) -> AddStatement

Sets the upset flag to the boolean of the value provided. Setting of this flag allows updating of the matched rows/documents with the provided value.

Parameters

value (optional[bool]) -- Set or unset the upsert flag.

class mysqlx.RemoveStatement(collection: DatabaseTargetType, condition: str)

Bases: FilterableStatement

A statement for document removal from a collection.

Parameters

• collection (mysqlx.Collection) -- The Collection object.
• condition (str) -- Sets the search condition to identify the documents to be removed.

Changed in version 8.0.12: The condition parameter was added.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

Raises

ProgrammingError -- If condition was not set.

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> RemoveStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FindStatement object.

Return type

mysqlx.FindStatement

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> FilterableStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter documents or records.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

class mysqlx.ModifyStatement(collection: DatabaseTargetType, condition: str)

Bases: FilterableStatement

A statement for document update operations on a Collection.

Parameters

• collection (mysqlx.Collection) -- The Collection object.
• condition (str) -- Sets the search condition to identify the documents to be modified.

Changed in version 8.0.12: The condition parameter is now mandatory.

array_append(doc_path: str, value: Any) -> ModifyStatement

Inserts a value into a specific position in an array attribute in documents of a collection.

Parameters

• doc_path (string) -- A document path that identifies the array attribute and position where the value will be inserted.
• value (object) -- The value to be inserted.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

array_insert(field: str, value: Any) -> ModifyStatement

Insert a value into the specified array in documents of a collection.

Parameters

• field (string) -- A document path that identifies the array attribute and position where the value will be inserted.
• value (object) -- The value to be inserted.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

change(doc_path: str, value: Any) -> ModifyStatement

Add an update to the statement setting the field, if it exists at the document path, to the given value.

Parameters

• doc_path (string) -- The document path of the item to be set.
• value (object) -- The value to be set on the specified attribute.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

Deprecated since version 8.0.12.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

Raises

ProgrammingError -- If condition was not set.

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_update_ops() -> Dict[str, Any]

Returns the list of update operations.

Returns

The list of update operations.

Return type

list

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

patch(doc: Dict | DbDoc | ExprParser | str) -> ModifyStatement

Takes a mysqlx.DbDoc, string JSON format or a dict with the changes and applies it on all matching documents.

Parameters

doc (object) -- A generic document (DbDoc), string in JSON format or dict, with the changes to apply to the matching documents.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

set(doc_path: str, value: Any) -> ModifyStatement

Sets or updates attributes on documents in a collection.

Parameters

• doc_path (string) -- The document path of the item to be set.
• value (string) -- The value to be set on the specified attribute.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

sort(*clauses: str) -> ModifyStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

unset(*doc_paths: str) -> ModifyStatement

Removes attributes from documents in a collection.

Parameters

doc_paths (list) -- The list of document paths of the attributes to be removed.

Returns

ModifyStatement object.

Return type

mysqlx.ModifyStatement

where(condition: str) -> FilterableStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter documents or records.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

class mysqlx.SelectStatement(table: DatabaseTargetType, *fields: str)

Bases: ReadStatement

A statement for record retrieval operations on a Table.

Parameters

• table (mysqlx.Table) -- The Table object.
• *fields -- The fields to be retrieved.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> DocResult | RowResult

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_sql() -> str

Returns the generated SQL.

Returns

The generated SQL.

Return type

str

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

group_by(*fields: str) -> ReadStatement

Sets a grouping criteria for the resultset.

Parameters

*fields -- The string expressions identifying the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

having(condition: str) -> ReadStatement

Sets a condition for records to be considered in agregate function operations.

Parameters

condition (string) -- A condition on the agregate functions used on the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

is_lock_exclusive() -> bool

Returns True if is EXCLUSIVE LOCK.

Returns

True if is EXCLUSIVE LOCK.

Return type

bool

is_lock_shared() -> bool

Returns True if is SHARED LOCK.

Returns

True if is SHARED LOCK.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

property lock_contention: LockContention

The lock contention value.

Type

mysqlx.LockContention

lock_exclusive(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with EXCLUSIVE LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

lock_shared(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with SHARED LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

order_by(*clauses: str) -> SelectStatement

Sets the order by criteria.

Parameters

*clauses -- The expression strings defining the order by criteria.

Returns

SelectStatement object.

Return type

mysqlx.SelectStatement

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> FilterableStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> SelectStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter records.

Returns

SelectStatement object.

Return type

mysqlx.SelectStatement

class mysqlx.InsertStatement(table: DatabaseTargetType, *fields: Any)

Bases: WriteStatement

A statement for insert operations on Table.

Parameters

• table (mysqlx.Table) -- The Table object.
• *fields -- The fields to be inserted.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

get_values() -> List[int | str | DbDoc | Dict[str, Any] | List[str | int | float | ExprParser | Dict[str, Any] | None]]

Returns the list of values.

Returns

The list of values.

Return type

list

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

values(*values: Any) -> InsertStatement

Set the values to be inserted.

Parameters

*values -- The values of the columns to be inserted.

Returns

InsertStatement object.

Return type

mysqlx.InsertStatement

class mysqlx.DeleteStatement(table: DatabaseTargetType)

Bases: FilterableStatement

A statement that drops a table.

Parameters

table (mysqlx.Table) -- The Table object.

Changed in version 8.0.12: The condition parameter was removed.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

Raises

ProgrammingError -- If condition was not set.

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

order_by(*clauses: str) -> DeleteStatement

Sets the order by criteria.

Parameters

*clauses -- The expression strings defining the order by criteria.

Returns

DeleteStatement object.

Return type

mysqlx.DeleteStatement

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> FilterableStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> DeleteStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter records.

Returns

DeleteStatement object.

Return type

mysqlx.DeleteStatement

class mysqlx.UpdateStatement(table: DatabaseTargetType)

Bases: FilterableStatement

A statement for record update operations on a Table.

Parameters

table (mysqlx.Table) -- The Table object.

Changed in version 8.0.12: The fields parameters were removed.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object

Return type

mysqlx.Result

Raises

ProgrammingError -- If condition was not set.

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_update_ops() -> Dict[str, Any]

Returns the list of update operations.

Returns

The list of update operations.

Return type

list

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

order_by(*clauses: str) -> UpdateStatement

Sets the order by criteria.

Parameters

*clauses -- The expression strings defining the order by criteria.

Returns

UpdateStatement object.

Return type

mysqlx.UpdateStatement

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

set(field: str, value: Any) -> UpdateStatement

Updates the column value on records in a table.

Parameters

• field (string) -- The column name to be updated.
• value (object) -- The value to be set on the specified column.

Returns

UpdateStatement object.

Return type

mysqlx.UpdateStatement

sort(*clauses: str) -> FilterableStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> UpdateStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter records.

Returns

UpdateStatement object.

Return type

mysqlx.UpdateStatement

class mysqlx.CreateCollectionIndexStatement(collection: DatabaseTargetType, index_name: str, index_desc: Dict[str, Any])

Bases: Statement

A statement that creates an index on a collection.

Parameters

• collection (mysqlx.Collection) -- Collection.
• index_name (string) -- Index name.

• index_desc (dict) --

  A dictionary containing the fields members that constraints the index to be created. It must have the form as shown in the following:

  {"fields": [{"field": member_path,
               "type": member_type,
               "required": member_required,
               "collation": collation,
               "options": options,
               "srid": srid},
               # {... more members,
               #      repeated as many times
               #      as needed}
               ],
   "type": type}

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Result

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

class mysqlx.ReadStatement(target: DatabaseTargetType, doc_based: bool = True, condition: str | None = None)

Bases: FilterableStatement

Provide base functionality for Read operations

Parameters

• target (object) -- The target database object, it can be mysqlx.Collection or mysqlx.Table.
• doc_based (Optional[bool]) -- True if it is document based (default: True).
• condition (Optional[str]) -- Sets the search condition to filter documents or records.

bind(*args: Any) -> FilterableStatement

Binds value(s) to a specific placeholder(s).

Parameters

*args -- The name of the placeholder and the value to bind. A mysqlx.DbDoc object or a JSON string representation can be used.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ProgrammingError -- If the number of arguments is invalid.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> DocResult | RowResult

Execute the statement.

Returns

Result object.

Return type

mysqlx.Result

get_binding_map() -> Dict[str, Any]

Returns the binding map dictionary.

Returns

The binding map dictionary.

Return type

dict

get_bindings() -> Dict[str, Any] | List

Returns the bindings list.

Returns

The bindings list.

Return type

list

get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the grouping expression list.

Returns

The grouping expression list.

Return type

list

get_having() -> MessageType

Returns the having expression.

Returns

The having expression.

Return type

object

get_limit_offset() -> int

Returns the limit offset.

Returns

The limit offset.

Return type

int

get_limit_row_count() -> int

Returns the limit row count.

Returns

The limit row count.

Return type

int

get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the projection expression.

Returns

The projection expression.

Return type

object

get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]

Returns the sort expression.

Returns

The sort expression.

Return type

object

get_where_expr() -> MessageType

Returns the where expression.

Returns

The where expression.

Return type

object

group_by(*fields: str) -> ReadStatement

Sets a grouping criteria for the resultset.

Parameters

*fields -- The string expressions identifying the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

having(condition: str) -> ReadStatement

Sets a condition for records to be considered in agregate function operations.

Parameters

condition (string) -- A condition on the agregate functions used on the grouping criteria.

Returns

ReadStatement object.

Return type

mysqlx.ReadStatement

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

is_lock_exclusive() -> bool

Returns True if is EXCLUSIVE LOCK.

Returns

True if is EXCLUSIVE LOCK.

Return type

bool

is_lock_shared() -> bool

Returns True if is SHARED LOCK.

Returns

True if is SHARED LOCK.

Return type

bool

limit(row_count: int, offset: int | None = None) -> FilterableStatement

Sets the maximum number of items to be returned.

Parameters

row_count (int) -- The maximum number of items.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If row_count is not a positive integer.

Changed in version 8.0.12: The usage of offset was deprecated.

property lock_contention: LockContention

The lock contention value.

Type

mysqlx.LockContention

lock_exclusive(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with EXCLUSIVE LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

lock_shared(lock_contention: LockContention = LockContention.DEFAULT) -> ReadStatement

Execute a read operation with SHARED LOCK. Only one lock can be

active at a time.

Parameters

lock_contention (mysqlx.LockContention) -- Lock contention.

offset(offset: int) -> FilterableStatement

Sets the number of items to skip.

Parameters

offset (int) -- The number of items to skip.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Raises

ValueError -- If offset is not a positive integer.

Added in version 8.0.12.

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

sort(*clauses: str) -> FilterableStatement

Sets the sorting criteria.

Parameters

*clauses -- The expression strings defining the sort criteria.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object

where(condition: str) -> FilterableStatement

Sets the search condition to filter.

Parameters

condition (str) -- Sets the search condition to filter documents or records.

Returns

FilterableStatement object.

Return type

mysqlx.FilterableStatement

Deprecated since version 8.0.12.

class mysqlx.WriteStatement(target: DatabaseTargetType, doc_based: bool)

Bases: Statement

Provide common write operation attributes.

property changed: bool

True if this statement has changes.

Type

bool

property deallocate_prepare_execute: bool

True to deallocate + prepare + execute statement.

Type

bool

property exec_counter: int

The number of times this statement was executed.

Type

int

execute() -> Any

Execute the statement.

Raises

NotImplementedError -- This method must be implemented.

get_values() -> List[int | str | DbDoc | Dict[str, Any] | List[str | int | float | ExprParser | Dict[str, Any] | None]]

Returns the list of values.

Returns

The list of values.

Return type

list

increment_exec_counter() -> None

Increments the number of times this statement has been executed.

is_doc_based() -> bool

Check if it is document based.

Returns

True if it is document based.

Return type

bool

property prepared: bool

True if this statement has been prepared.

Type

bool

property repeated: bool

True if this statement was executed more than once.

Type

bool

reset_exec_counter() -> None

Resets the number of times this statement has been executed.

property schema: SchemaType

The Schema object.

Type

mysqlx.Schema

property stmt_id: int

Returns this statement ID.

Returns

The statement ID.

Return type

int

property target: DatabaseTargetType

The database object target.

Type

object