MYSQLX...ERENCE(1)
💖 MySQL Connector/Python X DevAPI Reference 💖
okay bestie, welcome to MYSQLX...ERENCE(1) — this is the manual page for the MySQL Connector/Python X DevAPI Reference Documentation, and no cap, it's a whole journey. Buckle up. 🎀
✨ NAME ✨
mysqlxconnectorpythondevapireference — MySQL Connector/Python X DevAPI Reference Documentation. 💅
The Connector/Python provides a client library for the X DevAPI protocol that was introduced in MySQL 8.0. periodt.
MySQL 8.0 is an open-source relational database that is secure, high performing, and easy to use. The X DevAPI supports relational tables and JSON documents, making it possible to use both tables and collections at the same time. it's giving range, honestly.
For general information about the X DevAPI, please refer to documentation on http://dev.mysql.com/doc/x-devapi-userguide/en/ — that's the homework, bestie, go read it. 📚
Contents:
📜 LICENSE 📜
Copyright (c) 2016, 2023, Oracle and/or its affiliates. All rights reserved. (yes, all of them. every last one.)
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License, version 2.0, as published by the Free Software Foundation.
This program is also distributed with certain software (including but not limited to OpenSSL) that is licensed under separate terms, as designated in a particular file or component or in included license documentation. The authors of MySQL hereby grant you an additional permission to link the program and your derivative works with the separately licensed software that they have included with MySQL.
Without limiting anything contained in the foregoing, this file, which is part of MySQL Connector/Python, is also subject to the Universal FOSS Exception, version 1.0, a copy of which can be found at http://oss.oracle.com/licenses/universal-foss-exception. (that little dash before the URL is a soft hyphen situation, iykyk)
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License, version 2.0, for more details. 🫶
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
bestie, the all-caps warranty stuff is not me being dramatic, that's literally how it's written and it genuinely means it. no warranty. fr fr. 💀
✅ REQUIREMENTS ✅
the vibe check you must pass before any of this works:
- MySQL 8.0.0 or higher, with the X Plugin enabled ✨
- Python >= 3.7 🐍
- Protobuf C++ (version
>= 3.11.0, <= 3.19.6) - Python Protobuf (version
>= 3.11.0, <= 3.20.3) - dnspython (version
>= 1.16.0, <= 2.3.0) for DNS SRV support - lz4 (version
>= 2.1.6, <= 4.3.2) for connection compression support - zstandard (version
>= 0.12.0, <= 0.19.0) for connection compression support - gssapi (version
gssapi >= 1.6.9, <= 1.8.2) for Kerberos and LDAP SASL authentication support
note the version ranges, bestie — they're ranges, both ends, don't just install whatever's newest and cry about it later. 😭
📦 INSTALLATION 📦
Packages are available at the Connector/Python download site. For some packaging formats, there are different packages for different versions of Python; choose the one appropriate for the version of Python installed on your system. 👑
💖 Installing Connector/Python with pip 💖
This is the recommended way to install Connector/Python. slay.
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-pythonone line. one command. iconic. ✨
🪟 Installing Connector/Python on Microsoft Windows Using an MSI Package 🪟
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. (yes, the location of your choosing. you're the main character here.)
🐧 Installing Connector/Python on Linux Using the MySQL Yum Repository 🐧
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-releaseThen install Connector/Python as follows:
shell> sudo yum install mysql-connector-pythonupdate first, install second. that order. no skipping. 💅
📦 Installing Connector/Python on Linux Using an RPM Package 📦
To install a Connector/Python RPM package (denoted here as PACKAGE.rpm), use this command:
shell> rpm -i PACKAGE.rpm🍓 Installing Connector/Python on Linux Using a Debian Package 🍓
To install a Connector/Python Debian package (denoted here as PACKAGE.deb), use this command:
shell> dpkg -i PACKAGE.deb🍎 Installing Connector/Python on OS X Using a Disk Image 🍎
Download the .dmg file and install Connector/Python by opening it and double clicking the resulting .pkg file. double click. that's the whole trick. 🎀
🛠️ Installing Connector/Python from source 🛠️
🔍 Prerequisites 🔍
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
okay so building from source is the "I do my own nails" of installation. beautiful results, but you need the tools first. 💅
🧚 Installing Connector/Python from source on Unix and Unix-Like Systems 🧚
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(yes, the cd example literally says to cd into the .tar.gz file. that's what the page says, bestie. I'm not touching it. I report, I don't rewrite. 🙈)
🎓 TUTORIALS 🎓
🌸 Getting started 🌸
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. full SQL too?? she contains multitudes.
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. case-insensitive, so go off with your capitalization, honestly.
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:
REQUIREDis set by default.DISABLEDconnects to the MySQL Server without SSL.
If ssl-ca option is set, only the following SSL Modes are allowed:
VERIFY_CAvalidates the server Certificate with the CA Certificate.VERIFY_IDENTITYverifies 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. (platform-dependent socket timeout — meaning the vibes of your OS decide, not us.)
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: Theurllib.parse.quotefunction should be used to quote special characters for user and password when using a connection string in themysqlx.get_session()function.python from urllib.parse import quote session = mysqlx.get_session('mysqlx://root:{0}@localhost:33060?use-pure=true' ''.format(quote('pass?!#%@/')))
bestie, if your password has a ? or a # or a / in it and you don't quote it, the connection string will simply misunderstand you and that's a you problem. quote it. 💅
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. (preexisting! it has to already exist! don't come at the server with a schema it's never met.)
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'
})and that's where part 1 leaves us, bestie — chained objects, clean one-liners, schema in the URL. more coming. 🫶✨
NOTE: bestie, the default schema you pass in has to actually exist on the server already — otherwise it's gonna raise an error the second you connect. no negotiation. 💀
So the session just... uses that schema as its default schema, which you can pull back out with mysqlx.Session.get_default_schema(). And the glow-up? You can run SQL statements without spelling out the schema name every single time, which is so much nicer:
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'Next example incoming ✨ — here mysqlx.get_session() is doing the opening-a-session thing, and then we grab a reference to the test schema and build a collection using mysqlx.Schema.create_collection(), which lives on 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')Okay so the next step would be running CRUD operations on a collection that belongs to a particular schema. Once you've got your mysqlx.Schema object, hit up mysqlx.Schema.get_collection() and you get a reference to the collection — and on that you can do the fun stuff 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())And!!! parameter binding is also available as a chained method on each of the CRUD operations. The way it works: you drop a placeholder string with a : in front of it, then bind it with the bind() method. it's giving elegant, honestly. 💅
my_coll = db.get_collection('my_collection')
my_coll.remove('name = :data').bind('data', 'Sakila').execute()🌈 Resolving DNS SRV records 🌈
If you're running a DNS server with one of those service-discovery utilities that supports mapping SRV records, you can use the mysqlx+srv scheme or the dns-srv connection option — and Connector/Python will automatically resolve all the available server addresses those SRV records describe. no manual labour required, we love that for us. 🦋
NOTE: MySQL host configuration using DNS SRV requires the dnspython module. like, requires. 🔥
session = mysqlx.get_session('mysqlx://root:@foo.abc.com')
# or
session = mysqlx.get_session({
'host': 'foo.abc.com',
'user': 'root',
'password': '',
'dns-srv': True
})So for instance — say a DNS server at the foo.abc.com endpoint hands you the SRV records below. Then the servers would be in the following priority: foo2.abc.com, foo1.abc.com, foo3.abc.com, foo4.abc.com. (yes, in that order, read it again, it's real 👀)
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🌸 Specifying which TLS versions to use 🌸
You can pick exactly which TLS versions you want used during the connection while you're getting the session — that's the tls-versions option doing its thing. And as a lil bonus, the TLS ciphers can also be specified with the tls-ciphersuites option. security AND aesthetics? we stan. 👑
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()Out of the whole list of TLS versions you handed over, the highest supported version gets selected for the connection, and you get this as the result:
Mysqlx_ssl_version: TLSv1.2
Mysqlx_ssl_cipher: DHE-RSA-AES256-SHA🧚 Collections 🧚
Documents of the same type get grouped together and stored in the database as collections. That's the whole vibe. The X DevAPI uses Collection objects to store and retrieve documents. simple as that, fr fr. 💜
✨ Creating collections ✨
To create a brand new collection, call the mysqlx.Schema.create_collection() function from a mysqlx.Schema object. It hands you back a Collection object you can use immediately — like, right away, for example to insert documents into the collection. instant gratification. 🎀
Optionally, the argument reuse_existing can be set to True — this stops an error being generated if a collection with the same name already exists. no drama, no crash. 🫶
import mysqlx
# Connect to server on localhost
session = mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': 'mike',
'password': 's3cr3t!'
})
schema = session.get_schema('test')
# Create 'my_collection' in schema
schema.create_collection('my_collection', reuse_existing=True)💖 Schema validation 💖
Optionally, the argument validation can be set to create a server-side document validation schema. This argument should be a dict, and that dict 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. pick your fighter. 🔥
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)And if you try to insert a document that violates the schema definition for the collection? an error is thrown. periodt. 💀
🦋 Modifying collections 🦋
Want to enable a JSON schema validation on a collection that already exists (or update it if it's already there)? use the mysqlx.Schema.modify_collection() function. glow-up complete. ✨
# Using the same 'validation' dictionary used above, we can
# modify 'my_collection' to include a schema validation
schema.modify_collection('my_collection', validation=validation)🌈 Using Collection patch (mysqlx.ModifyStatement.patch()) 🌈
First things first — we need a session and a schema. obviously. 😌
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: create a sample collection and load it up with some sample data. (yes it's Game of Thrones, yes I'm obsessed, don't perceive me 🙈)
# 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 matching documents in a collection. In this case the new field name will be _is — with the value young for those documents whose age field is equal to or smaller than 21, and the value old for documents whose age field value is greater than 21. sorting the whole cast into vibes, honestly. 💅
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 next example shows how to add a new field with an array value. The code will add the field "parents" with the value ["Mace Tyrell", "Alerie Tyrell"] to documents whose family_name field has the value Tyrell. giving family tree, slay. 🌸
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"])And 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"})okay so this next one is giving surgical 💅 — it shows you how to remove the nested field Viserion out of the dragons field, and simultaneously how to update the value of the count field with a brand-new value that's built off the current one. chef's kiss.
NOTE: inmysqlx.ModifyStatement.patch(), all strings are considered literals — so if you want an expression, you have to reach formysqlx.expr(). no cap, that's the whole vibe check. 🙈
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 🎀
Connection Routers is a technique for connecting to one of multiple hosts using connection failover — which basically means: it tries to connect to the next endpoint if the current endpoint isn't available, and only then does it raise an error. iconic behaviour honestly. 🦋
To do it, define multiple hosts by specifying a URI-like string that contains multiple hosts, ports, and an optional priority — or use the routers option when you invoke mysqlx.get_client().
This is what lets the connector do automatic connection failover selection when an endpoint isn't available. When multiple endpoints are available, the server it picks for the session depends on the priority option. If a priority is set, it must be defined for each endpoint — no skipping bestie. The available endpoint with the highest priority gets prioritized first. If you don't specify one, a randomly available endpoint is used. 🎲
Here's how to specify multiple hosts in a URI-like string, without priority, when you call 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()You can also hand over the multiple hosts as a list, with the routers living 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 examples above have two hosts, but bestie you can define many more hosts and ports than that. And it's super important to understand: the MySQL user and password you supply (whether in the URI-like string or in the user and password options) applies to all of the possible endpoints. So the exact same MySQL account has to exist on every single one of them. 👑
NOTE: because of the failover, a connection attempt to establish a connection on all the given hosts will happen before an error is raised — so using theconnect_timeoutoption is recommended when a large number of hosts could be down. The order the connection attempts happen in is random unless thepriorityoption 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 looks like this:
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()And specifying multiple hosts with a priority in the connection settings looks like this:
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 thepriorityoption are values1to100, where100is the highest priority value. Priority determines the connection order, with the highest priority value going first. And heads up: if priority is given for one host, then a priority value must be given for all the hosts. no halfsies. ✨
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 the 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 the 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. math is mathing. 🧚
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: 612The following example shows using Multi-host and failover while using a pool. In this one, 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's gonna fail. However, that does not raise an error, and the connection attempt to the host "127.0.0.1", which is available, will succeed. But 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 PoolErrorThe 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 💜
Connection pooling is a technique of creating and managing a pool of connections that are ready for use — which greatly increases the performance of your applications by reducing the connection creation time. it's giving efficiency, it's giving glow up. ⭐
The way you use connection pooling in Connector/Python with the X Protocol is by calling the mysqlx.get_client() function like this:
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()And 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()okay so every session you make with mysqlx.Client.get_session()? it's a pooled connection, bestie. the moment you mysqlx.Session.close() it, it goes right back to the pool so it can get reused. recycling queen 💚♻️
and up until literally right now we've been raw-dogging our mysqlx.Client with zero config. we can actually set pooling options by passing a dict or a JSON document string as that second parameter. yup, position two. 👑
the available options for 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.
}
}so to turn pooling off in the client, we set the enabled option to False:
client = mysqlx.get_client(connection_str, {'pooling':{'enabled': False}})and to define the pool's maximum size we set max_size in the pooling options. in this next example 'max_size': 5 sets 5 as the maximum number of connections allowed in the pool. five and no more, periodt ✋
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()see that last one? it will raise a pool error. not a vibe. 😭
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 it means infinite. so like, forever. no cap.
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 that 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'}]so the 18 workers took random turns adding 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)(). teamwork honestly 🫶
🎀 Transactions 🎀
🌸 Savepoints 🌸
the SAVEPOINT statement sets a named transaction, allowing parts of a transaction to be rolled back before COMMIT. it's giving little time-travel checkpoints for your data. ⏳✨
💅 Get the collection object 💅
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")🌈 Set and rollback to a named transaction savepoint 🌈
a savepoint name can be provided to create a transaction savepoint, which can later be used to rollback. name it and claim it 💫
amysqlx.OperationalErrorwill be raised if the savepoint name is an invalid string, or if a nonexistent savepoint is being used inmysqlx.Session.rollback_to(). so watch out fr fr 😭
# 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()🦋 Set and rollback to an unnamed transaction savepoint 🦋
if a savepoint name is not provided, mysqlx.Session.release_savepoint() will return a generated savepoint name. it names itself, main character energy. 👑
# 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()💖 Releasing a transaction savepoint 💖
amysqlx.OperationalErrorwill be raised if a nonexistent savepoint is being used inmysqlx.Session.release_savepoint(). nonexistent savepoints are NOT the vibe 💀
# 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()✨ Creating Indexes ✨
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 describe the required arguments for the function and some examples of use. indexes are literally the glow-up your queries needed 🌟
🌟 Arguments for mysqlx.Collection.create_index() 🌟
to use 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. and in addition, for each member we need to specify the type of data that holds the field in the document and whether it is required or not. fields marked as required must appear on each document in the collection — no skips, no exceptions, periodt. 🎀
{"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) INDEXokay so that's the shape of it 💅 every member needs a field (the member_path, a str, required) and a type (also a str, and it has to be one of the valid ones, no improvising bestie). The rest is optional: required (either True or the default False), collation (a str, and only for the TEXT field type), plus options (an int) and srid (also an int), both of which are only for the GEOJSON field type. And you can stack as many members as you want in there. 🌸
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]DATETIMETIMESTAMPDATETIMETEXT[(length)]GEOJSON(extra options:options,srid)
⚠️ Note: the use of typeGEOJSONrequires the index type to be set toSPATIAL. No exceptions, bestie, it's giving mandatory. 💀
Using mysqlx.Collection.create_index()
First we need to get a session and a schema. Baby steps, we got this. 🦋
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. one field, one type, easy win.
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. no skipping them, they're mandatory now.
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 holds 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"})🙈 not me noticing the prose saysgeojson_namewhile the code literally passes"index_age"— I am keeping it exactly as written, character for character, because that is the whole assignment. 💅
Locking 🔒
Shared and Exclusive Locks
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. yes bestie, actual safety, we love to see it.
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.
Examples
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. Two clients, two whole personalities. 🫶
# 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()Locking with NOWAIT and SKIP_LOCKED
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. impatient queen behaviour, honestly valid.
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. bye to the rows that didn't want us, we move on.
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()Handling Connection Attributes (mysqlx.get_session()) 🎀
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. the server is literally keeping receipts on everyone, iykyk.
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, bestie — and you can hand them over either with the connection-attributes key or as a URL attribute while you're grabbing the connection. Like, for example:
import mysqlx
mysqlx.getSession('mysqlx://mike@localhost:33060/schema?connection-attributes=[my_attribute=some_value,foo=bar]')And on the server side? The connection attributes may look a lil something like this:
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. Do not try it. She will not allow it. 🙈
✨ REFERENCE ✨
💖 Connection 💖
mysqlx.get_client
mysqlx.get_client(connection_string: str | Dict[str, Any],
options_string: str | Dict[str, Any]) -> ClientCreates a Client instance with the provided connection data and settings. Iconic. 💅
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
mysqlx.get_session(*args: Any, **kwargs: Any) -> SessionCreates a Session instance using the provided connection data. no cap, that's literally the whole job.
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
mysqlx.Client
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. she's just a lil holder of vibes tbh.
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 aSessioninstance using the provided connection data.
Returns Session object.
Return type Session
mysqlx.Session
class mysqlx.Session(settings: Dict[str, Any])Bases: object
Enables interaction with a X Protocol enabled MySQL Product. main character energy fr fr 👑
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. (yes bestie, "underliying" — we're keeping it exactly how she wrote it 💀)commit() -> None— commits all the operations executed after a call tostartTransaction().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. gone. deleted. not coming back. 💀
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 aSchemaobject from the current session by the schema name configured in the connection settings.
Returns
- The
Schemaobject 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 aSchemaobject 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— returnsTrueif 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 tostartTransaction(). undo button, but make it dramatic.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 amysqlx.SqlStatementobject 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—Trueto use pure Python Protobuf implementation.
Type bool
🦋 CRUD 🦋
💜 mysqlx.Schema 💜
class mysqlx.Schema(session: SessionType, name: str)Bases: DatabaseObject
okay so this next bit is all about the Schema class — it's your client-side representation of a database schema, and it gives you access to all the schema contents. 💖
Parameters
session(mysqlx.XSession) — the Session object.name(str) — the Schema name.
am_i_real() -> Any
Checks if this object is actually real in the database. 👀
Returns True if the object exists in the database.
Return type bool
Raises NotImplementedError — this method must be implemented, bestie.
Deprecated since version 8.0.12 — use the exists_in_database() method instead. 💀
create_collection
create_collection(name: str, reuse_existing: bool = False,
validation: Dict[str, str | Dict] | None = None, **kwargs: Any)
-> CollectionCreates a new collection in the current schema with the name you picked, and hands you back an object that represents the brand-new collection. ✨
Parameters
name(str) — the name of the collection.reuse_existing(bool) — set toTrueto reuse an existing collection.validation(Optional[dict]) — a dict containing the keyslevel(the validation level) andschema(a dict or a string representation of a JSON schema specification).
Returns A Collection object.
Return type mysqlx.Collection
Raises
mysqlx.ProgrammingError— ifreuse_existingisFalseand the collection already exists, or if 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. Gone. Poof. 💅
Parameters
name(str) — the name of the collection to be dropped.
exists_in_database() -> bool
Checks if this object actually exists in the database.
Returns True if the object exists in the database.
Return type bool
get_collection
get_collection(name: str, check_existence: bool = False) ->
CollectionReturns the collection with the given name for this schema.
Returns A Collection object.
Return type mysqlx.Collection
get_collection_as_table
get_collection_as_table(name: str, check_existence: bool = False)
-> TableReturns a table object for the given collection. (Yes, it says "a a table object" in the original — we keep it real. 😭)
Returns A Table object.
Return type mysqlx.Table
get_collections() -> List[Collection]
Returns a list of all the collections in this schema.
Returns A 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 with the given name for this schema.
Returns A Table object.
Return type mysqlx.Table
get_tables() -> List[Table]
Returns a list of all the tables in this schema.
Returns A list of Table objects.
Return type list
get_view(name: str, check_existence: bool = False) -> View
Returns the view with the given name for this schema.
Returns A View object.
Return type mysqlx.View
modify_collection
modify_collection(name: str, validation: Dict[str, str | Dict] |
None = None) -> NoneModifies a collection using JSON schema validation.
Parameters
name(str) — the name of the collection.validation(Optional[dict]) — a dict containing the keyslevel(the validation level) andschema(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 the get_name() method instead.
mysqlx.Collection
class mysqlx.Collection(schema: Schema, name: str | bytes)Bases: DatabaseObject
Represents a collection of documents on a schema. 📚
Parameters
schema(mysqlx.Schema) — theSchemaobject.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 An 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 you provide.
Parameters
doc_id(str) — document ID.doc(mysqlx.DbDocordict) — new document.
am_i_real() -> Any
Checks if this object actually exists in the database.
Returns True if the object exists in the database.
Return type bool
Raises NotImplementedError — this method must be implemented.
Deprecated since version 8.0.12 — use the exists_in_database() method instead.
count() -> int
Counts the documents in the collection.
Returns The total number of documents in the collection.
Return type int
create_index
create_index(index_name: str, fields_desc: Dict[str, Any]) ->
CreateCollectionIndexStatementCreates a collection index.
Parameters
index_name(str) — index name.fields_desc(dict) — a dictionary containing the field members that constrain the index to be created. It must have the form shown in the following code block:
{"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
Checks if this object actually exists in the database.
Returns True if the object exists in the 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. like, you give it the ID, it gives you the doc. that's the whole thing 💅
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. yes bestie, the schema it lives in. that one.
Returns
The Schema object.
Return type
mysqlx.Schema
🌸 get_session() -> SessionType 🌸
Returns the session of this database object. your session, your vibe, your connection 💖
Returns
The Session object.
Return type
mysqlx.Session
🔥 modify(condition: str) -> ModifyStatement 🔥
Modifies documents based on the condition. you hand it a filter, it hands you back a ModifyStatement so you can go change stuff.
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. no more skipping it, bestie 😭
👑 property name: str 👑
The name of this database object.
Type str
💜 remove(condition: str) -> RemoveStatement 💜
Removes documents based on the condition. filter goes in, RemoveStatement comes out, documents go bye 🫶
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. fr fr, you have to pass it.
🎀 remove_one(doc_id: str) -> Result 🎀
Removes a Document matching the Document ID. one (1) document. singular. main character exit 🚪✨
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. out with the old, in with the new, no cap.
Parameters
doc_id(str) — Document IDdoc(mysqlx.DbDocordict) — 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. literally "who am i" but make it a method, iconic.
Returns
The name of this database object.
Return type
str
Deprecated since version 8.0.12: Use get_name() method instead. please bestie, use the new one 🙈
✨ mysqlx.Table ✨
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. like, all the classics are here 💖
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. how many rows? yes. all of them. 🧮
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. the connection underneath it all, the moment of truth ✨
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. vibe check on the object: is it a view or is it 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.View ✨
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. you can create 'em, alter 'em, drop 'em — the whole glow-up pipeline 💅
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:strproperty schema: Schema— theSchemaobject, bestie. Type:mysqlx.Schemaselect(*fields: str) -> SelectStatement- Creates a brand new
mysqlx.SelectStatementobject. ✨ - Parameters:
*fields— the fields to be retrieved. - Returns:
SelectStatementobject - Return type:
mysqlx.SelectStatement
- Creates a brand new
property session: SessionType— theSessionobject. Type:mysqlx.Sessionupdate() -> UpdateStatement- Creates a new
mysqlx.UpdateStatementobject. 💅 - Returns:
UpdateStatementobject - Return type:
mysqlx.UpdateStatement
- Creates a new
who_am_i() -> str- Returns the name of this database object. (yes, it's literally the identity check we love 🪞)
- Returns: the name of this database object.
- Return type:
str
🚨 Deprecated since version 8.0.12: use get_name() method instead. the old way is giving dusty energy, switch it up bestie.
🧚 mysqlx.expr 🧚
mysqlx.expr(string: str, allow_relational: bool = True) -> NoneExpression parser class. that's the whole vibe, it parses expressions fr fr.
🦋 Result 🦋
👑 mysqlx.Column 👑
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. it's the whole backstory of a column, like its lore 🎀
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:strproperty collation_name: str— the collation name. Added in version 8.0.12. Type:strproperty column_label: str— the column label. Added in version 8.0.12. Type:strproperty column_name: str— the column name. Added in version 8.0.12. Type:strproperty fractional_digits: int— the column fractional digits. Added in version 8.0.12. Type:intget_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
Trueif is bytes. - Returns: returns
Trueif is bytes. - Return type:
bool
- Returns
is_number_signed() -> bool- Returns
Trueif is a number signed. - Returns: returns
Trueif is a number signed. - Return type:
bool
- Returns
is_padded() -> bool | int- Returns
Trueif is padded. - Returns: returns
Trueif is padded. - Return type:
bool
- Returns
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:strproperty table_label: str— the table label. Added in version 8.0.12. Type:strproperty table_name: str— the table name. Added in version 8.0.12. Type:strproperty type: int— the column type. Added in version 8.0.12. Type:int
🌸 mysqlx.Row 🌸
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. one whole row of the tea ☕
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. it's giving legacy, ngl.
💜 mysqlx.Result 💜
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. so like, the receipts for everything that wasn't a plain query.
Parameters
connection(mysqlx.connection.Connection) — theConnectionobject.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.
🫶 mysqlx.BufferingResult 🫶
class mysqlx.BufferingResult(connection: ConnectionType)Bases: BaseResult
Provides base functionality for buffering result objects. it's the blueprint, the template, the moment.
Parameters
connection(mysqlx.connection.Connection) — theConnectionobject.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]— grab everything, no crumbs left.
Returns: the list of items of mysqlx.DbDoc or mysqlx.Row.
Return type: list
fetch_one() -> Row | DbDoc | None— just one item, one single lil guy.
Returns: one result item.
Return type: mysqlx.Row or mysqlx.DbDoc
get_affected_items_count() -> int— how many items the last operation touched, bestie.
Returns: the number of affected items.
Return type: int
get_warnings() -> List[Dict[str, int | str]]— hands you the warnings.
Returns: the list of warnings.
Return type: list
get_warnings_count() -> int— how many warnings there are.
Returns: the number of warnings.
Return type: int
index_of(col_name: str) -> int— gives you the index of the column.
Returns: the index of the column.
Return type: int
set_closed(flag: bool) -> None— sets whether the 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 whether the result has data.
Parameters:
flag(bool) —Trueif result has data.
set_has_more_results(flag: bool) -> None— sets whether there are more results.
Parameters:
flag(bool) —Trueif there are more results.
set_rows_affected(total: int) -> None— sets the number of rows affected.
🦋 mysqlx.RowResult 🦋
class mysqlx.RowResult(connection: ConnectionType)Bases: BufferingResult
It lets you traverse the Row objects that a Table.select operation hands back. It's giving "walk through your results one by one" energy ✨
Parameters:
connection(mysqlx.connection.Connection) — theConnectionobject.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) —Trueif result has data.
set_has_more_results(flag: bool) -> None— sets if has more results.
Parameters:
flag(bool) —Trueif has more results.
set_rows_affected(total: int) -> None— sets the number of rows affected.
🌈 mysqlx.SqlResult 🌈
class mysqlx.SqlResult(connection: ConnectionType)Bases: RowResult
It represents a result from a SQL statement. SQL girlies rise up 💅
Parameters:
connection(mysqlx.connection.Connection) — theConnectionobject.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— returnsTrueif 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) —Trueif result has data.
set_has_more_results(flag: bool) -> None— sets if has more results.
Parameters:
flag(bool) —Trueif has more results.
set_rows_affected(total: int) -> None— sets the number of rows affected.
👑 Statement 👑
(that's the whole heading, bestie — nothing else under it in this bit 🫶)
🍓 mysqlx.DbDoc 🍓
class mysqlx.DbDoc(value: str | Dict[str, Any])Bases: object
It represents a generic document in JSON format. JSON but make it a class ✨
Parameters:
value(object) — the value can be a JSON string or a dict.
Raises ValueError — if value type is not a basestring or dict. so don't try to be cute with the wrong type, fr fr 😭
as_str() -> str— serializemysqlx.DbDocto a JSON formattedstr.
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 amysqlx.DbDocobject containing thedoc_idprovided. Ifdoc_idis not provided, it will be removed from the newmysqlx.DbDocobject.
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.
Return type : list
✨ mysqlx.Statement ✨
class mysqlx.Statement(target: DatabaseTargetType, doc_based: bool = True)Bestie, this is the base class — the blueprint, the blueprint-est blueprint 💅. Bases: object.
It hands down all the base functionality to statement objects, and literally every other statement type inherits from it, so it's giving main character energy for real.
Parameters 🫶
target(object) — The target database object, it can bemysqlx.Collectionormysqlx.Table.doc_based(bool) —Trueif it is document based.
changed 🦋
property changed: boolTrue if this statement has changes.
Type: bool
deallocate_prepare_execute 💫
property deallocate_prepare_execute: boolTrue to deallocate + prepare + execute statement.
Type: bool
exec_counter 👑
property exec_counter: intThe number of times this statement was executed.
Type: int
execute() 🔥
execute() -> AnyExecute the statement.
Raises NotImplementedError — This method must be implemented. (Yes, it's a base class thing, the subclass has to do the actual work. No cap.)
increment_exec_counter() ⭐
increment_exec_counter() -> NoneIncrements the number of times this statement has been executed.
is_doc_based() 🌸
is_doc_based() -> boolCheck if it is document based.
Returns: True if it is document based.
Return type: bool
prepared 🎀
property prepared: boolTrue if this statement has been prepared.
Type: bool
repeated 🌈
property repeated: boolTrue if this statement was executed more than once.
Type: bool
reset_exec_counter() 💖
reset_exec_counter() -> NoneResets the number of times this statement has been executed.
schema 🧚
property schema: SchemaTypeThe Schema object.
Type: mysqlx.Schema
stmt_id 💜
property stmt_id: intReturns this statement ID.
Returns: The statement ID.
Return type: int
target 🍓
property target: DatabaseTargetTypeThe database object target.
Type: object
✨ mysqlx.FilterableStatement ✨
class mysqlx.FilterableStatement(target: DatabaseTargetType, doc_based: bool = True, condition: str | None = None)Bases: Statement
Okay so this one is a statement to be used with filterable statements — like, the ones where you get to narrow stuff down with conditions and sorting and limits. It's the "I want these specific things" energy 😌.
Parameters 💫
target(object) — The target database object, it can bemysqlx.Collectionormysqlx.Table.doc_based(Optional[bool]) —Trueif it is document based (default:True).condition(Optional[str]) — Sets the search condition to filter documents or records.
bind() 🪢
bind(*args: Any) -> FilterableStatementBinds 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. So like, count your args, bestie 🙈.
changed 🦋
property changed: boolTrue if this statement has changes.
Type: bool
deallocate_prepare_execute 💫
property deallocate_prepare_execute: boolTrue to deallocate + prepare + execute statement.
Type: bool
exec_counter 👑
property exec_counter: intThe number of times this statement was executed.
Type: int
execute() 🔥
execute() -> AnyExecute the statement.
Raises NotImplementedError — This method must be implemented.
get_binding_map() 🗺️
get_binding_map() -> Dict[str, Any]Returns the binding map dictionary.
Returns: The binding map dictionary.
Return type: dict
get_bindings() 📋
get_bindings() -> Dict[str, Any] | ListReturns the bindings list.
Returns: The bindings list.
Return type: list
get_grouping() 🎯
get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]Returns the grouping expression list.
Returns: The grouping expression list.
Return type: list
get_having() 🫶
get_having() -> MessageTypeReturns the having expression.
Returns: The having expression.
Return type: object
get_limit_offset() 📏
get_limit_offset() -> intReturns the limit offset.
Returns: The limit offset.
Return type: int
get_limit_row_count() 🔢
get_limit_row_count() -> intReturns the limit row count.
Returns: The limit row count.
Return type: int
get_projection_expr() 🌟
get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]Returns the projection expression.
Returns: The projection expression.
Return type: object
get_sort_expr() 🔀
get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]Returns the sort expression.
Returns: The sort expression.
Return type: object
get_where_expr() 🧭
get_where_expr() -> MessageTypeReturns the where expression.
Returns: The where expression.
Return type: object
increment_exec_counter() ⭐
increment_exec_counter() -> NoneIncrements the number of times this statement has been executed.
is_doc_based() 🌸
is_doc_based() -> boolCheck if it is document based.
Returns: True if it is document based.
Return type: bool
limit() 🛑
limit(row_count: int, offset: int | None = None) -> FilterableStatementSets the maximum number of items to be returned.
Parameters: row_count (int) — The maximum number of items.
Returns: FilterableStatement object.
Return type: mysqlx.FilterableStatement
RaisesValueError— Ifrow_countis not a positive integer.
Changed in version 8.0.12: The usage ofoffsetwas deprecated. Not me mourning theoffsetparam 😭, it's still there, it's just deprecated, periodt.
offset() ⏭️
offset(offset: int) -> FilterableStatementSets the number of items to skip.
Parameters: offset (int) — The number of items to skip.
Returns: FilterableStatement object.
Return type: mysqlx.FilterableStatement
RaisesValueError— Ifoffsetis not a positive integer.
Added in version 8.0.12. Fresh drop ✨
prepared 🎀
property prepared: boolTrue if this statement has been prepared.
Type: bool
repeated 🌈
property repeated: boolTrue if this statement was executed more than once.
Type: bool
reset_exec_counter() 💖
reset_exec_counter() -> NoneResets the number of times this statement has been executed.
schema 🧚
property schema: SchemaTypeThe Schema object.
Type: mysqlx.Schema
sort() 🎶
sort(*clauses: str) -> FilterableStatementSets 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. Yeah bestie, it's deprecated, use the newer thing, iykyk.
stmt_id 💜
property stmt_id: intReturns this statement ID.
Returns: The statement ID.
Return type: int
target 🍓
property target: DatabaseTargetTypeThe database object target.
Type: object
where() 🕵️
where(condition: str) -> FilterableStatementSets 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. Again, deprecated, no cap.
✨ mysqlx.SqlStatement ✨
class mysqlx.SqlStatement(connection: ConnectionType, sql: str)Bases: Statement
A statement for SQL execution 💅. This is the one where you just... write SQL. Iconic.
Parameters 🫶
connection(mysqlx.connection.Connection) — Connection object.sql(string) — The sql statement to be executed.
bind() 🪢
bind(*args: Any) -> SqlStatementBinds value(s) to a specific placeholder(s).
Parameters: *args — The value(s) to bind.
Returns: SqlStatement object.
Return type: mysqlx.SqlStatement
changed 🦋
property changed: boolTrue if this statement has changes.
Type: bool
deallocate_prepare_execute 💫
property deallocate_prepare_execute: boolTrue to deallocate + prepare + execute statement.
Type: bool
exec_counter 👑
property exec_counter: intThe number of times this statement was executed.
Type: int
execute() 🔥
execute() -> SqlResultExecute the statement.
Returns: SqlResult object.
Return type: mysqlx.SqlResult
get_binding_map() 🗺️
get_binding_map() -> Dict[str, Any]Returns the binding map dictionary.
- Returns — the binding map dictionary. 💖
- Return type —
dict
get_bindings()→Tuple | List— hands you back the bindings list, bestie. no notes.- Returns — the bindings list.
- Return type —
list
increment_exec_counter()→None— bumps up the number of times this statement has been executed. that's the whole job 🫶is_doc_based()→bool— vibe check: is it document based?- Returns —
Trueif it is document based. - Return type —
bool
- Returns —
prepared(property,bool) —Trueif this statement has been prepared.repeated(property,bool) —Trueif this statement was executed more than once.reset_exec_counter()→None— resets the number of times this statement has been executed. clean slate, we love a fresh start ✨schema(property,SchemaType) — the Schema object. its type ismysqlx.Schema.sql(property,str) — the SQL text statement. type isstring.stmt_id(property,int) — returns this statement ID.- Returns — the statement ID.
- Return type —
int
target(property,DatabaseTargetType) — the database object target. type isobject.
mysqlx.FindStatement 🦋
class mysqlx.FindStatement(collection: DatabaseTargetType, condition: str | None = None)Bases: ReadStatement
okay so this one is literally a statement document selection on a Collection. it's giving "go find me some docs" energy and honestly? iconic.
Parameters
collection(mysqlx.Collection) — the Collection object.condition(Optional[str]) — an optional expression to identify the documents to be retrieved. if you don't specify it, 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. amysqlx.DbDocobject or a JSON string representation can be used. - Returns —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises —
ProgrammingErrorif the number of arguments is invalid.
- Parameters:
changed(property,bool) —Trueif this statement has changes.deallocate_prepare_execute(property,bool) —Trueto deallocate + prepare + execute statement.exec_counter(property,int) — the number of times this statement was executed.execute()→DocResult | RowResult— execute the statement. go go go 🏃♀️- Returns —
Resultobject. - Return type —
mysqlx.Result
- Returns —
fields(*fields: str)→FindStatement— sets a document field filter.- Parameters:
*fields— the string expressions identifying the fields to be extracted. - Returns —
FindStatementobject. - Return type —
mysqlx.FindStatement
- Parameters:
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 —
ReadStatementobject. - Return type —
mysqlx.ReadStatement
- Parameters:
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 —
ReadStatementobject. - Return type —
mysqlx.ReadStatement
- Parameters:
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 —
Trueif it is document based. - Return type —
bool
- Returns —
is_lock_exclusive()→bool— returnsTrueif is EXCLUSIVE LOCK.- Returns —
Trueif is EXCLUSIVE LOCK. - Return type —
bool
- Returns —
is_lock_shared()→bool— returnsTrueif is SHARED LOCK.- Returns —
Trueif is SHARED LOCK. - Return type —
bool
- Returns —
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 —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises —
ValueErrorifrow_countis not a positive integer.
- Parameters:
Changed in version 8.0.12: the usage of offset was deprecated. so like… still there, but lowkey retired 💀
lock_contention(property,LockContention) — the lock contention value. its type ismysqlx.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.
- Parameters:
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.
- Parameters:
offset(offset: int)→FilterableStatement— sets the number of items to skip.- Parameters:
offset(int) — the number of items to skip. - Returns —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises —
ValueErrorifoffsetis not a positive integer.
- Parameters:
Added in version 8.0.12. ✨
prepared(property,bool) —Trueif this statement has been prepared.repeated(property,bool) —Trueif this statement was executed more than once.reset_exec_counter()→None— resets the number of times this statement has been executed.schema(property,SchemaType) — the Schema object. its type ismysqlx.Schema.sort(*clauses: str)→FindStatement— sets the sorting criteria.- Parameters:
*clauses— the expression strings defining the sort criteria. - Returns —
FindStatementobject. - Return type —
mysqlx.FindStatement
- Parameters:
stmt_id(property,int) — returns this statement ID.- Returns — the statement ID.
- Return type —
int
target(property,DatabaseTargetType) — the database object target. type isobject.
where(condition: str) -> FilterableStatement
Sets the search condition to filter. It's the "okay but WHICH ones tho" move 💅
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. She's retired bestie, don't build new stuff on her 😭
🌸 mysqlx.AddStatement 🌸
class mysqlx.AddStatement(collection: DatabaseTargetType)Bases: WriteStatement
A statement for document addition on a collection. Basically: you have a collection, you want to yeet new docs into it, this is the girl for the job 🫶
Parameters
collection(mysqlx.Collection) — TheCollectionobject.
add(*values: DbDoc) -> AddStatement
Adds a list of documents into a collection. Multiple at once?? we love efficiency ✨
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. The main character moment 👑
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. Fresh start energy 🌸
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 upsert flag to the boolean of the value provided. Setting of this flag allows updating of the matched rows/documents with the provided value. it's giving "insert but make it also update" 💅
Parameters
value(optional[bool]) — Set or unset the upsert flag.
🦋 mysqlx.RemoveStatement 🦋
class mysqlx.RemoveStatement(collection: DatabaseTargetType, condition: str)Bases: FilterableStatement
A statement for document removal from a collection. The "and now you're GONE" era 💀
Parameters
collection(mysqlx.Collection) — TheCollectionobject.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). Slots the value in where the placeholder lives 🎀
Parameters
*args— The name of the placeholder and the value to bind. Amysqlx.DbDocobject 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.
No condition, no execution. She will absolutely call you out 😭
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. Capping the results, respectfully ✋
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. The "skip the first few, bestie" move 🫶
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. Fresh start energy 🌸
property schema: SchemaType
The Schema object.
Type: mysqlx.Schema
sort(*clauses: str) -> RemoveStatement
Sets the sorting criteria. Organizing before deleting, because chaos is not the vibe 🎀
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. Yes bestie, same retirement party as before 💀
🌸 mysqlx.ModifyStatement 🌸
class mysqlx.ModifyStatement(collection: DatabaseTargetType, condition: str)
Bases: FilterableStatement
Okay so bestie, this is your go-to statement for document update operations on a Collection. Like, it's literally the queen of modifying docs. 👑
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. No more skipping it, bestie. 💅
array_append(doc_path: str, value: Any) -> ModifyStatement
Inserts a value into a specific position in an array attribute in documents of a collection. It's giving precise placement energy. 🎯
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. Basically the same vibe as array_append but with a field param name. Lowkey iconic. ✨
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). Like, you're holding space for your variables. 🫶
Parameters:
*args— The name of the placeholder and the value to bind. Amysqlx.DbDocobject 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. It's giving "if you're there, you're getting updated". 💁♀️
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. So maybe don't use this one, bestie. 😭
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. The moment of truth. 🚀
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. Keep that counter going up, up, up. 📈
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. Like a glow-up for your docs. 💖
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.
✨ mysqlx.SelectStatement ✨
class mysqlx.SelectStatement(table: DatabaseTargetType, *fields: str)Bases: ReadStatement
A statement for record retrieval operations on a Table. It's giving "let me fetch these records, bestie" energy. 💅
Parameters
table(mysqlx.Table) — TheTableobject.*fields— The fields to be retrieved.
✨ bind(*args: Any) -> FilterableStatement ✨
Binds value(s) to a specific placeholder(s). No cap, it's the glue for your placeholders. 🫶
Parameters
*args— The name of the placeholder and the value to bind. Amysqlx.DbDocobject 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 aggregate function operations.
Parameters
condition(string) — A condition on the aggregate 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
🌈 mysqlx.InsertStatement 🌈
class mysqlx.InsertStatement(table: DatabaseTargetType, *fields: Any)Bases: WriteStatement
A statement for insert operations on Table.
Parameters
table(mysqlx.Table) — TheTableobject.*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
Bumps up the count of how many times this statement has been run. Bestie, it's just keeping score. 💅
🦋 is_doc_based() -> bool
Checks if it's document based, okay?
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. Not me hitting execute twice... 🙈
Type — bool
🎀 reset_exec_counter() -> None
Resets the number of times this statement has been executed. Back to zero, clean slate, main character reset. ✨
🌈 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. Give it the goods. 💖
Parameters
*values— The values of the columns to be inserted.
Returns — InsertStatement object.
Return type — mysqlx.InsertStatement
🗑️ mysqlx.DeleteStatement 🗑️
class mysqlx.DeleteStatement(table: DatabaseTargetType)Bases: FilterableStatement
A statement that drops a table.
Parameters
table(mysqlx.Table) — TheTableobject.
Changed in version 8.0.12: The condition parameter was removed. ✂️
bind(*args: Any) -> FilterableStatement
Binds value(s) to a specific placeholder(s). Match 'em up, bestie. 🎯
Parameters
*args— The name of the placeholder and the value to bind. Amysqlx.DbDocobject 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. Send it, periodt. 🔥
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. Cap it, bestie. 🧢
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. Skipping like it's a boring story. 💀
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. It's giving "old news", bestie. 🫠
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
🔄 mysqlx.UpdateStatement 🔄
class mysqlx.UpdateStatement(table: DatabaseTargetType)Bases: FilterableStatement
A statement for record update operations on a Table. Glow-up for your rows. 💅✨
Parameters
table(mysqlx.Table) — TheTableobject.
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. Amysqlx.DbDocobject 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]— hands you the grouping expression list, bestie.- Returns — the grouping expression list.
- Return type —
list
get_having() -> MessageType— gives you the having expression. yes, that having.- Returns — the having expression.
- Return type —
object
get_limit_offset() -> int— returns the limit offset, no cap.- Returns — the limit offset.
- Return type —
int
get_limit_row_count() -> int— returns the limit row count, slay.- Returns — the limit row count.
- Return type —
int
get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]— returns the projection expression. the way it just… projects.- 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. where, queen.- Returns — the where expression.
- Return type —
object
increment_exec_counter() -> None— bumps up the number of times this statement has been executed. one more time for the girls ✨is_doc_based() -> bool— vibe check: is it document based?- Returns —
Trueif it is document based. - Return type —
bool
- Returns —
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 —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises
ValueError— ifrow_countis not a positive integer. - Changed in version 8.0.12: the usage of
offsetwas deprecated. it's giving legacy, we don't use it anymore bestie
- Parameters —
offset(offset: int) -> FilterableStatement— sets the number of items to skip.- Parameters —
offset(int) — the number of items to skip. - Returns —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises
ValueError— ifoffsetis not a positive integer. - Added in version 8.0.12.
- Parameters —
order_by(*clauses: str) -> UpdateStatement— sets the order by criteria.- Parameters —
*clauses— the expression strings defining the order by criteria. - Returns —
UpdateStatementobject. - Return type —
mysqlx.UpdateStatement
- Parameters —
property prepared: bool—Trueif this statement has been prepared. prepared and unbothered 💅- Type —
bool
- Type —
property repeated: bool—Trueif this statement was executed more than once. she's a repeat offender fr fr- Type —
bool
- Type —
reset_exec_counter() -> None— resets the number of times this statement has been executed. clean slate era 🧚property schema: SchemaType— theSchemaobject.- Type —
mysqlx.Schema
- Type —
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 —
UpdateStatementobject. - Return type —
mysqlx.UpdateStatement
- Parameters
sort(*clauses: str) -> FilterableStatement— sets the sorting criteria.- Parameters —
*clauses— the expression strings defining the sort criteria. - Returns —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Deprecated since version 8.0.12. okay so we're not doing this one anymore, it's giving old news
- Parameters —
property stmt_id: int— returns this statement ID.- Returns — the statement ID.
- Return type —
int
property target: DatabaseTargetType— the database object target.- Type —
object
- Type —
where(condition: str) -> UpdateStatement— sets the search condition to filter.- Parameters —
condition(str) — sets the search condition to filter records. - Returns —
UpdateStatementobject. - Return type —
mysqlx.UpdateStatement
- Parameters —
🌈 mysqlx.CreateCollectionIndexStatement 🌈
class mysqlx.CreateCollectionIndexStatement(collection: DatabaseTargetType, index_name: str, index_desc: Dict[str, Any])Bases: Statement
A statement that creates an index on a collection. index-building main character energy 👑
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—Trueif this statement has changes. growth mindset honestly- Type —
bool
- Type —
property deallocate_prepare_execute: bool—Trueto deallocate + prepare + execute statement.- Type —
bool
- Type —
property exec_counter: int— the number of times this statement was executed. we're keeping count, periodt- Type —
int
- Type —
execute() -> Result— execute the statement. go go go! 🔥- Returns —
Resultobject. - Return type —
mysqlx.Result
- Returns —
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 —
Trueif it is document based. - Return type —
bool
- Returns —
property prepared: bool—Trueif this statement has been prepared.- Type —
bool
- Type —
property repeated: bool—Trueif this statement was executed more than once.- Type —
bool
- Type —
reset_exec_counter() -> None— resets the number of times this statement has been executed.property schema: SchemaType— theSchemaobject.- Type —
mysqlx.Schema
- Type —
property stmt_id: int— returns this statement ID.- Returns — the statement ID.
- Return type —
int
property target: DatabaseTargetType— the database object target.- Type —
object
- Type —
🦋 mysqlx.ReadStatement 🦋
class mysqlx.ReadStatement(target: DatabaseTargetType, doc_based: bool = True, condition: str | None = None)Bases: FilterableStatement
Provide base functionality for Read operations the blueprint, the moment, the vibe 📖
Parameters
target(object) — the target database object, it can bemysqlx.Collectionormysqlx.Table.doc_based(Optional[bool]) —Trueif 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. Amysqlx.DbDocobject or a JSON string representation can be used. - Returns —
FilterableStatementobject. - Return type —
mysqlx.FilterableStatement - Raises
ProgrammingError— if the number of arguments is invalid. bestie the argument count is NOT mathing 😭
- Parameters —
property changed: bool—Trueif this statement has changes.- Type —
bool
- Type —
property deallocate_prepare_execute: bool—Trueto deallocate + prepare + execute statement.- Type —
bool
- Type —
property exec_counter: int— the number of times this statement was executed.- Type —
int
- Type —
execute() -> DocResult | RowResult— execute the statement.- Returns —
Resultobject.
- Returns —
get_binding_map() -> Dict[str, Any]
okay so this one literally hands you the binding map dictionary back, bestie. no cap. 🫶
Returns: The binding map dictionary.
Return type: dict
get_bindings() -> Dict[str, Any] | List
Returns the bindings list. it's giving list energy. ✨
Returns: The bindings list.
Return type: list
get_grouping() -> List[ProtobufMessageType | ProtobufMessageCextType]
Returns the grouping expression list. lowkey a whole list of grouping expressions. 💅
Returns: The grouping expression list.
Return type: list
get_having() -> MessageType
Returns the having expression. the having vibe check. 🌸
Returns: The having expression.
Return type: object
get_limit_offset() -> int
Returns the limit offset. just the number, periodt. 🔢
Returns: The limit offset.
Return type: int
get_limit_row_count() -> int
Returns the limit row count. the row count is right there. 👑
Returns: The limit row count.
Return type: int
get_projection_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]
Returns the projection expression. it's giving projection realness. 🦋
Returns: The projection expression.
Return type: object
get_sort_expr() -> List[ProtobufMessageType | ProtobufMessageCextType]
Returns the sort expression. sorting? we love to see it. 💖
Returns: The sort expression.
Return type: object
get_where_expr() -> MessageType
Returns the where expression. the where clause but make it expression. 😭
Returns: The where expression.
Return type: object
group_by(*fields: str) -> ReadStatement
Sets a grouping criteria for the resultset. grouping? iconic. 💅
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. (yes, the original says "agregate" with one g, we're keeping it exactly, no cap.) 🫶
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. just keeping count, bestie. 🔥
is_doc_based() -> bool
Check if it is document based. returns True 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. exclusive lock energy. 👑
Returns: True if is EXCLUSIVE LOCK.
Return type: bool
is_lock_shared() -> bool
Returns True if is SHARED LOCK. shared lock? we love sharing. 🌈
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. limit? yes. 🎀
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. it's giving contention. 💅
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. (the original has a weird line break: "Only one lock can be\n active at a time." we're keeping the meaning exactly.) 🔒
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. sharing is caring but only one lock at a time, bestie. 🫶
Parameters:
- lock_contention (
mysqlx.LockContention) -- Lock contention.
offset(offset: int) -> FilterableStatement
Sets the number of items to skip. skip? okay. 🦋
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. prepared? we stay ready. 👑
Type: bool
property repeated: bool
True if this statement was executed more than once. repeated? it's giving encore. 🔁
Type: bool
reset_exec_counter() -> None
Resets the number of times this statement has been executed. back to zero, periodt. 💅
property schema: SchemaType
The Schema object. schema realness. 🌸
Type: mysqlx.Schema
sort(*clauses: str) -> FilterableStatement
Sets the sorting criteria. sorting? we love a good sort. ✨
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. the ID, bestie. 🔢
Returns: The statement ID.
Return type: int
property target: DatabaseTargetType
The database object target. target acquired. 🎯
Type: object
where(condition: str) -> FilterableStatement
Sets the search condition to filter. where? we 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.
mysqlx.WriteStatement
class mysqlx.WriteStatement(target: DatabaseTargetType, doc_based: bool)Bases: Statement
Provide common write operation attributes. 💖
property changed: bool
True if this statement has changes. changed? we love growth. 🦋
Type: bool
property deallocate_prepare_execute: bool
True to deallocate + prepare + execute statement. the whole pipeline, bestie. 💅
Type: bool
property exec_counter: int
The number of times this statement was executed. count it up. 🔢
Type: int
execute() -> Any
Execute the statement. just do it. ✨
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. values? yes. 💖
Returns: The list of values.
Return type: list
increment_exec_counter() -> None
Increments the number of times this statement has been executed. same energy as before. 🔥
is_doc_based() -> bool
Check if it is document based. returns True 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. ready. 👑
Type: bool
property repeated: bool
True if this statement was executed more than once. encore. 🔁
Type: bool
reset_exec_counter() -> None
Resets the number of times this statement has been executed. back to zero. 💅
property schema: SchemaType
The Schema object. schema. 🌸
Type: mysqlx.Schema
property stmt_id: int
Returns this statement ID. the ID. 🔢
Returns: The statement ID.
Return type: int
property target: DatabaseTargetType
The database object target. target. 🎯
Type: object
Errors
mysqlx.DataError
class mysqlx.DataError(msg: str | None = None, errno: int | None = None,
values: Tuple[int | str, ...] | None = None, sqlstate: str | None =
None)Bases: DatabaseError
Exception for errors reporting problems with processed data. 💅
add_note(note, /)
Add a note to the exception. 🫶
args
(no description in the original, it's just chillin' here.) 💅
with_traceback(tb, /)
Set self.__traceback__ to tb and return self. 🔥
mysqlx.DatabaseError
class mysqlx.DatabaseError(msg: str | None = None, errno: int | None = None,
values: Tuple[int | str, ...] | None = None, sqlstate: str | None =
None)Bases: Error
Exception for errors related to the database. 💖
add_note(note, /)
Add a note to the exception. 🫶
args
(no description in the original, it's just chillin' here.) 💅
with_traceback(tb, /)
Set self.__traceback__ to tb and return self. 🔥
🌈 Exceptions 🌈
okay bestie this is the last stretch and honestly it's the most relatable part of the whole page, because it's the entire ✨exception family✨ — the friend group of everything that can possibly go wrong. Every single one of them takes the exact same constructor arguments, but imma write them out every single time anyway because we do not skip details in this house 💅
💖 mysqlx.Error
class mysqlx.Error(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: Exception
This is the mother of the whole squad — the base class for all other error exceptions. If you're catching anything from this module, catching Error gets you everybody. main character energy fr 👑
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🦋 mysqlx.IntegrityError
class mysqlx.IntegrityError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: DatabaseError
For when the relational integrity of your data is being messy — this is the exception for errors regarding relational integrity, and honestly it's giving "you broke the rules of the table" 😭
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🎀 mysqlx.InterfaceError
class mysqlx.InterfaceError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: Error
The interface itself is having a moment. This is the exception for errors related to the interface, no cap 🫶
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🌸 mysqlx.InternalError
class mysqlx.InternalError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: DatabaseError
Internal database errors — as in, internal, as in the database is going through something and you are simply witnessing it 🙈
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🧚 mysqlx.NotSupportedError
class mysqlx.NotSupportedError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: DatabaseError
The vibe check failed: this is the exception for errors when an unsupported database feature was used. You wanted a feature, the database said "not in my era" 💀
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🔥 mysqlx.OperationalError
class mysqlx.OperationalError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: DatabaseError
Exception for errors related to the database's operation — like, the database is operating, it's just not operating the way you hoped 😭
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🍓 mysqlx.PoolError
class mysqlx.PoolError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: Error
Exception for errors relating to connection pooling — yes, the pool has its own dedicated drama exception, and honestly? deserved 🌊
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
💜 mysqlx.ProgrammingError
class mysqlx.ProgrammingError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: DatabaseError
Exception for errors programming errors. Yeah. It's called ProgrammingError, it is for programming errors, we are not going to pretend it's deeper than that bestie ✨
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
⭐ mysqlx.TimeoutError
class mysqlx.TimeoutError(msg: str | None = None, errno: int | None = None, values: Tuple[int | str, ...] | None = None, sqlstate: str | None = None)Bases: Error
Exception for errors relating to connection timeout — the connection said "I'll be there in five" and then was simply never there 💀
add_note(note, /)— Add a note to the exceptionargswith_traceback(tb, /)— Setself.__traceback__totband returnself.
🌈 Navigation 🌈
- Index
- Search Page
👑 AUTHOR 👑
Nuno Mariz
💜 COPYRIGHT 💜
2016, 2023, Oracle and/or its affiliates. All rights reserved
and that's a wrap on the whole page bestie, we literally made it through every single option, every single class, every single line 🥹 this one is version 8.0, dated Sep 19, 2025, and you can find it filed under MYSQLX...ERENCE(1). go forth and query, you absolute icon 💅✨🦋
📦 where this page comes from
- fedora 43: mysql-connector-python3 8.0.33-7.fc43 · (MIT OR GPL-2.0-only) AND GPL-2.0-only AND BSD-2-Clause
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms