MySQL :: Using MySQL Connector/Python 8.0 with MySQL 8.0

The MySQL Connector/Python Team is pleased to announce MySQL Connector/Python 8.0.11, the first GA 8.0 release series of the official MySQL driver for Python. This release introduces the first Python driver that adds full MySQL 8.0 support.

Document Store

MySQL 8.0 includes many improvements and new features, with Document Store being the biggest.

MySQL Document Store is schema-less with a flexible data structure storage system for documents. This removes the requirement for schema design, normalization, foreign keys, constrains, and data types.

The Document Store is accessible by the X DevAPI, an API that introduces a new modern and easy-to-learn way to work with your data that is implemented by all MySQL Connectors that support the X Protocol, such as MySQL Connector/Python.

Getting Started

Installation

Requirements:

MySQL 8.0
Python 2.7 or >= 3.4
Protobuf C++ (version >= 2.6.0) if installing from source
Python Protobuf (version >= 3.0.0)

The recommended way to install Connector/Python is via pip.

shell> pip install mysql-connector-python

Packages are also available from the MySQL Connector/Python downloads 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.

For installation documentation, please visit the Connector/Python installation guide.

Writing your first program using the MySQL Document Store

Assuming Connector/Python 8.0 is installed on your system and you’re accessing a MySQL 8.0 server, you’re ready to write your first program using the MySQL Document Store.

import mysqlx

# Connect to server on localhost
session = mysqlx.get_session({
    "host": "localhost",
    "port": 33060,
    "user": "user",
    "password": "secret"
})

# Get schema object
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()

For a detailed explanation of this code, please visit the Getting Started section of the official MySQL Connector/Python X DevAPI Reference Documentation.

Improved Security

Server TCP connections are now secure by default, meaning the default SSL Mode is now set to REQUIRED (mysqlx.SSLMode.REQUIRED).
Support was also added for the latest secure MySQL 8 authentication plugins based on SHA-256 hashing, such as new the caching_sha2_password authentication plugin. Usage can be implicit or explicit, for example:

# Using the caching_sha2_password plugin
mysqlx.get_session({"user": "user", "password": "secret",
                    "auth": mysqlx.Auth.SHA256_MEMORY})
mysqlx.get_session("mysqlx://user:secret@localhost?auth=SHA256_MEMORY")

# Using the mysql_native_password plugin
mysqlx.get_session({"user": "user", "password": "secret",
                    "auth": mysqlx.Auth.MYSQL41})
mysqlx.get_session("mysqlx://user:secret@localhost?auth=MYSQL41")

# Using any server-side plugin
mysqlx.get_session({"user": "user", "password": "secret",
                    "auth": mysqlx.Auth.PLAIN})
mysqlx.get_session("mysqlx://user:secret@localhost?auth=PLAIN")

# If auth is not set, PLAIN will be used by default if connection is secure
mysqlx.get_session({"user": "user", "password": "secret"})
mysqlx.get_session("mysqlx://user:secret@localhost")

Fluent API

The X DevAPI operations are structured to be “fluent”. This means it’s possible to call multiple methods to modify an object prior to execution, which enables a fluent query builder and allows more readable, maintainable, and secure code.

An example is following where where() and sort() are fluent methods to modify the find operation prior to executing it.

collection = schema.get_collection("coll")
collection.find().where("age > 28").sort("age DESC").execute()

Support for raw SQL statements

The X DevAPI allows you to execute raw SQL statements to combine the flexibility of the document store model with the power of the relational model.

# Create a table
session.sql("CREATE TABLE `mydb`.`person` "
            "(name VARCHAR(20), age INT)").execute()
# Insert rows
session.sql("INSERT INTO `mydb`.`person` (name, age) "
            "VALUES ('Nuno', 42);").execute()
# Fetch rows
result = session.sql("SELECT name, age FROM `mydb`.`person`").execute()
for row in result.fetch_all():
    print("Name: {0}, Age: {1}".format(row["name"], row["age"]))

Transactions, Savepoints and Row Locking

Connector/Python provides an API to create, commit, or rollback a transaction, and also to create, release, or rollback to an intermediate savepoint within that transaction.

An optional savepoint name can be defined to create a transaction savepoint, which can later be used to rollback.

# Start transaction
session.start_transaction()

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

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

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

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

assert(2 == collection.count())

# Commit all operations
session.commit()

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

# Start transaction
session.start_transaction()

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

# Create a savepoint
savepoint = session.set_savepoint()

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

# Rollback to a savepoint
session.rollback_to(savepoint)

assert(2 == collection.count())

# Commit all operations
session.commit()

To learn more about writing applications using the MySQL X DevAPI with Connector/Python and other MySQL connectors and clients, see http://dev.mysql.com/doc/x-devapi-userguide/en/

For general documentation about how to get started using MySQL as a document store, see https://dev.mysql.com/doc/refman/8.0/en/document-store.html

For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see https://dev.mysql.com/doc/dev/connector-python/8.0/

We welcome and appreciate your feedback and bug reports: http://bugs.mysql.com/

Enjoy!