About Nuno Mariz

Nuno Mariz is a Software Developer at Oracle. He is the Team lead for Connector/Python. Nuno is an experienced developer with over 18 years of programming, deployment and scaling web applications, founder/leader of the Portuguese Python User Group and Triathlete.

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



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"]))


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})

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

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

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

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

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

# Create a savepoint

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

# Rollback to a savepoint

assert(2 == collection.count())

# Commit all operations

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

# 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

assert(2 == collection.count())

# Commit all operations

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/


MySQL Connector/Python 2.1.4 GA has been released

The MySQL Connector/Python Team is pleased to announce the newest MySQL Connector/Python 2.1.4, the second GA version of 2.1 release series of the pure Python database driver for MySQL.

This release includes a number of improvements for usability, stability and security.

Changes in MySQL Connector/Python 2.1.4 GA

Security Notes

The linked OpenSSL library for Connector/Python Commercial has been updated to version 1.0.1q. Issues fixed in the new OpenSSL version are described at http://www.openssl.org/news/vulnerabilities.html. This change does not affect Oracle-produced MySQL Community builds of Connector/Python, which use the yaSSL library instead.
The change also does not affect connections made using any pure Python implementation of Connector/Python, for which the version of OpenSSL used is whatever is installed on the system.

Bugs fixed

  • Connector/Python failed to establish connections using the cleartext client-side authentication plugin for accounts using the PAM server-side authentication plugin. Thanks to DaniĆ«l van Eeden for the patch. (Bug #22873551, Bug #80609)
  • A potential SQL injection vector was eliminated. (Bug #22529828)
  • Connections made using the C Extension failed when the ssl_ca parameter was given without ssl_cert and ssl_key. (Bug#21879914, Bug #79835, Bug #22494320)
  • For connections made with consume_results=True, consume_results was reset to False after callproc() execution failure. (Bug #21879859) References: This issue is a regression of: Bug #21492815.
  • In connections for which compress=True, LOAD DATA LOCAL INFILE statements produced “Packets out of error” errors. (Bug#21449996)
  • Attempts to insert Python ByteArray data into binary table columns failed. (Bug #77822, Bug #21498719)

Quick links

Documentation: http://dev.mysql.com/doc/connector-python/en/index.html
Release Notes: http://dev.mysql.com/doc/relnotes/connector-python/en/index.html
Downloads: http://dev.mysql.com/downloads/connector/python/#downloads
Feedback, bugs and feature requests: http://bugs.mysql.com
Forum: http://forums.mysql.com/list.php?50
On GitHub: https://github.com/oracle/mysql-connector-python


Thank you all for your support and feedback!

On behalf of the MySQL Connector/Python Team.