MySQL Connector/Python: Introduction and Installation

You are about to embark on a journey through the world of MySQL Connector/Python. Welcome aboard! This is the first article out of a ten-step guide that will take you through everything from installation to troubleshooting. Along the way you will become acquainted with the features and workings of the connector and its APIs.

This blog will introduce MySQL Connector/Python by going through the versions, editions, and the APIs. The middle part of this article will discuss how to download and install the connector, and the final part will talk about MySQL Server, how to set up the server for the examples in this article, and a word on the examples themselves.

Introduction

MySQL Connector/Python is the glue that is used between a Python program and a MySQL Server database. It can be used to manipulate the database objects using data definition language (DDL) statements as well as to change or query the data through data manipulation language (DML) statements.

You can also call MySQL Connector/Python a database driver. It is the official MySQL connector for Python, developed and maintained by Oracle Corporation by the MySQL development team. It effectively supports three different APIs, although only two are commonly used directly.

This section introduces the MySQL Connector/Python versions, editions, and the three APIs.

Versions

Before 2012, there was no Python connector maintained by Oracle. There were other third-party connectors, such as the MySQL-python (MySQLdb) interface; however, it was getting aged and only officially supported up to MySQL 5.5 and Python 2.7.

MySQL decided to develop its own connector: MySQL Connector/Python. It was written to be compatible with the MySQL-python interface and to be up to date with the latest MySQL Server and Python versions. The initial general availability (GA) release was version 1.0.7, which was released in September 2012. A major update occurred with version 2.1; it introduced the C Extension, which allows better performance. The latest GA release as of April 2018 is version 8.0.11, which additionally introduces the X DevAPI. This is the version that is the primary focus of this article.

Note

If you look at the change history of MySQL Connector/Python, you may be a little puzzled. The version series before 8.0 was 2.1 with a few pre-GA releases of version 2.2. The list of 8.0 releases is no less puzzling: the latest pre-GA release is 8.0.6 with the first GA release being 8.0.11. Why the jumps? The version numbers of most MySQL products were aligned, which required some irregularity in release numbers, but it now means that MySQL Server 8.0.11 and MySQL Connector/Python 8.0.11 are released together.

It is recommended to use the latest patch release of the latest series of GA quality. Only the latest GA series receives all improvements and bug fixes. That means that, at the time of writing, it is recommended to use the latest MySQL Connector/Python 8.0 release. While the MySQL Connector/Python 8.0 releases are coupled together with the release of MySQL Server and other MySQL products, they are backward compatible with older MySQL Server versions. So, even if you are still using, for example, MySQL Server 5.7, you should still use MySQL Connector/Python 8.0.

Tip

Use the latest release of the latest release series of GA quality to ensure you have access not only to all the latest features but also the latest available bug fixes. The latest MySQL Connector/Python version can be used with older MySQL Server versions. On the other hand, an older version of MySQL Connector/Python may not be compatible with the latest MySQL Server version. For example, MySQL Server 8.0 uses the caching_sha2_password authentication plugin by default, which is not supported until recently in MySQL Connector/Python.

As with any product under active development, new features are regularly added and bugs are fixed. You can follow the changes in the release notes, which are available from https://dev.mysql.com/doc/relnotes/connector-python/en/ .

In addition to the various versions of MySQL Connector/Python, there are (as with other MySQL products) two different editions to choose from. Let’s take a look at them.

Community and Enterprise Editions

MySQL products are available in two different editions: Community and Enterprise. The Enterprise Edition is a commercial offering from Oracle. The difference between the two editions varies among the products. For example, for MySQL Server, several additional plugins are available for the Enterprise Edition. For MySQL Connector/Python, the difference is subtler.

A common difference for all products is the license. The Community Edition is released under the GNU General Public License, version 2.0 , whereas the Enterprise Edition uses a proprietary license. Additionally, the Enterprise Edition includes technical support through MySQL Technical Support Services. These are presently the only differences between the two editions for MySQL Connector/Python itself.

This article will work with either of the two editions and, except when briefly discussing download locations and install methods later in this blog, there will be no mention of the edition. All examples have been written and tested with the Community Edition.

In contrast, when it comes to APIs, it makes a big difference which API you use.

APIs

There are effectively three different APIs that can be used in MySQL Connector/Python. Before you get started for real, it is worth taking a brief view of the differences.

Table 1 shows the three APIs, which MySQL Connector/Python module they are available in, the first GA version including support for the API.

Table 1. MySQL Connector/Python APIs

Additionally, the Connector/Python API and X DevAPI exist both in a pure Python implementation and one using C Extension under the hood. These two implementations are meant to be interchangeable. Some differences between the two implementations will be mentioned when encountered throughout the blog.

As you can see, the main focus is on the Connector/Python API and X DevAPI. The Connector/Python API and the C Extension API exclusively use SQL statements to execute queries. The X DevAPI, on the other hand, supports NoSQL methods to handle JSON documents and SQL tables as well as support for SQL statements. The X DevAPI is a common API available for other programming languages as well, including JavaScript (Node.js), PHP, Java, DotNet, and C++.

So which API should you choose? From the description thus far, it sounds like it is a no-brainer to choose the X DevAPI. However, there is a little more to it than that.

If you are exclusively using SQL statements to execute queries, the C Extension and C Extension API are more mature. For example, they offer much better support for features such as parameter binding and prepared statements. If you need a connection pool, they are also the APIs to choose. If you have existing Python programs, they are also most likely using the Connector/Python API (with or without the C Extension implementation enabled).

On the other hand, the X DevAPI is a new API that has been designed from the ground up to fit modern requirements. The API also exists for other programming languages, making it easier to switch between languages when several languages are required for the applications. The NoSQL parts of the API makes simple queries against SQL tables and working with JSON documents simpler. The new command-line client, MySQL Shell, also supports using the X DevAPI via either Python or JavaScript. So, the X DevAPI there is a lot talking for new projects.

Since the X DevAPI is essentially in its version 1.0 (MySQL 8.0 is the first GA version for the X DevAPI), new features are more likely to become available in relatively short succession. If you are missing a feature, keep an eye on the release notes to see if the feature has become available, or register your interest at https://bugs.mysql.com/ .

Whether to use the C Extension or not is to a large degree a question of performance compared to “convenience.” The C Extension implementation provides better performance particularly when working with large result sets and prepared statements. However, the pure Python implementation is available on more platforms, is easier to work with when building MySQL Connector/Python yourself, and is easier to modify (as the name suggest, the pure Python implementation is written entirely in Python).

This concludes the introduction to MySQL Connector/Python. It is time to get started with the installation process. The first step is to download MySQL Connector/Python.

Downloading

It is straightforward to download MySQL Connector/Python; however, there are still a few considerations. These considerations and the steps to perform the download are the topics of this section.

The first thing to ask is whether you need the Community or Enterprise Edition of the connector. This decides both the download and the install options. The Community Edition is available from several locations and both in the form of source code and as binary distributions. The Enterprise Edition is only available as the binary distribution from Oracle.

Tip

The recommended way to install the Community Edition of MySQL Connector/Python is to use packages from the Python Packaging Authority (PyPa)/Python Package Index (PyPi). This is done using the pip tool and does not require predownloading any files. One downside of using PyPi is there can be a small lag from when the release is made to when it becomes available in PyPi.

Table 2 shows an overview of the delivery methods available for MySQL Connector/Python and whether the method is available for the Community and Enterprise Editions .

Table 2. MySQL Connector/Python Download Options

As you can see, MySQL Connector/Python is available for a large range of platforms and in different distributions. The Community Edition is available directly using the pip command-line tool; using the MySQL Yum repository for Red Hat Enterprise Linux, Oracle Linux, and Fedora Linux; the MySQL APT repository for Debian and Ubuntu; and using the MySQL SUSE repository for SLES. The pip and package repository options are only available for the Community Edition.

Tip

Both a MySQL Installer and a MSI Installer for MySQL Connector/Python are available for Microsoft Windows. If you want to use one of these installers, MySQL Installer is recommended because it also supports most of the other MySQL products.

Table 3 shows the URLs for the download locations for the various sources and installers. The MySQL repositories count as installers in this context even though they are more like definition files used with an installer.

Table 3. Download Sources

The Community Edition-related downloads are available from pages under https://dev.mysql.com/downloads . If you need the source code, it is available from the MySQL Downloads site and MySQL’s GitHub repository.

 The Enterprise Edition is available either from the Patches & Updates tab in My Oracle Support (MOS) or from the Oracle Software Delivery Cloud (requires creating an account and signing in). MySQL customers are recommended to use My Oracle Support because it contains more releases and is updated more often than the Oracle Software Delivery Cloud. On the other hand, a 30-day trial version of the Enterprise Edition of the MySQL products is available from Oracle Software Delivery Cloud. MySQL Installer for Microsoft Windows is also available in an Enterprise Edition; this can be downloaded from either My Oracle Support or Oracle Software Delivery Cloud.

The downloads are pretty straightforward. Figure 1 shows the download screen to download the MySQL Installer for Microsoft Windows.

 Figure 1. Downloading the MySQL Installer for Microsoft Windows

Once you click Download, you will be taken to the page in Figure 2 if you are not logged in. Here you can choose to log into an existing Oracle Web account, sign up for a new Oracle Web account, or download without using an account by clicking No thanks, just start my download. Choose the option that suits you best. The Oracle Web account is also used for My Oracle Support and Oracle Software Delivery Cloud, so if you are an Oracle customer you can use your existing account.

Figure 2. Ready to download

Downloading other MySQL Products including MySQL Connector/Python from the community download page follows the same pattern. The main difference is that you will need to choose the operating system and optionally the operating system version you are using. The default operating system chosen will be the one you are browsing from. Figure 3 shows how the operating system can be chosen when downloading MySQL Connector/Python.

Figure 3 Choosing a platform for MySQL Connector/Python

Once you have chosen the platform, you can choose the specific file to download. The differences may be which Python version to use MySQL Connector/Python with and whether it is the pure Python or the C Extension implementation.

One word about the C Extension and downloads. Depending on the platform, the C Extension implementation may be bundled with the rest of the download and automatically installed, or there may be a separate file to download. On Microsoft Windows, the C Extension is always included if it available for the Python version. In general, the latest couple of supported Python versions will include the C Extension; for older Python versions, it is not included. For RPM and DEB packages, there are two packages for each MySQL Connector/Python release and supported Python version: one file with the pure Python implementation and one with the C Extension implementation.

The web sites where the Enterprise Edition of MySQL Installer and MySQL Connector/Python can be downloaded are designed differently, but the idea is the same. How downloads work from My Oracle Support and the Oracle Software Delivery Cloud will not be discussed further in my blog. Instead, let’s look at the installation process itself.

Installation

MySQL Connector/Python supports several ways to install the connector. The available methods depend on the operating system. The steps should give few surprises if you are used to installing software.

If your installation method includes the option of whether to install the C Extension or not (for example, RPM or DEB packages), it is recommended to include the C Extension package. Even if you do not plan on using the _mysql_connector module directly, using the C Extension implementation of the other APIs can provide better performance.

The type of installation that will be required closely follows the choice of how to download the installation file. The most unique way of installing MySQL Connector/Python is to use MySQL Installer . This section will go through installation using the pip command, using MySQL Installer, and using the MySQL Yum repository.

pip – All Platforms

The recommended way to install MySQL Connector/Python if you use the Community Edition is to use the pip command to install the package from the Python Packaging Authority (PyPa). This ensures that any potential dependencies are resolved automatically, and the same installation method can be used across all platforms where you need MySQL Connector/Python.

The pip command is available as part of the normal Python installation for Python version 2.7.9 and later if you downloaded Python from https://www.python.org/ . Noticeable exceptions are some Linux distributions such as RedHat Enterprise Linux, Oracle Linux, and CentOS Linux., which still use relatively old versions of Python. General installation instructions can be found at https://pip.pypa.io/en/stable/installing/ and https://packaging.python.org/guides/installing-using-linux-tools/ . The sidebar “Installing pip On the RedHat Family of Linux” includes an example of how to install pip on RedHat Enterprise Linux, Oracle Linux, and CentOS.

When pip is available, it is simple to install the latest available MySQL Connector/Python release using the install command. The exact output of the installation varies, for example, depending on whether dependencies such as protobuf have already been installed. An example output is

PS: Python> pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading https://files.pythonhosted.org/.../mysql_connector_python-8.0.11-cp36-cp36m-win_amd64.whl
(3.0MB)
    100% |███████████████| 3.0MB 3.5MB/s
Collecting protobuf>=3.0.0 (from mysql-connector-python)
  Using cached https://files.pythonhosted.org/.../protobuf-3.5.2.post1-cp36-cp36m-win_amd64.whl
Requirement already satisfied: six>=1.9 in c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python)
 (1.11.0)
Requirement already satisfied: setuptools in c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python)
 (28.8.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.11 protobuf-3.5.2.post1

The example is from Microsoft Windows executing the pip command in PowerShell. The command assumes that the pip command is in the search path for executables (this can be enabled when installing Python on Windows and will in general be the case on Linux). If the pip command is not in the search path, you must use the full path. When the installation is performed on other platforms, the command is the same and the output very similar.

If you want to uninstall the package, the command is very similar; just use the uninstall command instead.

So

PS: Python> pip uninstall mysql-connector-python
Uninstalling mysql-connector-python-8.0.11:
  Would remove:
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\_mysql_connector.cp36-win_amd64.pyd
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\_mysqlxpb.cp36-win_amd64.pyd
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\libeay32.dll
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\libmysql.dll
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysql\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysql_connector_python-8.0.11.dist-info\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\mysqlx\*
    c:\users\jesper\appdata\local\programs\python\python36\lib\site-packages\ssleay32.dll
Proceed (y/n)? y
  Successfully uninstalled mysql-connector-python-8.0.11

Installing pip On The Redhat Family Of Linux

The best way to install the pip command on Oracle Linux, RedHat Enterprise Linux, and CentOS is to use the EPEL Yum repository. The following steps assume you are using version 7 of the respectively Linux distribution. Older versions will require slightly different instructions. The steps are as follows:

1. Download the EPEL repository definition from https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm .
2. Install the downloaded EPEL RPM.
3. Install the python-pip and python-wheel packages.
4. Optionally, let pip upgrade itself using the pip install – upgrade pip command.

 The python-wheel package provides support for the wheel built-package format used for Python packages. See also https://pypi.org/project/wheel/ .

The combined steps executed in the Linux shell are as follows:

shell$ wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
...
2018-03-10 20:26:28 (55.3 KB/s) - 'epel-release-latest-7.noarch.rpm' saved [15080/15080]
shell$ sudo yum localinstall epel-release-latest-7.noarch.rpm
...
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : epel-release-7-11.noarch                      1/1
  Verifying  : epel-release-7-11.noarch                      1/1
Installed:
  epel-release.noarch 0:7-11
Complete!
shell$ sudo yum install python-pip python-wheel
...
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python-wheel-0.24.0-2.el7.noarch              1/2
  Installing : python2-pip-8.1.2-5.el7.noarch                2/2
  Verifying  : python2-pip-8.1.2-5.el7.noarch                1/2
  Verifying  : python-wheel-0.24.0-2.el7.noarch              2/2
Installed:
  python-wheel.noarch 0:0.24.0-2.el7
  python2-pip.noarch 0:8.1.2-5.el7
Complete!
shell$ sudo pip install --upgrade pip
Collecting pip
  Downloading pip-9.0.1-py2.py3-none-any.whl (1.3MB)
    100% |████████████████| 1.3MB 296kB/s
Installing collected packages: pip
  Found existing installation: pip 8.1.2
    Uninstalling pip-8.1.2:
      Successfully uninstalled pip-8.1.2
Successfully installed pip-9.0.1

At this point, the pip command has been installed as /usr/bin/pip. In most cases, commands in /usr/bin can be executed without specifying the full path.

Microsoft Windows – MySQL Installer

For installations on Microsoft Windows where you for one reason or another do not wish to use the pip command, the preferred installation method is MySQL Installer. One advantage is that it can be used to install both the Community and Enterprise Editions of MySQL Connector/Python. Which version will be installed depends on the edition of MySQL Installer.

The following instructions assume you have MySQL Installer on your computer already. If that is not the case, please see the “MySQL Installer for Microsoft Windows” sidebar for instructions. The first step is to launch MySQL Installer. The first time you use the installer, you will be asked to accept the license terms. Then you will be taken to a screen where you can choose which MySQL products you want to install. We will pick up at that point again after the discussion of Figure 4.

If you have already used MySQL Installer to install products, you will be shown the screen in Figure 4; it’s an overview of the MySQL products already installed and the available actions.

If you installed MySQL Installer some time ago and have not recently updated the catalog, it is recommended first to click the Catalog action in the lower right corner to ensure you can choose from all the latest releases. This will take you to a screen where you can execute a catalog update. The update does not change any of the products installed; it only updates the list of products that MySQL Installer uses to notify of upgrades and you choose from when installing new products.

Once the catalog is up to date, you can add a new product using the Add action to the right of the list of installed products. This brings you to the screen shown in Figure 5, which is also the screen you are taken directly to the first time MySQL Installer is launched.

Figure 5 Choosing what to install

The filter at the top can be used to narrow down or expand which products and releases should be included. By default, the latest GA releases are included for all software in both the 32-bit and 64-bit architectures. If you want to try out a development milestone release or release candidate, you need to include pre-releases by editing the filter. An example of filtering to search for GA releases of Connector/Python under The MySQL Connectors category and requiring it to be 64-bit can be seen in Figure 6.

Figure 6 Filtering the list of products

MySQL Connector/Python can be found under Available Products by expanding the MySQL Connectors group. There is one product listed for each supported Python version. MySQL Installer will check whether the correct Python version is installed. When you have found the right release, add it to the list of products and features to be installed by clicking the arrow pointing to the right and then clicking Next .

The next screen shows an overview of the products to install. Once you have confirmed everything is correct, click Execute to start the installation. The execution may take a little time because it includes downloading the connector. Once the installation has completed, click Next. This will allow you to copy the log to the clipboard and finish.

MySQL Installer For Microsoft Windows

The MySQL Installer for Microsoft Windows is the entry point to manage the various MySQL products (MySQL NDB Cluster is an exception). It allows you to install, upgrade, and remove the products and features from one interface. There is also limited support for configuring MySQL Server.

MySQL Installer comes in two flavors: one that includes a version of MySQL Server and one without (the “web” download). If you know you will be installing MySQL Server, it can be convenient to use the download that has MySQL Server bundled because it will save time during the installation. With either choice, MySQL Installer will download the product as part of the installation if you do not have a local installation file ready.

In order to install MySQL Installer, follow these steps:

1. Download MySQL Installer. If you are using the Community Edition of the MySQL products, download it from https://dev.mysql.com/downloads/installer/ . If you use the Enterprise Edition, download it from My Oracle Support ( https://support.oracle.com/ ) or Oracle Software Delivery Cloud ( https://edelivery.oracle.com/ ). Both locations for the Enterprise Edition require using an existing Oracle account or creating a new one. My Oracle Support is recommended if you are an existing customer.
2. The downloaded file is an MSI Installer but will for the Enterprise Edition be inside a zip file, which you can unzip and then execute the MSI Installer and follow the instructions.
3. If you have not downloaded the latest release of MySQL Installer, you will be offered the opportunity to upgrade it. It is recommended to do this.
4. When the installation has completed, MySQL Installer automatically launches.

The installer also can be launched later, for example through the Start menu.

Linux – MySQL Yum Repository

The easiest way to install MySQL products in the Community Edition on Linux distributions is to use the MySQL repository. For RedHat Enterprise Linux, Oracle Linux, CentOS, and Fedora, this means the MySQL Yum repository. This way the packages can be found by the yum command and Yum will be able to resolve dependencies automatically. Except for using the pip command to install MySQL Connector/Python, this is the recommended way to install MySQL software if you want the install to be managed.

That the installation is managed means the installer (pip or yum) handles dependencies for you, and it is possible to request an upgrade using the installer. For both installs and upgrades, the software is automatically downloaded from the repository.

The MySQL Yum repository is installed using the RPM that can be downloaded from https://dev.mysql.com/downloads/repo/yum/ . Choose the RPM that corresponds to your Linux distribution. The RPM can, for example, be installed using the yum localinstall command:

shell$ sudo yum localinstall \
            mysql57-community-release-el7-11.noarch.rpm
...
Running transaction
  Installing : mysql57-community-release-el7-11.noarch      1/1
  Verifying  : mysql57-community-release-el7-11.noarch      1/1
Installed:
  mysql57-community-release.noarch 0:el7-11                     
Complete!

 The MySQL RPMs are signed with GnuPG. To make the rpm command (invoked by yum) check the signatures and not complain about missing keys, you need to install the public key used by MySQL. There are several ways to do this as described in https://dev.mysql.com/doc/refman/en/checking-gpg-signature.html . One option is to get the public key from this page and save it in a file. You need the part starting with -----BEGIN PGP PUBLIC KEY BLOCK----- and finishing with -----END PGP PUBLIC KEY BLOCK----- (everything included). Save the key into a file, for example named mysql_pubkey.asc. Then you import the key into RPM’s keyring:

shell$ sudo rpm --import mysql_pubkey.asc

 Once the repository and the public key have been installed, MySQL Connector/Python can be installed as shown in Listing 1.

shell$ sudo yum install mysql-connector-python \
                        mysql-connector-python-cext
...
Downloading packages:
(1/2): mysql-connector-python-8.0.11-1.el7.x86_64.rpm | 418 kB 00:00
(2/2): mysql-connector-python-cext-8.0.11-1.el7.x86_6 | 4.8 MB 00:01
---------------------------------------------------------------
Total                                   3.3 MB/s | 5.2 MB 00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-connector-python-8.0.11-1.el7.x86_64   1/2
  Installing : mysql-connector-python-cext-8.0.11-1.el7.x86_64   2/2
  Verifying  : mysql-connector-python-8.0.11-1.el7.x86_64        1/2
  Verifying  : mysql-connector-python-cext-8.0.11-1.el7.x86_64   2/2
Installed:
  mysql-connector-python.x86_64 0:8.0.11-1.el7
  mysql-connector-python-cext.x86_64 0:8.0.11-1.el7
Complete!

Listing 1 Installing MySQL Connector/Python Using Yum on Linux

This code installed both the pure Python and the C Extension (with cext in the name) implementations of MySQL Connector/Python. Before moving on, let’s verify the MySQL Connector/Python installation.

Verifying the Installation

A simple way to verify that the installation of MySQL Connector/Python works is to create a small test program to print a few properties from the mysql.connector module. If the program executes without errors, the installation was successful.

Listing 2 shows an example of retrieving the MySQL Connector/Python version as well as a few other properties.

import mysql.connector
print(
  "MySQL Connector/Python version: {0}"
  .format(mysql.connector.__version__)
)
print("Version as tuple:")
print(mysql.connector.__version_info__)
print("")
print("API level: {0}"
  .format(mysql.connector.apilevel))
print("Parameter style: {0}"
  .format(mysql.connector.paramstyle))
print("Thread safe: {0}"
  .format(mysql.connector.threadsafety))

 Listing 2 Verifying That the MySQL Connector/Python Installation Works

The version is printed in two different ways, as a string and as a tuple. The tuple can be useful if you need an application to be compatible with two different versions of MySQL Connector/Python and need different code paths depending on the version.

The API level, parameter style, and thread safety properties do not, in general, change. They are related to the Python Database API specification ( https://www.python.org/dev/peps/pep-0249/ ) that the mysql.connector module implements. These three properties are required global properties of the module.

The output when using MySQL Connector/Python 8.0.11 is

PS: Server 1> python listing_1_1.py
MySQL Connector/Python version: 8.0.11
Version as tuple:
(8, 0, 11, '', 1)
API Level: 2.0
Parameter style: pyformat
Thread safe: 1

MySQL Server

MySQL Connector/Python is not worth much on its own. Unless you have a MySQL Server instance to connect to, you will be limited to doing things such as checking the version, as in the example in the previous section. So, if you do not already have access to an installation of MySQL Server, you will also need to install it. This section will give a brief overview of installing and configuring MySQL Server.

Installation

The installation process of MySQL Server is similar to the steps described for MySQL Connector/Python if you use the MySQL Installer or the MySQL Yum repository. In both of these cases, the installer will set up MySQL for you. Additionally, there is an option to install using a zip archive on Microsoft Windows or a tar archive on Linux, macOS, Oracle Solaris, and FreeBSD.

Note

This discussion assumes a new installation. If you already have MySQL installed, you can also choose to upgrade. However, if your current MySQL installation is not from the MySQL repository, it is best to remove the existing installation first to avoid conflicts, and then do a clean install.

Since the installation steps are so similar for MySQL Server compared to MySQL Connector/Python when using an installer, this discussion will focus on installing using a zip or tar archive. The discussion about retrieving the password that is set for the administrator account (root@localhost) is also relevant when using installers on Linux. Since MySQL Installer is an interactive installer, it will ask you what the password should be and set it for you.

Tip

The discussion about the installation of MySQL Server in my blog only covers some of the basics. For the full installation instructions, see https://dev.mysql.com/doc/refman/en/installing.html and https://dev.mysql.com/doc/refman/en/data-directory-initialization-mysqld.html .

Using a zip or tar archive can be particularly useful if you need multiple different versions installed on the same computer because it allows you to locate the installation where you like. If you choose this approach, you need to initialize the data directory manually. An example of doing this on Microsoft Windows can be seen in the following example:

PS: Python> D:\MySQL\mysql-8.0.11-winx64\bin\mysqld
              --basedir=D:\MySQL\mysql-8.0.11-winx64
              --datadir=D:\MySQL\Data_8.0.11
              --log_error=D:\MySQL\Data_8.0.11\error.log
              --initialize

The command is split over several lines to improve readability. Make sure you combine all of the parts into one line when you execute it. The command may take a little time to complete, particularly if you are installing on a non-memory-based (i.e. a spinning) disk drive.

The command in Linux and other Unix-like systems is very similar except that the -- user option has been added:

shell$ /MySQL/base/8.0.11/bin/mysqld \
          --basedir=/MySQL/base/8.0.11/ \
          --datadir=/MySQL/Data_8.0.11 \
          --log_error=/MySQL/Data_8.0.11/error.log \
          --user=mysql \
          --initialize

 The two commands use several arguments. They are:

• --basedir: This option tells where MySQL Server binaries, libraries, etc. are installed. This directory includes a bin, lib, share, and more subdirectories with the files required by MySQL Server.
• --datadir: This option tells where to store the data. This is the directory that is initialized by the command. This directory must either not exist or be empty. If it does not exist, the --log_error option cannot point to a file inside the data directory.
• --log_error: This option tells where to write the log messages to.
• --user: On Linux and Unix, this option is used to tell which user MySQL will be executed as. This is only required (but is allowed in general) if you initialize the data directory as the root user. In that case, MySQL will ensure that the newly created files are owned by the user specified by the --user argument. The user traditionally used is the mysql user, but for personal test instances, you can also use your normal login user.
• --initialize: This option tells MySQL to initialize the data directory.

The initialization includes setting the password for the root@localhost account. The password is random and can be found in the error log; this also applies when MySQL has been installed using, for example, RPMs. However, MySQL Installer will ask for the password during the installation and set it. If you are using macOS, the password will also be shown in the notifications. An example of the error log that includes the temporary password is

2018-03-11T05:01:08.871014Z 0 [System] [MY-010116] D:\MySQL\mysql-8.0.4-rc-winx64\bin\mysqld.exe (mysqld 8.0.4-rc) starting as process 3964 ...
2018-03-11T05:01:20.240818Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-03-11T05:01:20.259178Z 5 [Note] [MY-010454] A temporary password is generated for root@localhost: fj3dJih6Ao*T

 You need this password the first time you connect. Once you have connected, you must change the password before you can execute general queries because the random one generated during the installation is marked as expired. You change the password using the ALTER USER statement, as shown in Listing 3.

PS: Python> D:\MySQL\mysql-8.0.11-winx64\bin\mysql --user=root --password
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; org.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark ofOracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '&lknjJ2lAc1)#';
Query OK, 0 rows affected (0.15 sec)

Listing 3 Changing the Password of the root@localhost Account

Ensure that you choose a password that is hard for others to guess. You can also use the command to change other settings for the user such as the SSL requirements, the authentication plugin, and so on.

Tip

The default authentication plugin in MySQL 8.0 is the caching_sha2_password plugin. It provides good security because it is based on sha256 salted hashes. At the same time, the caching makes it perform well. However, since it is a new authentication plugin, older connectors and clients including MySQL Connector/Python 2.1.7 and earlier, the MySQL command-line client from MySQL Server 5.7, and third-party connectors such as those for PHP and Perl do not at the time of writing support the caching_sha2_password plugin. If you need to connect using one of these connectors or clients, you can use the older (and less secure because it is sha1-based and not salted) mysql_native_password plugin instead. See https://dev.mysql.com/doc/refman/en/create-user.html and https://dev.mysql.com/doc/refman/en/alter-user.html for more information about the syntax of the CREATE USER and ALTER USER statements.

The instance will, unless you used MySQL Installer , use the default configuration. The final thing to consider is how to change the configuration.

Configuration

In some cases, it is necessary to change the configuration of the newly installed MySQL Server instance. In general, the default values are a great starting point. Obviously, for a production server, there are some changes that are required, but often it is a case of few changes are better than many changes. For the purpose of the examples in my blog, the default configuration works well.

That said, you already saw some non-default settings in the examples where the data directory was initialized manually. Additionally, if you are developing an application that will be deployed to production, it is recommended to use a configuration that is as close to the production configuration as possible to avoid gotchas due to differences. While this, of course, does not mean the desktop you are developing on should be able to allocate half a terabyte of memory to the InnoDB buffer pool just because the production server is using that, you can use a configuration that is similar but scaled down.

Tip

You can read more about configuring MySQL in the Reference Manual, including the complete list of options, at https://dev.mysql.com/doc/refman/en/server-system-variables.html and https://dev.mysql.com/doc/refman/en/option-files.html .

In general, it is also best to use a MySQL configuration file to set any required options. This avoids missing some options when starting MySQL. You can then start the MySQL daemon (mysqld on Linux/Unix and mysqld.exe on Microsoft Windows) with the --defaults-file option with the path to the configuration file. By convention, the MySQL configuration file is named my.ini on Microsoft Windows and my.cnf on other platforms.

If you are using Microsoft Windows and chose to install MySQL as a service, you will be starting and stopping the MySQL service through the control panel application (or letting Microsoft Windows do it automatically). In this case, a configuration file is even more useful because you can specify to use it in the service definition, which avoids modifying the service if you later want to change the MySQL configuration.

The configuration file follows the INI file format. The following is an example with the options from the initialization on Microsoft Windows from earlier in the section as well as the TCP port number:

[mysqld]
basedir   = D:\MySQL\mysql-8.0.11-winx64
datadir   = D:\MySQL\Data_8.0.11
log_error = D:\MySQL\Data_8.0.11\error.log
port      = 3306

 This concludes the discussion about installing and configuring MySQL Server. A related topic is how to create the database user that the application will use to connect to MySQL.

Creating the Application User

When an application connects to MySQL, it is necessary to specify the username to use for the connection. Additionally, MySQL takes the hostname where the connection comes from into account, so the account name for the user is formed as username@hostname. The user’s privileges determine what the user is allowed to do in the database.

MySQL Server has one standard user available for logins, the root@localhost user. This user has all privileges; that is, it is an administrator account. Applications should not use this user for several reasons, which will be explained in the following discussion.

The application should, in general, not have permission to do everything. For example, the application should not be allowed to access tables it does not need, and there is rarely a requirement for an application to manage users. Additionally, MySQL has a limit on the number of concurrent connections allowed (the max_connections configuration option). However, there is one extra connection reserved for a user with the CONNECTION_ADMIN (SUPER) privilege. So, if the application user has all privileges, it can block out the database administrator from investigating why all connections are in use.

It is beyond the scope of my blog to go into the details of the MySQL privilege system. The main takeaway is that you should assign the minimum required privileges to your users, including during the development phase, because it is much easier to add new privileges as required than remove unnecessary privileges when you are ready to deploy the application.

Tip

It is worth getting familiar with MySQL security features including the access privilege system and user account management. The Security chapter in the MySQL Reference Manual is an excellent source: https://dev.mysql.com/doc/refman/en/security.html .

The following SQL statements can be used to create a test user who has the required privileges for the examples in this blog:

mysql> CREATE USER 'pyuser'@'localhost'
              IDENTIFIED BY 'Py@pp4Demo';
mysql> GRANT ALL PRIVILEGES
             ON world.*
             TO 'pyuser'@'localhost';
mysql> GRANT ALL PRIVILEGES
             ON py_test_db.*
             TO 'pyuser'@'localhost';

It is assumed that the test programs will be executed on the same host as where MySQL is installed. If this is not the case, replace localhost with the hostname where the test programs are executed. ALL PRIVILEGES in the GRANT statements gives all available privileges on the schema (database) level, but the administrative privileges are not included. This will still be more than the typical application needs, but it is used here for simplicity and to allow demonstrating queries that are not typically executed from within an application.

The password has been chosen as Py@pp4Demo. This is not a very strong password, and it is strongly recommended to use a different password that is more difficult to guess.

If you want to play with the world_x sample database, you will also need the following privileges:

mysql> GRANT ALL PRIVILEGES
             ON world_x.*
             TO 'pyuser'@'localhost';

However, none of the examples discussed in my blog use the world_x sample database. The installation instructions for the world_x sample database are very similar to those in the next step, which is to install some sample data for the code examples in my next articles.

Installing the world Sample Database

Throughout the blog, the world sample database is used for several of the examples. The example databases are considered part of the “other MySQL documentation” and can be accessed from https://dev.mysql.com/doc/index-other.html . The world database can be downloaded either as a gzip file or zip file; either way, after decompression, there is a single file: world.sql.

Note

There is the world database and the world_x database. My next article use the world database. The world_x database is not required but can optionally be installed using similar steps as shown here if you would like to have it for your own testing.

The world.sql file is self-contained. It will drop the world schema if it exists and recreate it with three tables: country, countrylanguage, and city, including some sample data. The easiest way to apply the world.sql file is to use the mysql command-line client ( https://dev.mysql.com/doc/refman/en/mysql.html ) from the same directory as where the world.sql file is located:

shell$ mysql --user=pyuser --password \
             --host=127.0.0.1 --port=3306 \
             --execute="SOURCE world.sql"
Enter password:

 This assumes that the mysql binary is in the execution search path; otherwise, the full path must be used. On Microsoft Windows, keep the whole command on the same line and remove the backslashes. The resulting tables are outlined in Listing 4.

mysql> SHOW TABLES FROM world;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM world.city;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE world.country\G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM world.country;
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE world.countrylanguage\G
*************************** 1. row ***************************
       Table: countrylanguage
Create Table: CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM world.countrylanguage;
+----------+
| COUNT(*) |
+----------+
|      984 |
+----------+
1 row in set (0.00 sec)

Listing 4. The Tables of the world Sample Database

Tip

For more information about the world sample database including installation instructions, see https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html .

Before concluding this blog, a word regarding the code examples is required.

Code Examples

There are a number of example programs in my blog. The programs have been tested with Python 3.6. For other Python versions, including Python 2.7 from Oracle Linux 7/Red Hat Enterprise Linux (RHEL) 7/CentOS 7, the examples will work with minor modifications. No changes are required for the MySQL Connector/Python-specific parts.

In Python 2, it is recommended to load the print function from__future__:

from __future__ import print_function

Additionally the UTF-8 string handling is different in Python 2, so it may be necessary to use the encode() method to print strings. For example:

print(
  "{0:15s}   {1:^7s}   {2:4.1f}".format(
    city['Name'].encode('utf8'),
    city['CountryCode'].encode('utf8'),
    city['Population']/1000000
  )
)

The examples using the mysql.connector module assume that a file named my.ini is present in the directory where Python is executed with the connection options required to connect to MySQL Server. An example configuration file is

[connector_python]
user     = pyuser
host     = 127.0.0.1
port     = 3306
password = Py@pp4Demo

The examples using the mysqlx module store the configuration in a file named config.py, which is also located in the same directory where Python is executed. The example configuration is

connect_args = {
  'host': '127.0.0.1',
  'port': 33060,
  'user': 'pyuser',
  'password': 'Py@pp4Demo',
};

The coding style in the examples is optimized for print and in particular eBook readers such as Kindle. Since this leaves very little real estate to work with, the lines have in general been kept below 40 characters with “long” lines up to 50 characters to minimize the amount of wrapping. The downside is that this means it has not really been possible to follow a standard coding style such as the one specified in PEP 8. It is recommended to follow PEP 8 or another well-established coding standard in your own projects.

All example programs that appear in a listing are available for download. The file name reflects the listing number; for example, the code in Listing 2 can be found in the file listing_1_2.py. 

This concludes the installation and preparations. The next step is to create connections from MySQL Connector/Python to MySQL Server, which is the topic of the next article.

Summary

This chapter got you up and running. First, MySQL Connector/Python was introduced. The latest GA release series is version 8.0, the same as for most other MySQL products. MySQL products are available as a Community Edition and a commercial Enterprise Edition . For MySQL Connector/Python, the main difference is the license and support, and either edition can be used with my blog.

MySQL Connector/Python has three APIs: two legacy APIs that only work with SQL statements and a new API called the X DevAPI that supports both NoSQL and SQL queries. How to use the three APIs is the topic of the rest of the blog.

In order to get started, you downloaded and installed both MySQL Connector/Python and MySQL Server. There was a brief discussion about configuring MySQL Server, instructions on how to create a user that can be used for my blog, how to install the test data, and a word on the code examples.

You are ready to use MySQL Connector/Python. The next article will show you how to connect using the API in the Connector/Python API (the mysql.connector module).

Вас заинтересует / Intresting for you:

Connecting with a MySQL databa... 3358 views Valerij Sun, 26 Aug 2018, 11:27:52

Importing MySQL module for Pyt... 4770 views Valerij Sun, 26 Aug 2018, 09:30:11

Determining characteristics of... 1872 views Valerij Sun, 26 Aug 2018, 12:52:37

Installing MySQL 8 using YUM /... 1961 views Doctor Thu, 06 Dec 2018, 06:47:43