Python 中使用 MySQL

安装MySQL

下载 MySQL 社区版：https://dev.mysql.com/downloa...

安装过程中需要输入 root 用户的密码，本地可以使用 root/123456 等。

在Windows上，安装时需要选择utf8mb4编码，以便正确地处理中文。

在Mac或Linux上，需要编辑MySQL的配置文件，把数据库默认的编码全部改为utf8mb4。MySQL的配置文件默认存放在/etc/my.cnf或者/etc/mysql/my.cnf 目录下：

[client]
default-character-set = utf8mb4

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

重启MySQL后，可以通过MySQL的客户端命令行检查编码：

$ mysql -u root -p
Enter password: 
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     | utf8mb4                                                |
+--------------------------+--------------------------------------------------------+
7 rows in set (0.00 sec)

安装MySQL驱动并测试

MySQL driver written in Python which does not depend on MySQL C client libraries and implements the DB API v2.0 specification (PEP-249).

$ pip install mysql-connector-python --allow-external mysql-connector-python

操作数据库：

>>> import mysql.connector

>>> conn = mysql.connector.connect(user='root', password='root', database='test')
>>> cursor = conn.cursor()

>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1

>>> conn.commit()
>>> cursor.close()
>>> conn.close()

执行查询：

>>> import mysql.connector

>>> conn = mysql.connector.connect(user='root', password='root', database='test')
>>> cursor = conn.cursor()

>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()

>>> values
[('1', 'Michael')]

>>> cursor.close()
>>> conn.close()