Tweet

MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.

In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.

The built-in MySQL Connector/Python connection attributesConnection Attributes in MySQL Server

The …

Tweet

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 …

Tweet

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

…

Tweet

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 …

Tweet

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 …

Tweet

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 …

Tweet

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.

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

Tweet

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 = { …

Tweet

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.

…

Tweet

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 …