Using SQLAlchemy with MySQL 8

I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.

In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.

Information

The examples in this blog uses MySQL Server 8.0.15, MySQL Connector/Python 8.0.15, and SQLAlchemy 1.2.18. It should be possible to reuse the instructions with other MySQL versions as well, except in older MySQL versions you need to explicitly use the utf8mb4 character set.

If you do not want to read through the whole blog, the main thing for using MySQL and MySQL Connector/Python with SQLAlchemy is to create the engine with dialect set to mysql and the driver to mysqlconnector:

engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://<user>:<password>@<host>:<port>/<default_db>...')

Installing MySQL Server

There are several ways to install MySQL Server and which one is the best depends on your circumstances and preferences. For the sake of this blog, I will show how MySQL Server can be installed on Oracle Linux/RHEL/CentOS 7 using RPMs and on Microsoft Windows using MySQL Installer. For more options, see the installation chapter in the reference manual. Let’s look at the Linux installation first.

RPM Install on Enterprise Linux

MySQL provides repositories for several Linux distributions including the Oracle Linux/RHEL/CentOS family. This makes it easy to install MySQL. The step to install the repository definition is:

shell$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpmLoaded plugins: langpacks, ulninfo
Repository ol7_developer_EPEL is listed more than once in the configuration
mysql80-community-release-el7-1.noarch.rpm                                     |  25 kB  00:00:00     
Examining /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm: mysql80-community-release-el7-1.noarch
Marking /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
======================================================================================================
 Package                       Arch       Version   Repository                                   Size
======================================================================================================
Installing:
 mysql80-community-release     noarch     el7-1     /mysql80-community-release-el7-1.noarch      31 k
 
Transaction Summary
======================================================================================================
Install  1 Package
 
Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-1.noarch                                             1/1
  Verifying  : mysql80-community-release-el7-1.noarch                                             1/1
 
Installed:
  mysql80-community-release.noarch 0:el7-1
 
Complete!

Now, you can install MySQL Server. There are several RPMs to choose from and which you need depends on which other features you need to use. A common set of RPMs can be installed as:

shell$ sudo yum install mysql-community-server mysql-community-libs \
            mysql-community-libs-compat mysql-community-common mysql-community-client
...

Note

If you have another MySQL installation, it will be upgraded to the latest release (at the time of writing 8.0.15).

On the first start, the data directory will be initialized:

shell$ sudo systemctl start mysqld

To keep a fresh installation secure, a random password has been set for the root user. This can be found from the MySQL error log:

shell$ sudo grep password /var/log/mysqld.log 
2018-11-05T08:05:09.985857Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: KWNfuA!1r:PF

Use this password to connect to MySQL and update the password (please use a strong password):

shell$ mysql --user=root --password
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle 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 'Kij0@jDi~Faf4';
Query OK, 0 rows affected (0.13 sec)

MySQL is now ready for use. Before continuing, I will show an example of installing MySQL on Microsoft Windows.

Microsoft Windows

On Microsoft Windows an easy way to install MySQL is to use the MySQL Installer. The installer can be downloaded from the MySQL download site. The MySQL Installer can be used to install most MySQL products. If you start MySQL Installer for the first time, you will be taken directly to the screen to choose the products to install; if you already have installed products, you will need to choose to add new products.

On the Select Products and Features screen, choose to install MySQL Server 8.0 (MySQL Installer will list the latest release from the list of available products):

Installing MySQL Server 8.0.13 from MySQL Installer
Installing MySQL Server from MySQL Installer – The screen shot is for 8.0.13, but other than the version number, it is the same for 8.0.15.

Optionally, you can filter the list of products. Feel free to choose other products you want. MySQL Notifier can be useful for starting and stopping MySQL, if you do not plan to have MySQL running at all times. You can also install MySQL Connector/Python this way, however for this blog a different method will be used.

Follow the installation wizard. For this blog, the default choices will work, though during the configuration you may want to ensure Open Windows Firewall ports for network access is unchecked unless you need remote access.

Preparing MySQL Server

While MySQL is now ready to work with SQLAlchemy, you will likely want to do a few more preparation steps. Here creating the MySQL user and schema (database) used by your application will be covered.

An example of creating the user pyuser@localhost and give it all privileges to the sqlalchemy schema and to create the sqlalchemy schema is:

mysql> CREATE USER pyuser@localhost IDENTIFIED BY 'Py@pp4Demo';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON sqlalchemy.* TO pyuser@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE SCHEMA sqlalchemy;
Query OK, 1 row affected (0.01 sec)

This will allow the pyuser user to connect from the same host as MySQL Server is installed by authenticating with the password Py@app4Demo.

Installing MySQL Connector/Python and SQLAlchemy

Both MySQL Connector/Python and SQLAlchemy can be installed in a platform independent way using the pip command. Since Python 2.7 is soon end of life, I will assume Python 3.6 in this blog. (MySQL Connector/Python 8.0.13 and later also supports Python 3.7.)

If you do not have Python 3.6 installed on Oracle Linux/RHEL/CentOS 7, you can easily install it for example from for EPEL repository. Assuming you have configured the EPEL repository, the following steps install Python 3.6, enable pip, and update pip to the latest version:

shell$ yum install python36
shell$ python3.6 -m ensurepip
shell$ python3.6 -m pip install --upgrade pip

You can now use python3.6 to invoke Python 3.6. In the following, replace python with python3.6 if you have installed Python 3.6 in this way.

To install the latest MySQL Connector/Python release (currently 8.0.15):

PS> python -m pip install mysql-connector-python
Collecting mysql-connector-python
  Using cached https://files.pythonhosted.org/packages/31/45/ef8cf013918108f508a1a1bb5539abaff5f78f3a569f7fa30232967713c9/mysql_connector_python-8.0.15-cp36-cp36m-win_amd64.whl

Collecting protobuf>=3.0.0 (from mysql-connector-python)
  Downloading https://files.pythonhosted.org/packages/5d/5c/476f473c2efc0a8d9fd7185e6c08dcbd21c469698e2a80487fa054b8c5ba/protobuf-3.7.0-cp36-cp36m-win_amd64.whl (1.1MB)
    100% |████████████████████████████████| 1.1MB 6.6MB/s
Requirement already satisfied: six>=1.9 in c:\users\myuser\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\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-connector-pytho
n) (28.8.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.15 protobuf-3.7.0

Similar for installing SQLAlchemy:

PS> python -m pip install SQLAlchemy
Collecting SQLAlchemy
  Downloading https://files.pythonhosted.org/packages/21/ed/7eb53305b43ca51774a458d7c292f3bc7664d7a9bbb5bac4149fa34756b9/SQLAlchemy-1.2.18.tar.gz (5.7MB)
    100% |████████████████████████████████| 5.7MB 3.3MB/s
Installing collected packages: SQLAlchemy
  Running setup.py install for SQLAlchemy ... done

That’s it. Now you are ready to use SQLAlchemy with MySQL Connector/Python 8 and MySQL Server 8.

SQLAlchemy Example

With MySQL and SQLAlchemy installed, it is straight forward to use SQLAlchemy in you application. Here, I will just show a very simple example based on the tutorial in the official SQLAlchemy manual.

The first step is to import the necessary parts of SQLAlchemy. In this example, only sqlalchemy itself and declarative_base from sqlalchemy.ext.declarative are required:

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

The second step is to define MySQL as the engine and that you want to connect using MySQL Connector/Python:

# Define the MySQL engine using MySQL Connector/Python
engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://pyuser:Py@pp4Demo@localhost:3306/sqlalchemy',
    echo=True)

The definition of MySQL and MySQL Connector Python happens in line 6. mysql defines that you are using MySQL as the database (the dialect), and mysqlconnector tells that you want to use MySQL Connector/Python as the driver. The rest of the line defines the connection options. In this case you have user:password@host:port/default_db. You can add more options if you need it. So, in this case the following options have been set:

  • User: pyuser
  • Password: Py@app4Demo
  • Host: localhost
  • Port: 3306
  • Default database: sqlalchemy

Avoid

Never hard code the connection parameters into your application. Particularly the password is an absolutely no go. It is done here to keep the example simple, but doing so in an actual application makes deployments hard and pose a severe security issue.

See also the documentation of the engine configuration in the SQLAlchemy manual for more information.

The argument echo=True makes SQLAlchemy print each SQL statement it executes. This can be useful when testing.

The third step is to define and create a table – in this example the users table:

# Define and create the table
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(length=50))
    fullname = sqlalchemy.Column(sqlalchemy.String(length=50))
    nickname = sqlalchemy.Column(sqlalchemy.String(length=50))

    def __repr__(self):
        return "<User(name='{0}', fullname='{1}', nickname='{2}')>".format(
                            self.name, self.fullname, self.nickname)

Base.metadata.create_all(engine)

MySQL requires that you specify the maximum number of characters for varchar columns, which is the data type used when specifying sqlalchemy.String(). So, the length argument is passed. (The length argument is also the first argument to sqlalchemy.String(), so you do not need to specify explicitly that it is the length.)

The call to Base.metadata.create_all() tells SQLAlchemy to create the underlying database table, if it does not already exist. Otherwise the existing table will be used.

The fourth step is to add a user. This requires a session. Once the session is created, you can add the user to it:

# Create a session
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()

# Add a user
jwk_user = User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')
session.add(jwk_user)
session.commit()

There are two things, I will like you to pay attention to here. When defining the jwk_user in line 31, the nickname is set to be the dolphin emoji. This emoji requires four bytes in UTF-8 (0xF09F90AC). In older versions of MySQL (5.7 and earlier), you would need to explicitly change the character set to handle UTF-8. In MySQL 8, however, the four byte variant of UTF-8 (utf8mb4) is the default, so the dolphin emoji will work out of the box.

Tip

If you are interested in more information about the recommendation of which character set to use in MySQL, I wrote a blog about that last year: Which Character Set Should You Use in MySQL?

The other thing is that in order to persist the new user, you need to call session.commit(). The session works as a transaction here, so the changes will not be persisted until it is explicitly committed.

The fifth – and final – step is to query the data just saved:

# Query the user
our_user = session.query(User).filter_by(name='jesper').first()
print('\nOur User:')
print(our_user)
print('Nick name in hex: {0}'.format(our_user.nickname.encode('utf-8')))

The query finds the first user with the name set to “jesper”. Because the dolphin emoji tends not to be displayed correctly in many shells, the byte sequence in hex is also printed, so it can be confirmed it is indeed the dolphin emoji that was retrieved.

The output of the entire program is:

2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,265 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(50),
        fullname VARCHAR(50),
        nickname VARCHAR(50),
        PRIMARY KEY (id)
)


2019-03-03 14:02:57,280 INFO sqlalchemy.engine.base.Engine {}
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine COMMIT
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, nickname) VALUES (%(name)s, %(fullname)s, %(nickname)s)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine {'name': 'jesper', 'fullname': 'Jesper Wisborg Krogh', 'nickname': '&#x1f42c;'}
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine COMMIT
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname
FROM users
WHERE users.name = %(name_1)s
 LIMIT %(param_1)s
2019-03-03 14:02:57,312 INFO sqlalchemy.engine.base.Engine {'name_1': 'jesper', 'param_1': 1}

Our User:
<User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')>
Nick name in hex: b'\xf0\x9f\x90\xac'

As it can be seen from the last two lines, the dolphin emoji was saved and retrieved correctly.

Complete Example Code

For completeness, here is the entire example program:

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

# Define the MySQL engine using MySQL Connector/Python
engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://pyuser:Py@pp4Demo@localhost:3306/sqlalchemy',
    echo=True)

# Define and create the table
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(length=50))
    fullname = sqlalchemy.Column(sqlalchemy.String(length=50))
    nickname = sqlalchemy.Column(sqlalchemy.String(length=50))

    def __repr__(self):
        return "<User(name='{0}', fullname='{1}', nickname='{2}')>".format(
                            self.name, self.fullname, self.nickname)

Base.metadata.create_all(engine)

# Create a session
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()

# Add a user
jwk_user = User(name='jesper', fullname='Jesper Wisborg Krogh', nickname='&#x1f42c;')
session.add(jwk_user)
session.commit()

# Query the user
our_user = session.query(User).filter_by(name='jesper').first()
print('\nOur User:')
print(our_user)
print('Nick name in hex: {0}'.format(our_user.nickname.encode('utf-8')))

Enjoy using MySQL, MySQL Connector/Python, and SQLAlchemy.

MySQL Connector/Python 8.0.14+: Changed Expression Handling in mysqlx

The X DevAPI allows you to work with JSON documents and SQL tables at the same time. Furthermore, the CRUD style API is more intuitive than SQL statements for some programmers. Either way, the X DevAPI allows you to mix JSON documents, SQL tables, CRUD methods, and SQL statements to give you the best of all worlds. In MySQL Connector/Python, the X DevAPI is implemented in the mysqlx module.

This blog will look at how MySQL Connector/Python handles expressions, and how you in version 8.0.14 and later need to use the mysqlx.expr() method to explicitly define expressions.

Information

The changed way to work with expressions does not apply when defining fields. In that case, you can still specify the expression inline.

Expression Handling

One original feature of the X DevAPI in MySQL Connector/Python was that expressions were automatically handled when you inlined them into statement definitions. For example, you could increase the population like:

result = countryinfo.modify("Name = :country") \
    .set("demographics.Population",
         "CAST(FLOOR(demographics.Population * 1.10) AS unsigned)") \
    .bind("country", country_name) \
    .execute()

Notice the expression in line 3.

While this was nice, it also caused some problems such as Bug 92416 where regular data could end up being interpreted as an expression by mistake. For example:

schema = db.create_schema("db1")
mycol = schema.create_collection("mycol")
mycol.add({"A": "(@)"}).execute()

In this example (@) would be interpreted as an expression even though it should be taken as a string.

The solution has been to require the mysqlx.expr() method to be used to define all expressions. This then allows MySQL to interpret all strings as literals. While it does remove the shortcut method, it also removes any ambiguities and thus makes the code safer.

Tip

The mysqlx.expr() method is also available in MySQL Connector/Python 8.0.13 and earlier as well as other connectors. If you have not yet upgraded to the latest version, it is recommended to start using mysqlx.expr() now to avoid problems when upgrading.

Let’s look at an example to better understand how the change works.

Example

As an example, consider an application that uses the world_x database and updates the population of a country with 10%. This can be done using the following expression:

CAST(FLOOR(demographics.Population * 1.10) AS unsigned)

At first, the application will use the inline method to specify the expression, then we will look at changing this to work in MySQL Connector/Python 8.0.14 and later.

Inline Expression

The source code for updating the population using an inline expression can be seen in the following sample program:

import mysqlx
import mysql.connector

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

print("Using inline expression.")
print("MySQL Connector/Python {0}".format(mysql.connector.__version__))

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("world_x")
countryinfo = schema.get_collection("countryinfo")
country_name = "Australia"

db.start_transaction()

# Get the population of the country
population = countryinfo.find("Name = :country") \
    .fields("demographics.Population AS Population") \
    .bind("country", country_name)

before = population.execute()
print("Before ...: {0}".format(before.fetch_one()['Population']))

# Update the population
result = countryinfo.modify("Name = :country") \
    .set("demographics.Population",
         "CAST(FLOOR(demographics.Population * 1.10) AS unsigned)") \
    .bind("country", country_name) \
    .execute()

after = population.execute()
print("After ....: {0}".format(after.fetch_one()['Population']))

# Leave the data in the same state as before the changes
db.rollback()
db.close()

The population is updated in the statement defined and executed in lines 30-34. The population is fetched both before and after and printed together with the MySQL Connector/Python version. At the end, the transaction is rolled back, so it is possible to execute the program several times while getting the same output.

Information

The mysql.connector module is only included in order to print the MySQL Connector/Python version.

The output using MySQL Connector/Python 8.0.13 is:

Using inline expression.
MySQL Connector/Python 8.0.13
Before ...: 18886000
After ....: 20774600

This is as expected. However, in MySQL Connector/Python 8.0.14 and later, the result is quite different:

Using inline expression.
MySQL Connector/Python 8.0.15
Before ...: 18886000
After ....: CAST(FLOOR(demographics.Population * 1.10) AS unsigned)

Now the expression is taken as a literal string – oops.

Warning

This also highlights that you must be careful when working with a schemaless data model. The database will not stop you from putting wrong data types into your documents.

Let’s look at how this can be fixed.

Explicit Expressions

The solution to the issue, we just saw, is to use explicit expressions. You can do that using the mysqlx.expr() method. This returns an expression object that you can use in your statements. The same example as before – but using an explicit expression – is:

import mysqlx
import mysql.connector

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

print("Using explicit expression.")
print("MySQL Connector/Python {0}".format(mysql.connector.__version__))

db = mysqlx.get_session(**connect_args)
schema = db.get_schema("world_x")
countryinfo = schema.get_collection("countryinfo")
country_name = "Australia"

db.start_transaction()

# Get the population of the country
population = countryinfo.find("Name = :country") \
    .fields("demographics.Population AS Population") \
    .bind("country", country_name)

before = population.execute()
print("Before ...: {0}".format(before.fetch_one()['Population']))

# Update the population
expr = mysqlx.expr("CAST(FLOOR(demographics.Population * 1.10) AS unsigned)")
result = countryinfo.modify("Name = :country") \
    .set("demographics.Population", expr) \
    .bind("country", country_name) \
    .execute()

after = population.execute()
print("After ....: {0}".format(after.fetch_one()['Population']))

# Leave the data in the same state as before the changes
db.rollback()
db.close()

The only change is the definition of the expression in line 30 and the use of it in line 32.

Tip

The expression object can be re-used if you need the same expression in several statements.

Now, MySQL Connector/Python 8.0.13 and 8.0.15 updates the population to the same value. First 8.0.13:

Using explicit expression.
MySQL Connector/Python 8.0.13
Before ...: 18886000
After ....: 20774600

Then 8.0.15:

Using explicit expression.
MySQL Connector/Python 8.0.15
Before ...: 18886000
After ....: 20774600

Further Reading

If this blog has caught you interest in MySQL Connector/Python whether you are looking at using the traditional Python Database API specification (PEP 249) or the new X DevAPI, then I have written MySQL Connector/Python Revealed published by Apress.
The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.

Enjoy.

MySQL Connector/Python 8.0.15: allow_local_infile Disabled by Default

MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.

The statement to load the data is LOAD DATA INFILE. By default the file must be server-side and MySQL Server will load it without involving the connections (other than for submitting the query and returning the result). However, there is also an optional keyword LOCAL that can be used to tell MySQL that a the file is located client-side: LOAD DATA LOCAL INFILE. It is this local variant that is the topic of this blog.

Dolphin

By default, MySQL Server 8 does not allow loading client-side files using the LOAD DATA LOCAL INFILE statement. (Similarly the mysql command-line client has the feature disabled by default.) Why? The reason is that there are security implications enabling the feature. For that reason, it is recommended to disable the feature unless you know you need it, and then only allow it for as short time as possible.

Advice

The server-side variant is safer as long as the server-side file system is secured. Additionally, MySQL will only read server-side files from the directory (including subdirectories) specified with the secure_file_priv option. Make sure the option is not set to an empty value as that will allow MySQL to read any file.

In MySQL Connector/Python, the mysql.connector module includes the option allow_local_infile which determines whether MySQL Connector/Python will allow the server to request a client-side file. In version 8.0.15 the default value for allow_local_infile has been changed from True to False to align with the default value for MySQL Server and the mysql command-line client.

This means that if you attempt to execute LOAD DATA LOCAL INFILE in MySQL Connector/Python 8.0.15 without explicitly enabling it, a ProgrammingError exception is raised:

mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version

(Admitted, the error message is not particularly good at telling what you are doing wrong.) What do you do, if you need to load client-side data files? These are the steps:

  1. Enable the local_infile option in MySQL Server. This only exists for the global scope. You can temporary enable it with SET GLOBAL local_infile = ON;, then disable it again when you are done by setting the variable to OFF.
  2. Enable allow_local_infile in your MySQL Connector/Python program. I will show an example of how to do this below. Again, it is recommended to only have it enabled when needed.

An example of creating a connection with allow_local_infile enabled is:

import mysql.connector

# Create connection to MySQL
db = mysql.connector.connect(
  option_files="my.ini",
  allow_local_infile=True
)

This example uses an INI formatted configuration file to specify the connection options, and the allow_local_infile option is added explicitly.

If you are interested in learning more about using LOAD DATA [LOCAL] INFILE with MySQL Connector/Python, then my book MySQL Connector/Python Revealed (Apress) has a section describing how to load data from files including an example. The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.

MySQL Connector/Python 8.0.14 X DevAPI: Default Schema

The MySQL X DevAPI is the new API that provides a uniform API across the supported programming languages. It has from the beginning supported that you can specify a default schema, when you connect. Originally it was not used for SQL statements. Starting with MySQL 8.0.14 the feature has been extended, so SQL statements take the default schema into consideration as well. This blog will explore how this works using MySQL Connector/Python. If you use a different programming language, the change will work in a similar way.

In order to explore the feature, a sample program is needed. A simple program that prints the MySQL Connector/Python version, queries the city table in the default schema, and either catches the exception or prints the resulting rows will work:

import mysql.connector
import mysqlx
from mysqlx.errors import OperationalError
import pprint

printer = pprint.PrettyPrinter(indent=3)

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

print("MySQL Connector/Python {0}".format(mysql.connector.__version__))

db = mysqlx.get_session(connect_args)
try:
    result = db.sql("SELECT * FROM city WHERE ID = 130").execute()
except OperationalError as err:
    print("Error: {0}".format(err))
    result = None
else:
    columns = [column.column_name for column in result.get_columns()]
    for row in result.fetch_all():
        row_data = dict(zip(columns, row))
        printer.pprint(row_data)

db.close()

Notice how the default schema is set in line 13.

If this program is executed with MySQL Connector/Python 8.0.13 or earlier, the query in line 20 will cause an OperationalError exception:

MySQL Connector/Python version: 8.0.13
Error: No database selected

However, if you execute the code with MySQL Connector/Python 8.0.14, the query executes and queries the city table in the world schema:

MySQL Connector/Python 8.0.14
{  'CountryCode': 'AUS',
    'District': 'New South Wales',
    'ID': 130,
    'Name': 'Sydney',
    'Population': 3276207}

This new behaviour is the same as you would be used to from the traditional API.

Tip

If you want to use the default schema with CRUD statements, you can retrieve the default schema with Session.get_default_schema(). That also works in earlier releases of MySQL Connector/Python.

If you want to read more about using the X DevAPI and MySQL Connector/Python in general, then I have written the book MySQL Connector/Python Revealed (see links in the sidebar to the right).

MySQL 8: Drop Several Stored Events, Procedures, or Functions

Maybe the biggest new feature in MySQL 8 is the new transaction data dictionary that improves the consistency of schema objects among other things. To further protect the data in the data dictionary, the data dictionary tables are hidden and their content only exposed through the Information Schema. (One exception is when you use the debug binary, then it is possible to get direct access to the data dictionary tables. This is not recommended at all on production systems!)

A side effect of the data dictionary tables being hidden is that those that have had a habit of manipulating the tables directly in MySQL 5.7 and earlier (I will not recommend doing that) will no longer be able to do so. Examples of manipulating the tables include dropping several stored events, procedures, and/or functions by deleting the corresponding rows in the mysql.event and mysql.proc tables.

Tip

I will use the word object to cover either an event, procedure, or function.

There are some discussions on Stack Overflow on how to do this task. See for example How to delete multiple events in mysql 8.0 database ? it was working in 5.7 but not in 8.0? and MySQL 8 – Remove All Stored Procedures in a Specific Database.

The obvious thought may be to write a stored procedure that can delete the objects, however that requires using a prepared statement which is not supported for DROP EVENT and similar:

mysql> SET @sql = 'DROP EVENT db1.event1';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_drop FROM @sql;
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

There is a feature request to implement this in Bug 42337.

So what to do instead? My suggestion is to use MySQL Shell. There you can generate the queries and use session.sql() to execute them without using a prepared statement. However, first some objects are needed.

Deleting events, functions, and procedures in MySQL Shell
Deleting events, functions, and procedures in MySQL Shell

Setup

Before we start looking at the options to drop several events, procedures, or functions, lets create some objects to work with. For the examples there are three events, three procedures, and three functions that can be created as follows:

mysql> CREATE SCHEMA db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1;
Database changed
mysql> CREATE EVENT event1 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE EVENT event2 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `event``3` ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE proc1() DO SLEEP(1);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE proc2() DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `proc``3`() DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION func1() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.40 sec)

mysql> CREATE FUNCTION func2() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE FUNCTION `func``3`() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.30 sec)

mysql> SELECT EVENT_SCHEMA, EVENT_NAME
         FROM information_schema.EVENTS
        WHERE EVENT_SCHEMA = 'db1';
+--------------+------------+
| EVENT_SCHEMA | EVENT_NAME |
+--------------+------------+
| db1          | event1     |
| db1          | event2     |
| db1          | event`3    |
+--------------+------------+
3 rows in set (0.01 sec)

mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
         FROM information_schema.ROUTINES
        WHERE ROUTINE_SCHEMA = 'db1';
+----------------+--------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |
+----------------+--------------+--------------+
| db1            | func1        | FUNCTION     |
| db1            | func2        | FUNCTION     |
| db1            | func`3       | FUNCTION     |
| db1            | proc1        | PROCEDURE    |
| db1            | proc2        | PROCEDURE    |
| db1            | proc`3       | PROCEDURE    |
+----------------+--------------+--------------+
6 rows in set (0.01 sec)

This also shows how the events and routines can be found from the Information Schema.

The third event, procedure, and function are named with a backtick before the digit. This is to ensure our delete method quotes the identifier names correctly. If you are deleting events and routines from a production system, you are encouraged to test carefully that you quote the identifiers correctly.

Now on to delete the objects.

MySQL Shell

MySQL Shell is one of the great recent additions to MySQL. It supports performing DBA and devops tasks using SQL, Python, and JavaScript. Having a full featured programing language available allows you to automate tasks that otherwise would have been cumbersome or impossible.

The Python and JavaScript modes support executing queries either using NoSQL CRUD methods or SQL statements. So, now the task of deleting events, procedures, or functions becomes as simple as executing a query against the Information Schema to find the names of the objects and then generate the queries in a loop. The following will use Python, but it can also be written in JavaScript if you prefer.

I will first go through dropping events, then functions and procedures.

Events – Interactive

It is possible to get the names using the select() CRUD method on the information_schema.EVENTS table:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> events = i_s.EVENTS \
       ->             .select("sys.quote_identifier(EVENT_SCHEMA) AS EventSchema", "sys.quote_identifier(EVENT_NAME) AS EventName") \
       ->             .where("EVENT_SCHEMA = 'db1'").execute().fetch_all();

mysql-py> events
[
    [
        "`db1`",
        "`event1`"
    ], 
    [
        "`db1`",
        "`event2`"
    ], 
    [
        "`db1`",
        "`event``3`"
    ]
]

First an object of the information_schema is stored in the i_s variable. This allows you to access the Information Schema EVENTS table using i_s.EVENTS. From here the select() CRUD method is used to find all events in the db1 schema.

Tip

If you have a different criteria for which events to delete, edit the argument to the where() method.

The sys.quote_identifier() function is used to ensure that the schema and event names are quoted correctly, so for example the backtick in event`3 is escaped correctly (by adding a second backtick).

Important

The sys.quote_identifier() only works guaranteed to work correctly when the ANSI_QUOTES SQL mode is not enabled. When ANSI quotes are used, double quotes (“) are using to quote identifiers though backticks are still supported.

Now events is a list of the three events that were created earlier. You can easily check which events where found by just “executing” events. With that, you can delete the events one by one in a loop. For this a query template is first defined, then used to add the name of the schema and event. Usually it is not safe to do that using the Python format() function, however here we have ensured the names are quotes appropriately by using sys.quote_identifier().

mysql-py> sql_fmt = "DROP EVENT {0}.{1}"
mysql-py> for event in events:
       ->     print(sql_fmt.format(*event))
       ->     session.sql(sql_fmt.format(*event)).execute()
       ->
DROP EVENT `db1`.`event1`
DROP EVENT `db1`.`event2`
DROP EVENT `db1`.`event``3`
Query OK, 0 rows affected (0.0038 sec)

That’s it. Not exactly a one liner, but not hard either.

This is fine if it is a one off that you need to delete some events, but what if this is a more regular task? MySQL Shell can accommodate for that as well.

Events – Function

I have previously discussed how you can use external libraries in MySQL Shell and even shown an example of using it to have auto-repeating reports. It can also be used to implement a function where you give the schema you want to delete events from as an argument.

Let’s look at how the code looks for that:

from mysqlsh import globals

def events_by_schema(schema, verbose=True):
    """Drops all events in the schema passed as an argument.

    Requires a connection to exist to the MySQL instance where the
    events are to be dropped.

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP EVENT statement executed.

    Returns the number of events dropped and False if an issue is
    encountered so no attempts are made to drop the events.
    """

    # Get the session and ensure it is connected
    if not globals.session.is_open():
        print("Please create a connection first.")
        return False

    # Define the query to get the events
    i_s = globals.session.get_schema("information_schema")
    stmt = i_s.EVENTS.select(
        "sys.quote_identifier(EVENT_SCHEMA) AS EventSchema",
        "sys.quote_identifier(EVENT_NAME) AS EventName")
    stmt = stmt.where("EVENT_SCHEMA = :schema")

    # Execute the query and check for warnings
    result = stmt.bind("schema", schema).execute()
    events = result.fetch_all()
    if (result.get_warnings_count() > 0):
        # Bail out and print the warnings
        print("Warnings occurred - bailing out:")
        print(result.get_warnings())
        return False

    # Drop the events and check for warnings after each event
    sql_fmt = "DROP EVENT {0}.{1}"
    for event in events:
        sql = sql_fmt.format(*event)
        if verbose:
            print(sql)

        drop_result = globals.session.sql(sql).execute()
        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return len(events)

Yes, this code is a lot longer than the previous example, but in reality it is the same with some extra checks added and obtaining the session (the connection to the MySQL instance) from the mysqlsh.globals module. Since the schema is now provided as an argument to the function, a placeholder and bind() are used to ensure it is safe to use the value in a query.

If you save the code in a file in the Python search path, you can import it into MySQL Shell. Recall from my earlier blog that you can include extra directories in your search path by updating mysqlshrc.py in one of the places where MySQL Shell looks for it; for example in ${HOME}/.mysqlsh on Linux or %APPDATA%\MySQL\mysqlsh on Windows. An example of the mysqlshrc.py file is:

import sys
sys.path.append("D:\MySQL\Shell\Python")

In this example, the code has been saved in the file delete_objects.py, so you can delete all events in the db1 schema like the following example:

mysql-py> import delete_objects
mysql-py> num_delete_events = delete_objects.events_by_schema('db1')
DROP EVENT `db1`.`event1`
DROP EVENT `db1`.`event2`
DROP EVENT `db1`.`event``3`

That completes the example for events. Now let’s turn to functions and procedures.

Procedures and Functions – Interactive

The example of deleting functions and/or procedures is very similar to what have just been seen for events, other than using the information_schema.ROUTINES table instead of information_schema.EVENTS to get the routine names and that your need to set a filter on ROUTINE_TYPE to specify whether you want procedures or functions (or both if the filter is absent). So, I will jump straight into the example by first dropping all functions, then all procedures in the db1 schema:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> functions = i_s.ROUTINES \
       ->                .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \
       ->                .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'FUNCTION'").execute().fetch_all()

mysql-py> functions
[
    [
        "`db1`",
        "`func1`"
    ], 
    [
        "`db1`",
        "`func2`"
    ], 
    [
        "`db1`",
        "`func``3`"
    ]
]

mysql-py> for function in functions:
       ->     print(sql_fmt.format(*function))
       ->     session.sql(sql_fmt.format(*function)).execute()
       ->
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`
Query OK, 0 rows affected (0.0684 sec)

Similar for the procedures:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> procedures = i_s.ROUTINES \
       ->             .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \
       ->             .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'PROCEDURE'").execute().fetch_all()

mysql-py> procedures
[
    [
        "`db1`",
        "`proc1`"
    ], 
    [
        "`db1`",
        "`proc2`"
    ], 
    [
        "`db1`",
        "`proc``3`"
    ]
]

mysql-py> sql_fmt = "DROP PROCEDURE {0}.{1}"

mysql-py> for procedure in procedures:
       ->     print(sql_fmt.format(*procedure))
       ->     session.sql(sql_fmt.format(*procedure)).execute()
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`
Query OK, 0 rows affected (0.0976 sec)

Again, it is possible to create a function, so the code can be re-used.

Functions and Procedures – Function

Since the code for deleting functions and procedures is so similar – it is just the filter and DROP FUNCTION versus DROP PROCEDURE that is the difference – it is simple to use the same function to delete either. In fact, it would not take much to combine it with events_by_schema() from above, but that will be left as an exercise for the reader (there is a hint in the next section using MySQL Connector/Python).

The code for the routines_by_schema() function is:

from mysqlsh import globals

def routines_by_schema(schema, routine_type=None, verbose=True):
    """Drops all routines of a given type in the schema passed as an
    argument. If no type is given, both functions and procedures are
    dropped. The default is to drop both.

    Requires a connection to exist to the MySQL instance where the
    routines are to be dropped.

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP FUNCTION|PROCEDURE statement executed.

    Returns the number of routines dropped and False if an issue is
    encountered so no attempts are made to drop the routines.
    """

    # Get the session and ensure it is connected
    if not globals.session.is_open():
        print("Please create a connection first.")
        return False

    # Define the query to get the routines
    i_s = globals.session.get_schema("information_schema")
    filters = ["ROUTINE_SCHEMA = :schema"]
    if routine_type is not None:
        filters.append("ROUTINE_TYPE = :type")
    stmt = i_s.ROUTINES.select(
        "sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema",
        "sys.quote_identifier(ROUTINE_NAME) AS RoutineName",
        "ROUTINE_TYPE")
    stmt = stmt.where(" AND ".join(filters))

    # Execute the query and check for warnings
    stmt = stmt.bind("schema", schema)
    if routine_type is not None:
        stmt = stmt.bind("type", routine_type)
    result = stmt.execute()
    routines = result.fetch_all()
    routines = result.fetch_all()
    if (result.get_warnings_count() > 0):
        # Bail out and print the warnings
        print("Warnings occurred - bailing out:")
        print(result.get_warnings())
        return False

    # Drop the routines and check for warnings after each routine
    sql_fmt = "DROP {2} {0}.{1}"
    for routine in routines:
        sql = sql_fmt.format(*routine)
        if verbose:
            print(sql)

        drop_result = globals.session.sql(sql).execute()

        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return len(routines)

The function takes an extra argument compared to events_by_schema(), so it is possible to either delete both functions and procedures (the default) or just one type. The use of the function is also similar to what have been seen before:

mysql-py> num_delete_functions = delete_objects.routines_by_schema('db1', 'FUNCTION')
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`

mysql-py> num_delete_procedure = delete_objects.routines_by_schema('db1', 'PROCEDURE')
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`

# Restore the functions and procedures
mysql-py> num_delete_routines = delete_objects.routines_by_schema('db1')
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`

The last thing is to look at how the same actions can be done with MySQL Connector/Python.

MySQL Connector/Python

Given how similar using Python in MySQL Shell is with MySQL Connector/Python, it is natural to make the functions available as a command-line tool or function for Connector/Python programs. The following shows an example of how that can be done – combining events_by_schema() and routines_by_schema() into one function. The source code is:

import mysqlx

def objects_by_schema(session, schema, object_type=None, verbose=True):
    """Drops all events, functions, and/or procedures in the schema
    passed as an argument.

    Requires a connection to exist to the MySQL instance where the
    events are to be dropped.

    The object_type can be None (drop all events, functions, and
    procedures - the default), "EVENT", "FUNCTION", or "PROCEDURE".

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP statement executed.

    Returns the number of events, functions, and procedures dropped
    as a dictionary or False if an issue is encountered so no attempts
    are made to drop the events.
    """

    # Get the session and ensure it is connected
    if not session.is_open():
        print("Please create a connection first.")
        return False

    # Get an object for the Information Schema
    # and whether ANSI quotes are used
    i_s = session.get_schema("information_schema")
    sql_mode = session.sql("SELECT @@session.sql_mode AS sql_mode").execute() \
                      .fetch_one()["sql_mode"]
    sql_modes = sql_mode.split(",")
    ansi_quotes = "ANSI_QUOTES" if "ANSI_QUOTES" in sql_modes else ""

    events = []
    # If dropping events, get a list of all events
    if object_type is None or object_type == "EVENT":
        events_table = i_s.get_table("EVENTS")
        stmt = events_table.select(
            "EVENT_SCHEMA AS ObjectSchema", "EVENT_NAME AS ObjectName",
            "'EVENT' AS ObjectType")
        stmt = stmt.where("EVENT_SCHEMA = :schema")

        # Execute the query and check for warnings
        result = stmt.bind("schema", schema).execute()
        events = result.fetch_all()
        if (result.get_warnings_count() > 0):
            # Bail out and print the warnings
            print("Warnings occurred - bailing out:")
            print(result.get_warnings())
            return False

    routines = []
    if object_type is None or object_type in ("FUNCTION", "PROCEDURE"):
        routines_table = i_s.get_table("ROUTINES")
        filters = ["ROUTINE_SCHEMA = :schema"]
        if object_type is not None:
            filters.append("ROUTINE_TYPE = :type")
        stmt = routines_table.select(
            "ROUTINE_SCHEMA AS ObjectSchema", "ROUTINE_NAME AS ObjectName",
            "ROUTINE_TYPE AS ObjectType")
        stmt = stmt.where(" AND ".join(filters))

        # Execute the query and check for warnings
        stmt = stmt.bind("schema", schema)
        if object_type is not None:
            stmt = stmt.bind("type", object_type)
        result = stmt.execute()
        routines = result.fetch_all()
        if (result.get_warnings_count() > 0):
            # Bail out and print the warnings
            print("Warnings occurred - bailing out:")
            print(result.get_warnings())
            return False

    # Drop the routines and check for warnings after each routine
    objects = events + routines
    sql_fmt = "DROP {0} {1}.{2}"
    objects_deleted = {
        "EVENT": 0,
        "FUNCTION": 0,
        "PROCEDURE": 0,
    }
    for obj in objects:
        objects_deleted[obj[2]] += 1
        sql = sql_fmt.format(obj[2],
                  mysqlx.helpers.quote_identifier(obj[0], ansi_quotes),
                  mysqlx.helpers.quote_identifier(obj[1], ansi_quotes))
        if verbose:
            print(sql)

        drop_result = session.sql(sql).execute()

        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return objects_deleted

if __name__ == "__main__":
    import sys
    import argparse
    import getpass

    # Handle the command line arguments
    parser = argparse.ArgumentParser(
        prog="delete_objects.py",
        description="Delete all events, functions, and/or procedures from a "
                   + "single schema.")

    parser.add_argument("--type", default="ALL",
        choices=["ALL", "EVENT", "FUNCTION", "PROCEDURE"],
        help="The object type to drop.")
    parser.add_argument("--schema", default=None,
        help="The schema to drop objects from.")
    parser.add_argument("--verbose", type=int, default=1,
        help="Whether to produce verbose output.")
    parser.add_argument("--user", default=None,
        help="The user to connect to MySQL as.")
    parser.add_argument("--host", default="127.0.0.1",
        help="The host to connect to MySQL on.")
    parser.add_argument("--port", type=int, default=33060,
        help="The mysqlx port to connect to MySQL on.")

    # Parse the arguments and get the password used when connecting to MySQL
    args = parser.parse_args(sys.argv[1:])
    object_type = args.type
    if args.type == "ALL":
        object_type = None
    verbose = True if args.verbose > 0 else False
    password = getpass.getpass("MySQL Password: ")

    # Connect to MySQL
    session = mysqlx.get_session(user=args.user, password=password,
                                 host=args.host, port=args.port)

    # Drop the objects
    objects_deleted = objects_by_schema(session, args.schema, object_type,
                                        verbose)
    if verbose:
        print(objects_deleted)

While the example is pretty extensive, it is not quite complete. There is only support for TCP connections using the user, password, host, and port options, and the password is always provided interactively (the most secure). Support for a configuration file and other options can be added if needed.

Instead of using the sys.quote_identifier() function, the schema and object names are quoted using the mysqlx.helpers.quote_identifier() function. Otherwise the program should be self explanatory given the previous examples. You can use python delete_objects.py --help to get help with the supported arguments.

MySQL X DevAPI Connection Pool with Connector/Python

If you have an application that need to use multiple connections to the MySQL database for short periods of times, it can be a good to use a connection pool to avoid creating a new connection and going through the whole authentication process every time a connection is needed. For the Python Database API (PEP249), MySQL Connector/Python has had support for connection pools for a long time. With the release of MySQL 8.0.13, the new X DevAPI also has support for connection pools.

MySQL Connector/Python X DevAPI connection pool code snippet.

This blog will first cover the background of the X DevAPI connection pool feature in MySQL Connector/Python. Then provide an example.

Background

You create a connection pool using the mysqlx.get_client() function. You may wonder why you are creating a client and not a pool? As will be shown later, there is a little more to this feature than just a connection pool. So, it makes sense to use a more generic term.

The get_client() function takes two arguments: The connection options and the client options. The connection options are the usual arguments defining which MySQL instance to connect to, authentication related options, how to connect, etc. The client options are the interesting ones in the discussion of a connection pool.

The client options is a dictionary or a JSON document written as a string. Currently, the only supported client options are the ones defining the connection pool. These are specified under the pooling field (and example will be provided shortly). This leaves room for the possibility to expand get_client() later with other features than a connection pool.

There are currently four connection pool options:

  • enabled: Whether the connection pool is enabled. The default is True.
  • max_size: The maximum number of connections that can be in the pool. The default is 25.
  • max_idle_time: How long time in milliseconds a connection can be idle before it is closed. The default is 0 which means “infinite” (in practice 2147483000 milliseconds).
  • queue_timeout: The maximum amount of time in milliseconds that an attempt to get a connection from the pool will block. If no connections have become available before the timeout, a mysqlx.errors.PoolError exception is raised. The default is 0 which means “infinite” (in practice 2147483000 milliseconds).

What happens if you disable the connection pool? In that case the client that is returned simply work as a template for connections and you can keep creating connections until MySQL Server runs out of connections. In that case, the session you end up with is a regular connection, so it when you close it, it will disconnect to MySQL.

Back to the case where the connection pool is enabled. Once you have the client object, you can start using the pool. You retrieve a connection from the pool with the get_session() method. No arguments are used. After this you can use the session just as a regular standalone connection. The only difference is that when you close the session, it is returned to the pool rather than disconnected.

Enough background. Let’s see an example.

Example

The following example creates a connection pool with at most two connections. Then two sessions are fetched from the pool and their connection IDs are printed. A third session will be requested before one of the original sessions is returned to the pool. Finally, a session is reused and its connection ID is printed.

import mysqlx
from datetime import datetime

cnxid_sql = "SELECT CONNECTION_ID() AS ConnectionID"
fmt_id = "Connection {0} ID ..........................: {1}"

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

client_options = {
    "pooling": {
        "enabled": True,
        "max_size": 2,
        "max_idle_time": 60000,
        "queue_timeout": 3000,
    }
}

# Create the connection pool
pool = mysqlx.get_client(connect_args, client_options)

# Fetch two connections (exhausting the pool)
# and get the connection ID for each
connection1 = pool.get_session()
id1_row = connection1.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(1, id1_row["ConnectionID"]))
connection2 = pool.get_session()
id2_row = connection2.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(2, id2_row["ConnectionID"]))

# Attempt to get a third connection
time = datetime.now().strftime('%H:%M:%S')
print("Starting to request connection 3 .........: {0}".format(time))
try:
    connection3 = pool.get_session()
except mysqlx.errors.PoolError as err:
    print("Unable to fetch connection 3 .............: {0}".format(err))
time = datetime.now().strftime('%H:%M:%S')
print("Request for connection 3 completed .......: {0}".format(time))

# Return connection 1 to the pool
connection1.close()

# Try to get connection 3 again
connection3 = pool.get_session()
id3_row = connection3.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(3, id3_row["ConnectionID"]))

# Close all connetions
pool.close()

The first thing to notice is the client options defined in lines 14-21. In this case all four options are set, but you only need to set those where you do not want the default value. The settings allow for at most two connections in the pool, when requesting a session it is allowed to take at most 3 seconds, and idle sessions should be disconnected after 60 seconds.

In line 24 the connection pool (client) is created and subsequent two sessions are fetched from the pool. When a third session is requested, it will trigger a PoolError exception as the pool is exhausted. Lines 38-41 shows how to handle the exception.

Finally the first connection is returned to the pool and it is possible to get the third request to complete.

An example of the output is (the connection IDs and timestamps will differ from execution to execution):

Connection 1 ID ..........................: 239
Connection 2 ID ..........................: 240
Starting to request connection 3 .........: 18:23:14
Unable to fetch connection 3 .............: pool max size has been reached
Request for connection 3 completed .......: 18:23:44
Connection 3 ID ..........................: 241

From the output you can see that the first attempt to fetch connection 3 takes three seconds before it times out and raises the exception – just as specified by the queue_timeout setting.

What may surprise you (at least if you have studied Chapter 5 from MySQL Connector/Python Revealed) from this output is that once connection 1 has been returned to the pool and connection 3 fetches the session again, it has a new connection ID. Does that mean the pool is not working? No, the pool is working alright. However, the X Plugin (the plugin in MySQL Server handling connections using the X Protocol) works differently than the connection handling for the traditional MySQL protocol.

The X Plugin distinguishes between the connection to the application and the thread inside MySQL. So, when the session is returned to the pool and the session is reset (to set the session variables back to the defaults and remove user variables) the thread inside MySQL is removed. As MySQL uses threads, it is cheap to create a new thread as it is needed, so this is not a performance problem. However, the connection to the application is maintained. This means you safe the expensive steps of creating the connection and authenticating, while the threads only actually exists inside MySQL while it is out of the pool.

If you are interested in learning more about MySQL Connector/Python 8 including how to use the X DevAPI, then I am the author of MySQL Connector/Python Revealed (Apress). It is available from Apress, Amazon, and other book stores.

Using Django with MySQL 8

A framework can be a great way to allow you to spend more time on the actual application or web site and less time on standard tasks. It can also greatly reduce the amount of custom code needed. Django is one of the best known web frameworks for Python, and the good news is that it works out of the box with MySQL Server 8 and MySQL Connector/Python 8. This blog will look at how to use Django with MySQL 8.

There actually is very little to get Django to work with MySQL 8. Just install it, configure Django to use MySQL Connector/Python as a backend, and that’s it. From the Django point of view, you just have to configure the database option in settings.py to use MySQL Connector/Python and your database settings, for example:

DATABASES = {
    'default': {
        'NAME': 'mydb',
        'ENGINE': 'mysql.connector.django',
        'USER': 'django',
        'PASSWORD': '$@jkHhj34N!bd',
        'OPTIONS': {
          'autocommit': True,
        },
    }
}

Advice

The instructions in this blog should also work for older versions of MySQL.

Obviously this assumes, you have MySQL installed already. If you do not, the rest of the blog includes a more comprehensive list of steps. The first step is to install MySQL Server.

Django Administration Screen using MySQL as the backend
Django Administration Screen using MySQL as the backend

Installing MySQL Server

There are several ways to install MySQL Server and which one is the best depends on your circumstances and preferences. For the sake of this blog, I will show how MySQL Server can be installed on Oracle Linux/RHEL/CentOS 7 using RPMs and on Microsoft Windows using MySQL Installer. For more options, see the installation chapter in the reference manual. Let’s look at the Linux installation first.

RPM Install on Enterprise Linux

MySQL provides repositories for several Linux distributions including the Oracle Linux/RHEL/CentOS family. This makes it easy to install MySQL. The step to install the repository definition is:

shell$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpmLoaded plugins: langpacks, ulninfo
Repository ol7_developer_EPEL is listed more than once in the configuration
mysql80-community-release-el7-1.noarch.rpm                                     |  25 kB  00:00:00     
Examining /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm: mysql80-community-release-el7-1.noarch
Marking /var/tmp/yum-root-Ts4OzC/mysql80-community-release-el7-1.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================
 Package                       Arch       Version   Repository                                   Size
======================================================================================================
Installing:
 mysql80-community-release     noarch     el7-1     /mysql80-community-release-el7-1.noarch      31 k

Transaction Summary
======================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-1.noarch                                             1/1 
  Verifying  : mysql80-community-release-el7-1.noarch                                             1/1 

Installed:
  mysql80-community-release.noarch 0:el7-1

Complete!

Now, you can install MySQL Server. There are several RPMs to choose from and which you need depends on which other features you need to use. A common set of RPMs can be installed as:

shell$ sudo yum install mysql-community-server mysql-community-libs \
            mysql-community-libs-compat mysql-community-common mysql-community-client
...

Note

If you have another MySQL installation, it will be upgraded to the latest release (at the time of writing 8.0.13).

On the first start, the data directory will be initialized:

shell$ sudo systemctl start mysqld

To keep a fresh installation secure, a random password has been set for the root user. This can be found from the MySQL error log:

shell$ sudo grep password /var/log/mysqld.log 
2018-11-05T08:05:09.985857Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: KWNfuA!1r:PF

Use this password to connect to MySQL and update the password (please use a strong password):

shell$ mysql --user=root --password
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle 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 'Kij0@jDi~Faf4';
Query OK, 0 rows affected (0.13 sec)

MySQL is now ready for use. Before continuing, I will show an example of installing MySQL on Microsoft Windows.

Microsoft Windows

On Microsoft Windows an easy way to install MySQL is to use the MySQL Installer. The installer can be downloaded from the MySQL download site. The MySQL Installer can be used to install most MySQL products. If you start MySQL Installer for the first time, you will be taken directly to the screen to choose the products to install; if you already have installed products, you will need to choose to add new products.

On the Select Products and Features screen, choose to install MySQL Server 8.0 (MySQL Installer will list the latest release from the list of available products):

Installing MySQL Server 8.0.13 from MySQL Installer
Installing MySQL Server 8.0.13 from MySQL Installer

Optionally, you can filter the list of products. Feel free to choose other products you want. MySQL Notifier can be useful for starting and stopping MySQL, if you do not plan to have MySQL running at all times. You can also install MySQL Connector/Python this way, however for this blog a different method will be used.

Follow the installation wizard. For this blog, the default choices will work, though during the configuration you may want to ensure Open Windows Firewall ports for network access is unchecked unless you need remote access.

Before you can connect to MySQL from your Django program, you need a user and a schema (database) to use from your web site.

Preparing MySQL Server

While MySQL is now ready to work with Django, you will likely want to do a few more preparation steps. Here creating the MySQL user and schema (database) used by Django and support for named time zones will be covered.

Creating the User and Schema

An example of creating the user django@localhost and give it all privileges to the mydb schema and to create the mydb schema is:

mysql> CREATE USER django@localhost IDENTIFIED BY '$@jkHhj34N!bd';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL ON mydb.* TO django@localhost;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE DATABASE mydb CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.07 sec)

This will allow the django user to connect from the same host as MySQL Server is installed by authenticating with the password $@jkHhj34N!bd.

In MySQL 8 it is not necessary to specify the database character set to utf8mb4 as it is the default. However, if you use an older version of MySQL Server, you should ensure you are using UTF-8. The utf8mb4 character set means that characters using up to four bytes are supported.

Named Time Zones

If you want to used named time zones (for example Australia/Sydney), you will need to install the data for these in MySQL. On Linux you can use the mysql_tzinfo_to_sql script that comes with the MySQL installation:

shell$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql --user=root --password mysql

On Microsoft Windows, you need to download the time zone information and load these into the database, for example:

mysql> use mysql
Database changed

mysql> SOURCE timezone_posix.sql

See also MySQL Server Time Zone Support in the reference manual.

Now, you can move on to MySQL Connector/Python and Django.

Installing MySQL Connector/Python and Django

Both MySQL Connector/Python and Django can be installed in a platform independent way using the pip command. Since Django 2.1 is only available for Python 3.4 and later, it is recommended to use Python 3.4 or later. This blog assumes Python 3.6. (MySQL Connector/Python 8.0.13 and later also supports Python 3.7.)

If you do not have Python 3.6 installed on Oracle Linux/RHEL/CentOS 7, you can easily install it for example from for EPEL repository. Assuming you have configured the EPEL repository, the following steps install Python 3.6, enable pip, and update pip to the latest version:

shell$ yum install python36
shell$ python3.6 -m ensurepip
shell$ python3.6 -m pip install --upgrade pip

You can now use python3.6 to invoke Python 3.6. In the following, replace python with python3.6 if you have installed Python 3.6 in this way.

To install the latest MySQL Connector/Python release (currently 8.0.13):

PS:> python -m pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading https://files.pythonhosted.org/packages/4a/91/ffdd28ae0f8e01b09df67a4e48cef17a1a5374cd0ce0f2fe27e1d635423e/mysql_connector_python-8.0.13-cp36-cp36m-wi
(3.2MB)
    100% |████████████████████████████████| 3.2MB 3.0MB/s
Requirement already satisfied: protobuf>=3.0.0 in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from mysql-connector-pytho
st1)
Requirement already satisfied: six>=1.9 in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-conne
 (1.11.0)
Requirement already satisfied: setuptools in c:\users\myuser\appdata\local\programs\python\python36\lib\site-packages (from protobuf>=3.0.0->mysql-con
n) (28.8.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.13

Similar for installing Django:

PS:> python -m pip install Django
Collecting Django
  Downloading https://files.pythonhosted.org/packages/d1/e5/2676be45ea49cfd09a663f289376b3888accd57ff06c953297bfdee1fb08/Django-2.1.3-py3-none-any.whl (7.3MB)
    100% |████████████████████████████████| 7.3MB 1.8MB/s
Collecting pytz (from Django)
  Downloading https://files.pythonhosted.org/packages/f8/0e/2365ddc010afb3d79147f1dd544e5ee24bf4ece58ab99b16fbb465ce6dc0/pytz-2018.7-py2.py3-none-any.whl (506kB)
    100% |████████████████████████████████| 512kB 10.5MB/s
Installing collected packages: pytz, Django
Successfully installed Django-2.1.3 pytz-2018.7

That’s it. Now you are ready to use Django with MySQL Connector/Python 8 and MySQL Server 8.

Using Django

I will not go into details of how to use Django. If you are new to Django, you can consider going through the tutorial for Django 2.1 on the Django website. This sets up a web site using a database backend. The important thing with respect to MySQL is the configuration of the DATABASE property in settings.py (the first step in part 2):

DATABASES = {
    'default': {
        'NAME': 'mydb',
        'ENGINE': 'mysql.connector.django',
        'USER': 'django',
        'PASSWORD': '$@jkHhj34N!bd',
        'OPTIONS': {
          'autocommit': True,
        },
    }
}

The key here is the engine. You need to set it to mysql.connector.django to use MySQL Connector/Python. The rest of the options are schema name, credentials, and MySQL Connector/Python specific options.

The Django documentation also has more information about using MySQL as the database. A couple of updates of the statements in the document:

  • As of MySQL 8, InnoDB does correctly restore the auto-increment ID after a restart.
  • The default character set in MySQL 8 is UTF-8 (called utf8mb4 in MySQL).
  • There is also support for a C Extension in MySQL Connector/Python. In MySQL 8 this is the default for the platforms/Python version where the C Extension is installed.

Note

In the second part of the tutorial, I need to swap the python manage.py sqlmigrate polls 0001 command with the next manage.py migrate command. Otherwise the sqlmigrate commands fails with an error.

The rest is all Python and Django. Have fun creating your next web site with Django, MySQL Connector/Python 8, and MySQL 8.

If you are interested in learning more about MySQL Connector/Python 8, then I am the author of MySQL Connector/Python Revealed (Apress) – also available at Amazon and other bookshops.

Slides and Workbooks From Oracle OpenWorld & CodeOne

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations.

All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in.

Click on the download link in the Oracle OpenWorld session catalog to download the presentations.
Click on the download icon in the Oracle OpenWorld session catalog to download the presentations.

For the hands-on labs the downloadable file includes the workbook as well as the scripts related to the exercises. The workbook contains the instructions for setting up the system used for the exercises as well as the exercises themselves and some additionaly background information. For the talks, the download consists of a PDF version of the slides.

The three hands-on labs and three talks I had were:

  • DEV5957: Develop Python Applications with MySQL Connector/Python
    This covered MySQL Connector/Python in general from installation to best practices. The talk focused on using the connector with SQL tables using both SQL statements and NoSQL CRUD methods. If you are interested in how I installed MySQL Connector/Python on iPad (the screen shot on in the right hand side of slide showing the pip command), see my previous blog about installing MySQL Connector/Python in Pythonista 3.
  • DEV5959: Python and the MySQL Document Store
    This covered how to use MySQL Connector/Python (and a little of MySQL Shell in Python mode) with the MySQL JSON Document Store using the NoSQL API (the X DevAPI).
  • HOL1703: A Practical Introduction to the MySQL Document Store
    This hands-on lab used the MySQL Shell in Python mode to use the MySQL Document Store including an introduction to the CRUD methods. The lab also includes a comparison of implementing the same X DevAPI program using Python, Node.js, and Java.
  • HOL1706: Developing Modern Applications with the MySQL Document Store and NodeJS
    This lab is similar to HOL1703 except it mainly uses Node.js programs to use the MySQL Document Store.
  • HOL2986: Using MySQL Common Table Expressions and Window Functions
    An introduction to the new MySQL 8.0 query features: common table expressions (CTEs) and the window functions that can be used for analytic queries.
  • THT6703: NoSQL + SQL = MySQL
    A brief introduction to MySQL, MySQL 8, and how you can use it both as a NoSQL document store and a traditional SQL database.

Enjoy.

NoSQL/X DevAPI Tutorial with MySQL Connector/Python 8.0

The MySQL Document Store became general available (GA) with MySQL 8. One of the nice features of the MySQL Document Store is the X DevAPI that allows you to query the data from a multitude of programming languages using the same API (but while retaining the conventions of the language). The programming languages with support for the X DevAPI includes JavaScript (Node.js), PHP, Java, DotNet, and C++.

I will be using MySQL Connector/Python 8.0.12 for the example in this blog. The example is executed on Microsoft Windows with Python 3.6 installed, but it has also been tested on Oracle Linux 7 with Python 2.7. I do assume that MySQL Connector/Python has been installed. If that is not the case, you can read how to do it in the Installing Connector/Python from a Binary Distribution section in the manual or Chapter 1 of MySQL Connector/Python Revealed from Apress.

The output of the example program
The output of the example program

The example will go through the following steps:

  • Getting Ready:
    1. Load the mysqlx module.
    2. Create a database connection.
  • Setup:
    1. Create a schema.
    2. Create a collection.
  • CRUD – Create:
    1. Insert some documents into the collection.
  • CRUD – Read:
    1. Query the documents.
  • Cleanup:
    1. Drop the schema.

The program uses the pyuser@localhost user. The connection parameters can be changed as described in the “Getting Ready” section. A user that fulfills the requirement to the example program can be created using the following SQL statements:

mysql> CREATE USER pyuser@localhost IDENTIFIED BY 'Py@pp4Demo';
mysql> GRANT CREATE, INSERT, SELECT, DROP
             ON my_collections.* TO pyuser@localhost;

Warning

This program is not an example of using best practices. Do not store the password and preferably also the other connection options in the source code. There is also very limited handling of errors and warnings in order to keep the example simple. You should not skip those steps in a production program.

Getting Ready

The first thing is to get ready by importing MySQL Connector/Python’s mysqlx module and connect to MySQL. This is simple to do as shown in the below code snippet (the line numbers refer to the full example):

import mysqlx

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

# OK is not used but would correspond to a value of 0.
# 1 is Info and 2 is warning. Errors cause an exception.
warning_levels = ("OK", "Info", "Warning")

# Create the database connection
db = mysqlx.get_session(**connect_args)

The mysqlx module in imported in line 38. This is where the MySQL Connector/Python implementation of the X DevAPI resides. The module includes support for CRUD statements both for documents and SQL tables, schema and collection manipulations, as well as executing SQL statements. In this example, only the CRUD implementation for documents and the schema and collection manipulation will be used.

The warning_levels variable is uses to convert numeric warning levels returns by the X DevAPI to names. There is an example of how to handle warnings after the first document has been added.

Finally, the connection is created in line 52 using the get_session() method in the mysqlx module.  With a connection object in place, let’s move on to set up the schema and collection.

Setup

The X DevAPI has support for creating and dropping schemas and collections (but currently not SQL tables). This is used in the example to set up the my_collections schema with a single collection called my_docs:

# Create the my_collections schema
schema = db.create_schema("my_collections")

# Create the my_docs collection in the my_collections schema
my_docs = schema.create_collection("my_docs")

The create_schema() method on the database (session) object is used to create the schema. It will succeed even if the schema already exists. In that case the existing schema will be returned.

The collection is similarly created with the create_collection() method from the schema object. This will by default fail if the collection already exists. It can be overwritten with the reuse argument (the second argument).

That is it. A collection is always defined internally in the same way when it is created. You can add indexes – I hope to get back to that in a future blog – but there is no need to think of columns and data types at this point. That is the advantage of using a schemaless database (but also one of the dangers – now the whole responsibility of staying consistent is up to you as a developer).

Let’s continue to add some documents.

CRUD – Create

For this example, three documents will be added to the my_docs collection. The documents contain information about three persons including their name, birthday, and hobbies. The documents can be defined as Python dictionaries with JSON arrays represented as Python lists:

# Define three documents to insert
adam = {
    "First_name": "Adam",
    "Surname": "Smith",
    "Birthday": "1970-10-31",
    "Hobbies": [
        "Programming",
        "Databases",
        "Hiking"
    ]
}

kate = {
    "First_name": "Kate",
    "Surname": "Lee",
    "Birthday": "1982-08-09",
    "Hobbies": [
        "Programming",
        "Photography",
        "Running"
    ]
}

jane = {
    "First_name": "Jane",
    "Surname": "Walker",
    "Birthday": "1977-02-23",
    "Hobbies": [
        "Databases",
        "Hiking",
        "Photography"
    ]
}

This is the beauty of working with JSON documents in Python. They just work.

The birthdays are written in the ISO 8601 format (the same as MySQL’s date data type uses – but not datetime!). As the MySQL Document Store is schemaless, you are free to chose whatever format you feel like, however, it is strongly recommended to use a standard format. This YYYY-mm-dd format has the advantage that it will sort correctly, so alone for that reason, it is a strong candidate.

The documents will be inserted in two rounds. First Adam will be added, then Kate and Jane.

Adding a Single Document

There are a few ways to add documents (all working in the same basic way). This example will show two of them. First let’s look at how Adam is added:

# Insert the document for Adam
# This is an example of chaining the actions
db.start_transaction()
result = my_docs.add(adam).execute()

if (result.get_warnings_count() > 0):
    print("{0} warnings occurred!".format(result.get_warnings_count()))
    print("The warnings are:\n")
    for warning in result.get_warnings():
        level = warning_levels[warning["level"]]
        print("   * Level: {0} - Errno: {1} - Message: {2}".format(
            level, warning["code"], warning["msg"]))
    print("")
    print("Rolling the transaction back and existing.")
    db.rollback()
    exit()

# No errors or warnings, so the transaction can be committed
db.commit()

print("Adam: Number of documents added: {0}".format(
    result.get_affected_items_count()))
print("Document ID for Adam: {0}".format(result.get_generated_ids()))

The document is added inside a transaction. The X DevAPI connection inherits the value of autocommit from the server-side (defaults to ON), so to be sure the create action can be tested for warnings before committing, an explicit transaction is used. (Errors cause an exception, so since that is not handled here, it would cause an automatic rollback.)

The document is added using a chained statement. When you build an X DevAPI statement, you can choose between calling the method one by one or chaining them together as it is done in this case. Or you can choose a combination with some parts chained and some not. When the documents for Kate and Jane are added, it will be done without chaining.

The statement is submitted to the database using the execute() method. If you are used to executing Python statements in MySQL Shell, you may not be familiar with execute() as MySQL Shell allows you to skip it for interactive statements where the result is not assigned to a variable. The result is stored in the result variable which will be used to examine whether any warnings were triggered by the statement.

Tip

In MySQL Connector/Python, you must always call execute() to execute an X DevAPI statement.

It is best practice to verify whether queries cause any warnings. A warning will still allow the statement to execute, but it is in general a sign that not everything is as it should be. So, take warnings seriously. The earlier you include tests for warnings, the easier it is to handle them.

In line 99, the get_warnings_count() method of the result object is used to check if any warnings occurred. If so, the number of warnings is printed and each warning if retrieved using the get_warnings() method. A warning is a dictionary with three elements:

  • level: 1 for note and 2 for warning. This is what the warning_levels variable was created for at the start of the example.
  • code: The MySQL error number. The mysqlx.errorcode module contains string symbols for all the error numbers. This can be useful in order to check whether it is an expected error number that can be ignored.
  • msg: A string message describing the problem.

In this case, if any warnings occur, the transaction is rolled back, and the script exists.

Tip

Include handling of warnings from the beginning of coding your program. Handling warnings from the get go makes it much easier to handle them. They are usually a sign of something not working as expected and it is important that you know exactly why the warnings occur. All warnings include an error code that you can check against to verify whether it is an expected warning. If some warning is expected and you are confident, it is acceptable to ignore it.

If no error occurs, some information from the result is printed. An example output looks like (the ID will be different):

Adam: Number of documents added: 1
Document ID for Adam: ['00005b9634e3000000000000001c']

As expected one document has been added. The number of documents is printed using the get_affected_items_count() method. More interesting is the document ID. As the document did not include an element named _id, MySQL added one automatically and assigned a value to it. I will not go into how the ID is generated here, but just note that it includes three parts that together ensure global uniqueness even if you use multiple clients against multiple MySQL Server instances. At the same time, the IDs are still being generated in a way that is optimal for the InnoDB storage engine that is used for the underlying storage of the documents. The IDs are returned as a list; in this case there is only one element in the list, but if more than one document is inserted without an _id value, then there will be one generated ID per document.

The final step is to commit the transaction, so the document is persisted in the collection.

Adding Multiple Documents

When you want to add multiple documents using a single CRUD statement, you can essentially do it in two ways. You can add all of the documents in one go in the initial add() call similar to what was done for a single document with Adam. This can for example be done by having the documents in a tuple or list.

The other way, which will be used here, is to repeatably call add() to add the documents. Let’s see how that works:

db.start_transaction()
stmt_add = my_docs.add()
stmt_add.add(kate)
stmt_add.add(jane)
result = stmt_add.execute()
db.commit()
print("Kate and Jane: Number of documents added: {0}".format(
    result.get_affected_items_count()))
print("Document IDs: {0}".format(result.get_generated_ids()))

To keep the example from getting too long, the check for warnings have been removed, and the example will just focus on adding the documents.

After the transaction has been started, the statement object is created by calling add() on the collection object. In this case, no arguments are given, so at that point in time, the statement will not insert any documents.

Then the two documents are added one by one by calling add() on the statement object, first with the kate document, then with the jane document. An advantage of this approach is that if you for example generate the documents inside a loop, then you can add them as they are ready.

When both documents have been added, the execute() method is called to submit the documents to the database and the transaction is committed. Again, some information from the result is printed (the IDs will be different):

Kate and Jane: Number of documents added: 2
Document IDs: ['00005b9634e3000000000000001d', '00005b9634e3000000000000001e']

So, two documents are inserted (again as expected) and two IDs are generated.

The way that the add statement was used to insert the two documents is an example of the opposite of chaining. Here, one action at a time is performed and the result is stored in the stmt_add variable.

Now that there are some documents to work with, it is time to query them.

CRUD – Read

When you want to query documents in a collation, you use the find() method of the collection object. The resulting find statement support all of the usual refinements such as filtering, sorting, grouping, etc. In this example, three queries will be executed. The first will find the total number of documents in the collection. The second, will find the persons born on 9 August 1982. The third will find the persons who has hiking as a hobby.

Total Number of Documents

The X DevAPI makes it easy to determine the number of documents in the document – the count() method of the collection will return the value as an integer. In practice the count() method goes through the same steps as you will see in the two subsequent queries, but they are hidden inside the implementation. The code snippet is:

print("The total number of documents in the collection: {0}".format(
    my_docs.count()))

It cannot get much easier than that. The output is:

The total number of documents in the collection: 3

Let’s move on and see some of the steps that were hidden in the first query.

Finding Documents Based on Simple Comparison

The persons (in this case just one person) born on 9 August 1982 can be found by creating a find statement and adding a simple filter. The example code is:

# Find the person born on 9 August 1982
print("")
stmt_find = my_docs.find("Birthday = :birthday")
stmt_find.fields("First_name", "Surname")
stmt_find.bind("birthday","1982-08-09")
result = stmt_find.execute()
person = result.fetch_one()
print("Person born on 9 August 1982: {First_name} {Surname}".format(**person))

The filter clause is added in the call to find(). The syntax :birthday means that a parameter is used and the value will be added later. That has two advantages: it makes it easier to reuse the statement, and importantly it makes the statement safer as MySQL will ensure the value is escaped correctly – this is similar to the mysql_real_escape_string() function in the MySQL C API. The value of the parameter is given using the bind() method that has two arguments: the parameter name and value. If you use multiple parameters, call bind() once for each of them.

Otherwise the statement is simple to use. The filtering condition may seem too simple given it is a JSON document it applies to. However, Birthday in the condition is interpreted as $.Birthday (the $. part is optional) – that is the object named Birthday and is a child of the root of the document, which is just what is needed in this case. The next example includes a more complicated filter condition.

The fields to include are specified in a similar manner to the filter condition. You specify the path to the element you want to include. You can optionally rename the element using the AS keyword, for example: Surname AS Last_name. As for the condition, the $. part is optional.

The resulting row is retrieved using the fetch_one() method on the result object. This is fine here as we know there is only one resulting row. However, in a more general case you should use fetch_one() is a loop and continue until it returns None at which point all rows have been fetched.

The output is:

Person born on 9 August 1982: Kate Lee

Querying with Condition on Element in Array

A more complicated find statement is to look into the Hobbies array and see if any of the elements is Hiking. This query also matches two of the persons in the collection, so a loop is required to handle them. The code is:

stmt_find = my_docs.find("JSON_CONTAINS($.Hobbies, :hobby)")
stmt_find.fields("First_name", "Surname")
stmt_find.sort("Surname", "First_name")
stmt_find.bind("hobby", '"Hiking"')
result = stmt_find.execute()
people = result.fetch_all()
print("Number of people in the result: {0}".format(result.count))
print("The people with a hobby of hiking:")
for person in people:
    print("   {First_name} {Surname}".format(**person))

There are two main differences between this example and the previous: the filter condition and how the result documents are handled.

The filter uses the JSON_CONTAINS() function to check whether the $.Hobbies elements contains the value specified by the :hobby parameter. In the call to bind(), the parameter value is set to "Hiking". Note that Hiking must be quoted with double quotes as it is a JSON string. In this case, $. is included in the document path. However, it is still optional.

After executing the query, the resulting documents are fetched using the fetch_all() method. This will return all of the documents as a list. This makes it simpler to loop over the resulting rows, however be aware that for large result sets, it can cause a high memory usage on the application server.

Warning

Be careful with the fetch_all() method if the query can return a large result set. It will require the remaining part of the result to be stored in-memory on the application-side.

One advantage of the fetch_all() method is that it will allow you to get the total number of documents in the result using the count property of the result. The count property will show 0 until fetch_all() have completed. Once the documents have been fetched, it is possible to print the names of the persons who like to hike. The output is:

Number of people in the result: 2
The people with a hobby of hiking:
   Adam Smith
   Jane Walker

Other than a bit of cleanup, there is nothing more to do.

Cleanup

The final part of the example is to clean up. The my_collections schema is dropped so the database is left in the same state as at the start, and the connection is closed:

# Remove the schema again, so the the database is left in the same
# state as at the start. Comment out if you want to play with the
# data.
db.drop_schema("my_collections")

# Close the database connection.
db.close()

Dropping a schema is done in the same way as creating it, just that the drop_schema() method is used instead. The drop_schema() method will also work if the schema does not exist. In that case it is a null-operation.

It is important always to close the database connection. Have you ever seen the MySQL Server error log full of notes about aborted connections? If you do not explicitly close the database connection when you are done with it, one of those notes will be generated (provided the server is configured with error_log_verbosity = 3).

Additionally, not closing the connection will keep the connection alive until the program terminates. That is not a problem here, but in other cases, it may take a long time before the application shuts down. In the meantime, the connection count is higher than it needs to be, and if you happen to have an ongoing transaction (can very easily happen with autocommit = OFF), the connection may cause lock issues or slowness for the other connections.

Advice

Always close the database connection when you are done with it.

Want to Learn More?

I hope this has triggered your curiosity and you are ready to dive deeper into the world of MySQL Connector/Python, the X DevAPI, and the MySQL Document Store. If so, there are two recently released books that you may find useful.

Disclaimer

I am the author of one of these books.

One book is MySQL Connector/Python Revealed (Apress) written by me. It goes through MySQL Connector/Python both for the legacy PEP249 API (mainly the mysql.connector module) and the new X DevAPI (the mysqlx module). There are three chapters dedicated to the X DevAPI.

The other book is Introducing the MySQL 8 Document Store (Apress) written by Dr. Charles Bell (MySQL developer). This book goes through how JSON works in MySQL including information about the X DevAPI and its siblings the X Protocol and the X Plugin.

Both books are more than 500 pages and comes with code examples that will help bring you up to speed with MySQL Connector/Python and the MySQL Document Store.

MySQL Connector/Python on iOS Using Pythonista 3

One of the nice things about MySQL Connector/Python is that it is available in a pure Python implementation. This makes it very portable. Today I have been exploring the possibility to take advantage of that to make MySQL Connector/Python available on my iPad.

There are few Python interpreters available for iPad. The one I will be discussing today is Pythonista 3 which has support for both Python 2.7 and 3.6. One of the things that caught my interest is that it comes with libraries to work with iOS such as accessing the contact and photos as well as UI tools. This is a commercial program (AUD 15), but this far looks to be worth the money. There are other options and I hope to write about those another day.

MySQL Connector/Python is available from PyPi. This makes it easy to install the modules in a uniform way across platforms. Unfortunately, Pythonista 3 does not support the pip command out of the box. Instead there is a community contribution called StaSh that can be used to get a shell-like environment that can be used to execute pip. So, our first step is to install StaSh.

Coding with MySQL Connector/Python on an iPad
Coding with MySQL Connector/Python on an iPad

Install StaSh

StaSh is a project maintained by ywangd and can be found on GitHub. It is self-installing by executing a small Python program that can be found in the README.md file on the project repository page. You copy the source code into a new file in Pythonista 3 and execute it (using the “Play” button):

StaSh is installed by executing a downloaded script.
StaSh is installed by executing a downloaded script.

Then you need to restart Pythonista 3. At this point StaSh is installed.

Installing PyPi Package Using StaSh pip

In order to be able to use the pip command through StaSh, you need to launch the launch_stash.py program which was installed in the previous step. The program can be found in the This iPad folder:

Open the launch_stash.py Script in This iPad
Open the launch_stash.py Script in This iPad

Open the program and use the “Play” button again to execute it. This creates the shell. You can do other things than use the pip command, but for the purpose of installing MySQL Connector/Python that is all that is required. The command is:

[~/Documents]$ pip install mysql-connector-python

The console output is:

Using the pip command in StaSh to install MySQL Connector/Python.
Using the pip command in StaSh to install MySQL Connector/Python.

That’s it. Now you can use the MySQL Connector/Python modules on your iPad just as in any other environment.

Example

To verify it is working, let’s create a simple example. For this to work, you need MySQL installed allowing external access which likely requires enabling access to port 3306 in your firewall.

A simple example that queries the city table in the world database for the city with ID = 130 is:

connect_args = {
    "host": "192.0.2.1",
    "port": 3306,
    "user": "testuser",
    "password": "password",
}

db = mysql.connector.connect(**connect_args)
cursor = db.cursor(named_tuple=True)

sql = "SELECT * FROM world.city WHERE ID = 130"
cursor.execute(sql)
print(cursor.fetchone())

cursor.close()
db.close()

Edit the connect_args dictionary with the connection arguments required for your MySQL instance.

Warning

The connection arguments are included inside the source code here to keep the example simple. Do not do this in real programs. It is unsafe to store the password in the source code and it makes the program hard to maintain.

When you run it, the details of Sydney, Australia is printed to the console:

Example of querying the world.city table using MySQL Connector/Python in Pythonista 3.
Example of querying the world.city table using MySQL Connector/Python in Pythonista 3.

This all looks great, but what about the X DevAPI? Unfortunately, there are some problems there.

X DevAPI

The X DevAPI is new in MySQL 8.0. It is included in MySQL Connector/Python in the mysqlx module. However, it does not work out of the box in Pythonista 3. When you try to execute the import mysqlx command, it fails:

>>> import mysqlx
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/__init__.py", line 35, in <module>
    from .connection import Session
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/connection.py", line 50, in <module>
    from .crud import Schema
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/crud.py", line 34, in <module>
    from .statement import (FindStatement, AddStatement, RemoveStatement,
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/statement.py", line 36, in <module>
    from .expr import ExprParser
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/expr.py", line 34, in <module>
    from .protobuf import Message, mysqlxpb_enum
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/protobuf/__init__.py", line 75, in <module>
    from . import mysqlx_connection_pb2
  File "/private/var/mobile/Containers/Shared/AppGroup/BA51D6FF-34C8-460C-88B7-0078A16FBBD2/Pythonista3/Documents/site-packages-3/mysqlx/protobuf/mysqlx_connection_pb2.py", line 8, in <module>
    from google.protobuf import reflection as _reflection
  File "/var/containers/Bundle/Application/743A0BDC-D77A-4479-A1D9-DFF16785FBC7/Pythonista3.app/Frameworks/Py3Kit.framework/pylib/site-packages/google/protobuf/reflection.py", line 69, in <module>
    from google.protobuf.internal import python_message
  File "/var/containers/Bundle/Application/743A0BDC-D77A-4479-A1D9-DFF16785FBC7/Pythonista3.app/Frameworks/Py3Kit.framework/pylib/site-packages/google/protobuf/internal/python_message.py", line 849
    except struct.error, e:
                       ^
SyntaxError: invalid syntax

So, the Protobuf module that comes with Pythonista 3 uses the old comma syntax for exception handling. This is not allowed with Python 3 (PEP 3110).

Update: While I had tried to install a newer version of Protobuf using the StaSh pip command, what I had not realised originally is that for the change to take effect, you need to restart Pythonista 3. Once that is done, the mysqlx module works as well. To install Protobuf in StaSh, launch StaSh in the same way as when MySQL Connector/Python was installed above and execute the pip command:

[~/Documents]$ pip install protobuf