MySQL Connector/Python

MySQL Connector/Python is Oracle's official Python driver for MySQL databases. It provides two primary ways to interact with MySQL:

1. Python Database API Specification v2.0 (PEP 249) compliant API: A standard interface for traditional SQL-based database access.
2. X DevAPI implementation: A modern API designed for NoSQL-style document store operations and advanced MySQL features.

Use the PEP 249 API for standard relational database operations and the X DevAPI for document-based workflows.

MySQL Connector/Python provides a client library for the X DevAPI protocol (introduced in MySQL 8.0). This API allows developers to interact with MySQL 8.0 using both relational tables and JSON documents, enabling a hybrid approach where you can work with tables and collections simultaneously.

For deep conceptual details on the X DevAPI protocol itself, refer to the official MySQL X DevAPI User Guide at http://dev.mysql.com/doc/x-devapi-userguide/en/.

By default, if a transaction requests a row that is already locked, it will block and wait until the lock is released. You can change this behavior using mysqlx.LockContention options passed to the locking methods:

• mysqlx.LockContention.NOWAIT: The query executes immediately. If the requested row is locked, the operation fails with an error instead of waiting.
• mysqlx.LockContention.SKIP_LOCKED: The query executes immediately. Instead of failing or waiting, it simply excludes any locked rows from the result set.

# Using NOWAIT to fail immediately if locked
collection.find("_id = :id").lock_shared(mysqlx.LockContention.NOWAIT).bind("id", "1").execute()

# Using SKIP_LOCKED to ignore locked rows
collection.find("_id = :id").lock_exclusive(mysqlx.LockContention.SKIP_LOCKED).bind("id", "1").execute()

The mysql.ai module provides an optional API for integrating with MySQL HeatWave's AI and Machine Learning capabilities.

Note: This requires manual installation of dependencies: langchain, pandas, and scikit-learn.

GenAI

Provides LangChain-compatible implementations:

• MyLLM
• MyVectorStore
• MyEmbeddings

AutoML

Provides Scikit-Learn compatible estimators:

• MyClassifier
• MyRegressor
• MyAnomalyDetector
• MyGenericTransformer

Connection pooling in Connector/Python with the X Protocol manages a pool of ready-to-use connections to reduce connection creation time and improve application performance.

When you use mysqlx.get_client(), you create a client that manages a pool. Every session obtained via client.get_session() uses a pooled connection. When you call session.close(), the connection is not destroyed but is instead returned to the pool to be reused by other requests or threads.

import mysqlx

connection_str = 'mysqlx://mike:s3cr3t!@localhost:33060'
options_string = '{}'

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

# ... perform work ...

session.close()  # Returns connection to the pool
client.close()   # Closes the client and the pool

MySQL server tracks operational details for every connected client using connection attributes. These attributes fall into two categories:

1. System-defined attributes: Automatically set by the X DevAPI (e.g., _client_name, _client_version, _os). These always start with an underscore (_).
2. User-specified attributes: Custom metadata provided by the client.

Constraint: User-defined connection attributes must not start with an underscore (_), as that prefix is reserved for system-defined attributes.

In the X DevAPI, documents of the same type are grouped into Collection objects. You can create a new collection using the mysqlx.Schema.create_collection() method on a mysqlx.Schema object.

To prevent errors if a collection with the same name already exists, set the reuse_existing argument to True.

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)

You can pass custom connection attributes when establishing a session by appending them to the connection URL using the connection-attributes key. The attributes should be formatted as a comma-separated list of key=value pairs within square brackets.

import mysqlx

# Using a connection URL with custom attributes
# Format: mysqlx://user@host:port/schema?connection-attributes=[key1=value1,key2=value2]
mysqlx.getSession('mysqlx://mike@localhost:33060/schema?connection-attributes=[my_attribute=some_value,foo=bar]')

If your environment uses DNS SRV records for service discovery, you can use the mysqlx+srv scheme or the dns-srv option. This allows the connector to automatically resolve available server addresses.

Requirement: This requires the dnspython module to be installed.

import mysqlx

# Using the connection scheme
session = mysqlx.get_session('mysqlx://root:@foo.abc.com')

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