AI工程系统运维

大数据单机学习环境-MySQL

字数统计: 1.2k字   |   阅读时长: 5分
, ,

摘要: MySQL 环境搭建

【对数据分析、人工智能、金融科技、风控服务感兴趣的同学，欢迎关注我哈，阅读更多原创文章】
我的网站：潮汐朝夕的生活实验室
我的公众号：潮汐朝夕
我的知乎：潮汐朝夕
我的github：FennelDumplings
我的leetcode：FennelDumplings

MySQL 可以作为 Hive 的元数据存储，本文介绍 CentOS 中使用 yum 安装 MySQL 的过程，如果是 Mac 或者 Ubuntu 也类似。

如果机器中安装过 MySQL，需要先卸载干净：

1
yum remove mysql mysql-server mysql-libs mysql-server

之后执行 rpm -qa | grep mysql，应该为空，如果不为空，则用 rpm -ev 进行删除即可。

卸载干净之后就可以安装了。

MySQL 下载和安装

从 https://dev.mysql.com/downloads/repo/yum/ 获取下载链接，5.7 版本的链接如下：

1
https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
1
2
3
4
5
6
# 下载 MySQL 的 yum 源
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# 安装 MySQL 的 yum 源
rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
# 安装 MySQL
yum -y install mysql-community-server

执行 whereis mysql 查看 MySQL 安装位置，结果如下：

1
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

修改 MySQL 密码

重置密码的第一步跳过 MySQL 的密码认证过程，打开文件 /etc/my.cnf，在 # [mysqld] 的下一行添加：

1
skip-grant-tables

之后重启 mysql

1
service mysqld restart

无密码进入 mysql：

1
mysql -uroot -p

之后执行以下命令：

1
2
3
4
5
6
use mysql
-- 用 SQL 修改密码
update user set authentication_string=password("123456") where user="root"; 
-- 立即生效
flush privileges; 
quit

此后把之前在 /etc/my.cnf 中添加的一行删掉，再重启，此后执行以下命令进入 mysql 时输入密码即可。

1
mysql -uroot -p

建库测试

1
2
create database hive;
show databases;

结果如下：

1
2
3
4
5
6
7
8
9
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Python

由于 MySQL 服务器以独立的进程运行，并通过网络对外服务，所以需要支持 Python 的 MySQL 驱动来连接到 MySQL 服务器。在 Python 中支持 MySQL 的数据库模块有很多，我们选择使用 PyMySQL。

1
pip install PyMySQL

PyMySQL 也遵循 Python Database API 2.0 规范，所以操作 MySQL 数据库的方式与 SQLite 相似。

连接

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

# 打开数据库连接，参数1：主机名或IP；参数2：用户名；参数3：密码；参数4：数据库名
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mysql')

# 使用 cursor() 创建一个 cursor 对象
cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()
print("Database version: {}".format(data))

# 关闭数据库
db.close()

结果如下：

1
Database version: ('5.7.41',)

建数据表

创建数据表需要使用 execute() 方法，这里使用该方法创建一个 books 图书表，books 表包含 id(主键)、name（图书名称）、category（图书分类）、price（图书价格）和 public_time（出版时间）5 个字段。

创建 books 表的 SQL 语句如下：

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS "books";

CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY (id)   
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

对应的 Python 代码如下：

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
import pymysql

# 打开数据库连接，参数1：主机名或IP；参数2：用户名；参数3：密码；参数4：数据库名
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mysql')

# 使用 cursor() 创建一个 cursor 对象
cursor = db.cursor()

# 使用 execute() 方法执行SQL语句，如果表存在删除
cursor.execute("DROP TABLE IF EXISTS books")

# 使用预处理语句创建表
sql = """CREATE TABLE books(
            id int(8) NOT NULL AUTO_INCREMENT,
            name varchar(50) NOT NULL,
            category varchar(50) NOT NULL,
            price decimal(10,2) DEFAULT NULL,
            publish_time date DEFAULT NULL,
            PRIMARY KEY (id)   
         ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
      """

# 执行SQL语句
cursor.execute(sql)

# 关闭连接数据库
db.close()

增删改查

使用 executemany() 方法向数据表中批量添加多条记录，executemany() 方法格式如下：

1
2
3
4
executemany(operation, seq_of_params)

operation：操作的SQL语句。
seq_of_params：参数序列。

代码如下：

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
import pymysql

# 连接数据库
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mysql')

# 使用 cursor() 创建一个 cursor 对象
cursor = db.cursor()

# 数据列表
data = [
    ("零基础Python", "Python", "79.80", "2022-5-30"),
    ("零基础Java", "Java", "69.80", "2022-4-19"),
    ("Python入门到精通", "Python", "89.80", "2022-5-19"),
    ("PHP项目开发入门", "PHP", "99.80", "2022-2-19"),
]

try:
    # 执行SQL,插入多条数据
    cursor.executemany("insert into books(name,category,price,publish_time) values (%s,%s,%s,%s)", data)

    # 提交数据
    db.commit()
    
except:
    # 发生错误时回滚
    db.rollback()

# 关闭数据库连接
db.close()

其中：

  • 使用 connect() 方法连接数据库时，额外设置字符集 “charset=utf8”，可以防止插入中文时错。
  • 在使用 insert 语句插入时，使用“%s”作为占位符，可以防止 SQL 注入。
Share