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.
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
...
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):
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
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='🐬') 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.
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': '🐬'}
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='🐬')>
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='🐬')
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.