python 连接 mysql 数据库，可以把生产的数据存入到 mysql 数据库，从 mysql 数据库中读取数据。本篇基于 linux 简单介绍。

安装 mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
sudo apt update

sudo apt install mysql-server

# 启动 mysql
sudo service mysql

# 访问数据库
mysql --defaults-file=/etc/mysql/debian.cnf
# 或者查看用户名密码，然后使用用户名和密码登录
cat /etc/mysql/debian.cnf
mysql -u debian-sys-maint -p

创建数据库和表、支持中文

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql --defaults-file=/etc/mysql/debian.cnf

# 查看可用数据库
mysql> show databases;

# 创建新数据库
mysql> create database dro;

# 创建新表
mysql> use dro;
Database changed
mysql> create table abr (
    -> id INT PRIMARY KEY,
    -> filename VARCHAR(300) NOT NULL,
    -> message VARCHAR(300) NOT NULL
    -> );

mysql> exit;

中文支持

1
2
3
4
5
6
7
8
9
10
locale  # check for UTF-8

sudo apt update && sudo apt install locales
sudo locale-gen en_US en_US.UTF-8
sudo update-locale LC_ALL=en_US.UTF-8 LANG=en_US.UTF-8
export LANG=en_US.UTF-8

echo 'export LANG=en_US.UTF-8' > ~/.bashrc

locale  # verify settings

mysql 数据库表中支持插入中文

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> alter database dro character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use dro;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_dro |
+---------------+
| abr           |
+---------------+
1 row in set (0.00 sec)

mysql> alter table abr convert to character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

python 包 mysql-connector-python 安装

1
pip install mysql-connector-python

插入数据到数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="dro"
)

mycursor = mydb.cursor()

sql = "INSERT INTO abr (id, filename, message) VALUES (%s, %s, %s)"
val = (1, "hello-world.mp4", "视频中时刻00:01处出现动物聚集异常")
mycursor.execute(sql, val)

mydb.commit()

从数据库中读取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="dro"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM abr ORDER BY id DESC")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

参考文献

  1. MySQL 教程
  2. Python MySQL
  3. MySQL不能插入中文字段的解决办法