Using MySQL ​

MySQL is the most widely used database server in the web world. While SQLite is lightweight and embeddable, it cannot handle high concurrent access, making it suitable for desktop and mobile applications. In contrast, MySQL is designed for server-side use, supporting high concurrent access but also consuming significantly more memory than SQLite.

Additionally, MySQL has multiple database engines, with the most commonly used one being InnoDB, which supports database transactions.

Installing MySQL ​

You can download the latest Community Server 8.x version directly from the MySQL official website. MySQL is cross-platform, so choose the appropriate installer for your platform and proceed with the installation.

During installation, MySQL will prompt for the root user's password. Make sure to remember it. If you find it hard to remember, set the password to "password."

On Windows, choose UTF-8 encoding during installation to handle Chinese characters correctly.

On Mac or Linux, you need to edit MySQL's configuration file and change the default database encoding to UTF-8. The MySQL configuration file is usually located at /etc/my.cnf or /etc/mysql/my.cnf:

[client]
default-character-set = utf8mb4

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8mb4
collation-server = utf8_general_ci

After restarting MySQL, you can check the encoding through the MySQL command-line client:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor...
...

mysql> show variables like '%char%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| character_set_client     | utf8mb4                              |
| character_set_connection | utf8mb4                              |
| character_set_database   | utf8mb4                              |
| character_set_filesystem | binary                               |
| character_set_results    | utf8mb4                              |
| character_set_server     | utf8mb4                              |
| character_set_system     | utf8mb3                              |
| character_sets_dir       | /usr/local/mysql-8.x/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)

Seeing "utf8mb4" indicates that the encoding is set correctly.

Starting MySQL with Docker ​

If you prefer not to install MySQL, you can quickly start MySQL using Docker.

First, install Docker Desktop, then run the following command in the terminal:

$ docker run -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 --name mysql-8.4 -v ./mysql-data:/var/lib/mysql mysql:8.4 --mysql-native-password=ON --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

The command parameters are explained below:

• -e MYSQL_ROOT_PASSWORD=password: Sets the root user's password to "password".
• -p 3306:3306: Maps port 3306 on the host.
• --name mysql-8.4: Names the container "mysql-8.4" (can be set to any name).
• -v ./mysql-data:/var/lib/mysql: Maps the current directory ./mysql-data to the container's /var/lib/mysql directory, where the MySQL database files are stored, to avoid data loss when the container stops.
• mysql:8.4: Uses the image "mysql:8.4".
• --mysql-native-password=ON: Enables plain text password authentication.
• --character-set-server=utf8mb4: Sets utf8mb4 as the character set.
• --collation-server=utf8mb4_unicode_ci: Sets utf8mb4 as the collation.

After running the command, the following output indicates a successful startup:

2024-07-11 02:44:05+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.1-1.el9 started.
...
2024-07-11T02:44:16.874162Z 0 [System] [MY-015015] [Server] MySQL Server - start.
...
2024-07-11T02:44:17.120017Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-11T02:44:17.561242Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
...
2024-07-11T02:44:17.868691Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

The "ready for connections" message indicates that the server is up and running.

Installing the MySQL Driver ​

Since the MySQL server runs as a separate process and provides services over the network, a Python MySQL driver is required to connect to the MySQL server. The official driver is mysql-connector-python:

$ pip install mysql-connector-python

Here’s how to connect to the MySQL server's test database:

# Import the MySQL driver:
>>> import mysql.connector
# Set the password to your root password:
>>> conn = mysql.connector.connect(user='root', password='password', database='test')
>>> cursor = conn.cursor()
# Create the user table:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# Insert a record (note that MySQL uses %s as a placeholder):
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1
# Commit the transaction:
>>> conn.commit()
>>> cursor.close()
# Run a query:
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
# Close the Cursor and Connection:
>>> cursor.close()
True
>>> conn.close()

Since Python's DB-API definitions are universal, the code for operating a MySQL database is similar to that for SQLite.

Summary ​

After performing an INSERT or other operations, remember to call commit() to commit the transaction.
MySQL's SQL placeholder is %s.