Introducing MySQL Connector/Python

by Jesper Krogh

When you write a program that needs to connect to a database server, you need a library that translates your queries into the messages that can be used with the protocol supported by the database. When the database is MySQL, this library is called a connector. There are connectors for several programming languages, from JavaScript (Node.js) to C++. The connector used with Python is called MySQL Connector/Python.

The standard API for using databases in Python is PEP249 – Python Database API Specification v2.0. This API is also what MySQL Connector/Python has implemented since version 1.0. The PEP249 implementation provides great support for executing queries through SQL statements.

The latest MySQL version is 8.0 and introduces a new API for MySQL called the X DevAPI. The special thing about the X DevAPI is that it is primarily a NoSQL API. It supports storing data in JSON documents (the MySQL Document Store), the use of SQL tables through a NoSQL API, and of course the execution of SQL statements. Additionally, the X DevAPI has been developed to work similarly in all supported programming languages while keeping to the traditions and conventions of each respective language.

MySQL Connector/Python 8.0 includes support both for the PEP249 API and the X DevAPI. This support gives the programmer great flexibility to choose the API that works the best for any given job. The two main modules containing the implementations are mysql.connector for the PEP249 API, and mysqlx for the X DevAPI. The following table compares the two APIs.

The easiest way to describe the differences between the APIs is to look at an example. The MySQL sample database named world_x will be used to execute a SELECT in the PEP249 API and the X DevAPI, as well as a read query in the JSON document store part of the X Dev API.

Let’s start with the PEP249 API. The query finds the city with ID = 130, and returns the name, country code, district (state), and population. The population is stored in a JSON column, so the -> operator is used (the same as the JSON_EXTRACT() function). The example code is:

    import mysql.connector

    connect_args = {
        "host": "127.0.0.1",
        "port": 3306,
        "user": "root",
        "password": "password",
    };

    fmt = "{0:<6}   {1:^7}   {2:<15}   {3:>10}"

    db = mysql.connector.connect(**connect_args)
    cursor = db.cursor(dictionary=True)
    sql = """
        SELECT Name, CountryCode, District, Info->'$.Population' AS Population
          FROM world_x.city
         WHERE ID = %(id)s"""
    cursor.execute(sql, params={"id": 130})

    row = cursor.fetchone()

    print("PEP249 API:\n")

    print(fmt.format("Name", "Country", "District", "Population"))

    print(fmt.format(

    row["Name"], row["CountryCode"], row["District"], row["Population"]))

    cursor.close()

    db.close()

Warning: Connection parameters including passwords are for simplicity included in my examples in this post. Never though, store passwords in your programs. Additionally, the examples are without any kind of error handling, which of course you should include in anything you write for production use.

The output of executing the program is:

    PEP249 API:

    Name       Country   District                       Population
    Sydney     AUS         New South Wales      3276207

As comparison, executing the same query through the X DevAPI can be done as follows:

   import mysqlx

   connect_args = {
        "host": '127.0.0.1',
        "port": 33060,
        "user": "root",
        "password": "password",

    }; 

    fmt = "{0:<6}   {1:^7}   {2:<15}   {3:>10}"

    db = mysqlx.get_session(**connect_args)

    schema = db.get_schema("world_x")
    city = schema.get_table("city")

    stmt = city.select(
        "Name", "CountryCode", "District",
        "Info->'$.Population' AS Population"
    ).where("ID = :id")

    result = stmt.bind("id", 130).execute()
    row = result.fetch_one()

    print("X DevAPI - SQL Table:\n")
    print(fmt.format("Name", "Country", "District", "Population"))
    print(fmt.format(
        row["Name"], row["CountryCode"], row["District"], row["Population"]))

    db.close()

Notice how the connection is made to port 33060. This port number is not a mistake. The traditional MySQL protocol uses port 3306 by default. The X DevAPI uses a new protocol – the X Protocol – which by default uses port 33060.

The X DevAPI example takes advantage of the support for chaining when building the SQL statement to be executed. This use of chaining can, for example, be seen in the code starting with stmt = city.select. One difference from the PEP249 version is that you don’t pass a written-out SQL statement to theX DevAPI. Instead the statement to be executed is created for you through a series of API calls.

The world_x.city table does not qualify as a JSON document collection despite it having a JSON column. Instead, the countryinfo collection is used. The following example shows how to execute a read query against the JSON Document Store. The example is similar to the two previous examples, as it finds information for Australia (with the _id = AUS):

    import mysqlx

    connect_args = {
        "host": '127.0.0.1',
        "port": 33060,
        "user": "root", 
        "password": "password",
    };

    fmt = "{0:<10}   {1:^12}   {2:>10}"

    db = mysqlx.get_session(**connect_args)

     schema = db.get_schema("world_x")
     city = schema.get_collection("countryinfo")

     stmt = city.find("_id = :country").fields(
         "Name", "IndepYear", "demographics.Population AS Population")
     result = stmt.bind("country", "AUS").execute()
     doc = result.fetch_one()

     print("X DevAPI - Document:\n")
     print(fmt.format("Name", "Independence", "Population"))
     print(fmt.format(doc["Name"], doc["IndepYear"], doc["Population"]))

     db.close()

Notice when using the Document Store that you set the filtering condition in the call to find(), then add the fields using the field() method. The opposite was the case for the SQL table where the fields where specified through a call to the select() method, and the condition was set through calling the where() method. Otherwise the overall flow is similar.

If you want to explore MySQL Connector/Python on your own, there are several ways to install MySQL Connector/Python. The simplest is to use the pip command to install the mysql-connector-python package:

    shell$ pip install mysql-connector-python

There are also repositories available for several Linux distributions, a MySQL Installer for Microsoft Windows, and direct downloads from https://dev.mysql.com/downloads/. The full installation instructions can be found in the MySQL Connector/Python reference manual.

About the Author

Jesper Wisborg Krogh is a member of the Oracle MySQL Support team and has spoken on several occasions at Oracle OpenWorld. He is author of the following books on MySQL:

• MySQL Connector/Python Revealed
• Pro MySQL NDB Cluster

This article was contributed by Jesper Krogh, author of MySQL Connector/Python Revealed.