关于python: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()