使用Python完成RDS数据开发及分析

​

 目录

创建数据库语句

配置文件

创建表格create_tables.py 

创建触发器creat_triggers.py

插入语句creat_insert.py

 新增数据insert_data.py

查询数据select_data.py 

更新数据update_data.py

删除数据delete.py

删除表格delete_table.py

nginx配置

数据分析data_analysis.py

考证地址为下面的链接：

 云数据库Clouder认证：SQL基础开发与应用_阿里云认证_阿里云培训中心-阿里云

创建数据库语句

CREATE DATABASE IF NOT EXISTS `commodity` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

配置文件

下载地址：

wget 'https://labfileapp.oss-cn-hangzhou.aliyuncs.com/clouder/account.yml'

配置文件名称：account.yml

配置文件内容：

database:  

        host: rm-bp1gnkhyazncgwof7oo.mysql.rds.aliyuncs.com  

        username: aliyun  

        password: Aliyun123

创建表格create_tables.py 

#!/apps/venv/bin/pythonimport pymysqlimport yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

book_types = """

CREATE TABLE IF NOT EXISTS `book_types` (`type_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍类型ID',

`book_type` varchar(32) NOT NULL DEFAULT '',

PRIMARY KEY(`type_id`)

) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci COMMENT '书籍类型';

"""

books = """

CREATE TABLE IF NOT EXISTS `books` (`book_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍ID',

`name` varchar(64) NOT NULL DEFAULT '' COMMENT '书籍名称',

`price` decimal(10, 2) unsigned zerofill NOT NULL COMMENT '单价',

`stock` int unsigned NOT NULL DEFAULT 1000 COMMENT '库存量',

`author` varchar(128) NOT NULL DEFAULT '' COMMENT '作家名称',

`publishing_house` varchar(64) NOT NULL DEFAULT '' COMMENT '出版社',

`publishing_date` date NOT NULL COMMENT '出版日期',

`type_id` int unsigned NOT NULL COMMENT '书籍类型ID',

PRIMARY KEY(`book_id`),

KEY `fk_book_type_id` (`type_id`),

CONSTRAINT `fk_book_type_id` FOREIGN KEY(`type_id`) REFERENCES `book_types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci COMMENT = '书籍信息';

"""

users = """

CREATE TABLE IF NOT EXISTS `users` (

`user_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',

`user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名称',

`register_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户注册时间',

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '用户表';

"""

orders = """

CREATE TABLE IF NOT EXISTS `orders` (

`order_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',

`user_id` bigint unsigned NOT NULL COMMENT '用户ID',

`order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单时间',

PRIMARY KEY (`order_id`),

KEY `fk_user_id_idx` (`user_id`),

CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '订单表';

"""

order_items = """

CREATE TABLE IF NOT EXISTS `order_items` (

`order_item_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '订单项目ID',

`order_id` bigint unsigned NOT NULL COMMENT '订单ID',

`book_id` bigint unsigned NOT NULL COMMENT '书籍ID',

`quantity` int unsigned NOT NULL COMMENT '商品数量',

PRIMARY KEY(`order_item_id`),

KEY `fk_order_id` (`order_id`),

KEY `fk_book_id` (`book_id`),

CONSTRAINT `fk_book_id` FOREIGN KEY(`book_id`) REFERENCES `books` (`book_id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `fk_order_id` FOREIGN KEY(`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci COMMENT = '订单项';

"""

tables_schema = [

{'tbl_name': 'book_types', 'tbl_schema': book_types},

{'tbl_name': 'books', 'tbl_schema': books},

{'tbl_name': 'users', 'tbl_schema': users},

{'tbl_name': 'orders', 'tbl_schema': orders},

{'tbl_name': 'order_items', 'tbl_schema': order_items}

]

def create_table(tables_schema: list) -> None:

for table_schema in tables_schema:

tbl_name = table_schema['tbl_name']

tbl_schema = table_schema['tbl_schema']

try:

cursor.execute(tbl_schema)

print(f"创建{tbl_name}表成功")

except Exception as e:

print(f"创建{tbl_name}表失败", e)

finally:

conn.commit()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、创建表

create_table(tables_schema)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

创建触发器creat_triggers.py

#!/apps/venv/bin/pythonimport pymysql

import yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

tg_insert_o = """

CREATE TRIGGER tg_insert_o

AFTER INSERT

ON order_items FOR EACH ROW

UPDATE `books` SET `stock`=`stock`- new.`quantity` WHERE `book_id`= new.`book_id`;

"""

tg_update_o = """

CREATE TRIGGER tg_update_o

AFTER UPDATE

ON order_items FOR EACH ROW

UPDATE `books` SET `stock`= old.`quantity`- new.`quantity`+`stock` WHERE `book_id`= old.`book_id`;

"""

tg_delete_o = """

CREATE TRIGGER tg_delete_o

AFTER DELETE

ON order_items FOR EACH ROW

UPDATE `books` SET `stock`=`stock`+ old.`quantity` WHERE `book_id`= old.`book_id`;

"""

triggers_schema = [

{'tg_name': 'tg_insert_o', 'tg_schema': tg_insert_o},

{'tg_name': 'tg_update_o', 'tg_schema': tg_update_o},

{'tg_name': 'tg_delete_o', 'tg_schema': tg_delete_o}

]

def create_trigger(triggers_schema: list) -> None:

for trigger_schema in triggers_schema:

tg_name = trigger_schema['tg_name']

tg_schema = trigger_schema['tg_schema']

try:

cursor.execute(tg_schema)

print(f"创建触发器{tg_name}成功")

except Exception as e:

print(f"创建触发器{tg_name}表失败", e)

finally:

conn.commit()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、创建触发器

create_trigger(triggers_schema)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

​编辑

插入语句creat_insert.py

#!/apps/venv/bin/pythonimport pymysql

import yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

book_types = [

(1, '计算机'), (2, '历史'), (3, '小说'), (4, '科幻'), (5, '随笔'), (6, '心理学'), (7, '科学'), (8, '传记'), (9,'励志'), (10, '悬疑'), (11, '哲学'), (12, '语言学'), (13, '人工智能'), (14, '数据分析'), (15, '数据挖掘')

]

books = [

(1, 'Python编程从入门到精通', 49.90, '张三', '图灵出版社', '2022-01-15', 1),

(2, '数据结构与算法分析', 35.00, '李四', '清华大学出版社', '2021-09-30', 1),

(3, '人类简史', 25.50, '尤瓦尔·赫拉利', '中信出版社', '2020-03-12', 2),

(4, '活着', 18.00, '余华', '作家出版社', '2019-05-08', 3),

(5, '白夜行', 22.50, '东野圭吾', '南海出版公司', '2021-11-20', 3),

(6, 'Java编程思想', 59.00, 'Bruce Eckel', '机械工业出版社', '2020-07-10', 1),

(7, '三体', 28.80, '刘慈欣', '重庆出版社', '2018-12-18', 4),

(8, '围城', 15.50, '钱钟书', '人民文学出版社', '2017-10-22', 3),

(9, 'JavaScript权威指南', 42.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1),

(10, '活法', 19.00, '梁文道', '江苏文艺出版社', '2019-09-10', 5),

(11, '思考，快与慢', 26.50, '丹尼尔·卡尼曼', '中信出版社', '2018-06-05', 6),

(12, '时间简史', 32.00, '史蒂芬·霍金', '湖南科学技术出版社', '2017-11-15', 7),

(13, 'C++ Primer Plus', 55.00, 'Stephen Prata', '电子工业出版社', '2021-08-20', 1),

(14, '红楼梦', 24.50, '曹雪芹', '人民文学出版社', '2019-03-10', 3),

(15, '西游记', 23.00, '吴承恩', '人民文学出版社', '2020-09-18', 3),

(16, '乔布斯传', 38.00, '沃尔特·艾萨克森', '中信出版社', '2018-04-15', 8),

(17, '高效能人士的七个习惯', 28.80, '史蒂芬·柯维', '华西出版社', '2019-07-22', 9),

(18, '囚鸟', 20.00, '冯内古特', '上海译文出版社', '2020-12-08', 3),

(19, 'C语言程序设计', 29.90, '谭浩强', '清华大学出版社', '2017-06-30', 1),

(20, 'C++语言程序设计', 39.90, '谭浩强', '清华大学出版社', '2019-08-08', 1),

(21, '哈利·波特与魔法石', 28.00, 'J.K.罗琳', '人民文学出版社', '2019-08-15', 3),

(22, '一九八四', 24.90, '乔治·奥威尔', '新经典文化', '2020-05-20', 3),

(23, 'C#图解教程', 35.00, '某某某', '某某出版社', '2021-12-01', 1),

(24, '东方快车谋杀案', 19.90, '阿加莎·克里斯蒂', '外语教学与研究出版社', '2018-11-10', 10),

(25, '思考的艺术', 32.50, '罗尔斯·邓南巴', '北京联合出版公司', '2020-07-25', 11),

(26, '黑客与画家', 29.90, 'Paul Graham', '人民邮电出版社', '2019-04-05', 1),

(27, '长夜难明', 26.80, '王小波', '作家出版社', '2017-12-15', 3),

(28, 'JavaScript高级程序设计', 55.50, 'Nicholas C. Zakas', '人民邮电出版社', '2022-02-01', 1),

(29, '月亮与六便士', 22.00, '毛姆', '译林出版社', '2018-08-12', 3),

(30, '简爱', 18.50, '夏洛蒂·勃朗特', '人民文学出版社', '2019-01-20', 3),

(31, 'React进阶实践', 38.00, '某某某', '某某出版社', '2021-07-10', 1),

(32, '解忧杂货店', 21.50, '东野圭吾', '南海出版公司', '2019-12-08', 3),

(33, 'Python数据科学手册', 49.50, 'Jake VanderPlas', '机械工业出版社', '2020-10-15', 1),

(34, '追风筝的人', 25.80, '卡勒德·胡赛尼', '上海人民出版社', '2021-06-30', 3),

(35, '深入理解计算机系统', 55.00, 'Randal E. Bryant / David R. O’Hallaron',

'机械工业出版社', '2022-03-20', 1),

(36, '白夜行', 22.50, '东野圭吾', '南海出版公司', '2021-11-20', 3),

(37, 'JavaScript高级编程', 46.80, 'Zakas', '电子工业出版社', '2020-09-10', 1),

(38, '百年孤独', 38.00, '加西亚·马尔克斯', '南海出版公司', '2019-05-12', 3),

(39, '编程珠玑', 45.00, 'Jon Bentley', '人民邮电出版社', '2018-08-25', 1),

(40, 'JavaScript权威指南', 42.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1),

(41, '飘', 33.50, '玛格丽特·米切尔', '译林出版社', '2021-09-18', 3),

(42, '计算机网络', 39.90, '谢希仁', '电子工业出版社', '2020-12-01', 1),

(43, '偷影子的人', 24.80, '马克·李维', '南海出版公司', '2018-11-15', 3),

(44, '深入理解现代英语', 29.50, '余光中', '北京大学出版社', '2021-03-10', 12),

(45, 'C Primer Plus', 38.00, 'Stephen Prata', '人民邮电出版社', '2022-05-20', 1),

(46, '挪威的森林', 27.00, '村上春树', '上海译文出版社', '2020-11-08', 3),

(47, '计算机程序设计艺术', 65.00, '高德纳', '机械工业出版社', '2021-07-01', 1),

(48, '活出生命的意义', 23.50, '维克多·弗兰克尔', '湖南科学技术出版社', '2019-09-22', 6),

(49, '程序员的自我修养', 46.00, '俞甲子', '人民邮电出版社', '2022-01-15', 1),

(50, '万历十五年', 28.90, '黄仁宇', '上海人民出版社', '2020-07-30', 2),

(51, '高效能人士的思维模式', 25.00, '史蒂芬·柯维', '经济管理出版社', '2018-06-10', 9),

(52, 'JavaScript权威指南', 42.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1),

(53, '解忧杂货店', 21.50, '东野圭吾', '南海出版公司', '2019-12-08', 3),

(54, 'Python数据科学手册', 49.50, 'Jake VanderPlas', '机械工业出版社', '2020-10-15', 1),

(55, '追风筝的人', 25.80, '卡勒德·胡赛尼', '上海人民出版社', '2021-06-30', 3),

(56, '深入理解计算机系统', 55.00, 'Randal E. Bryant / David R. O’Hallaron',

'机械工业出版社', '2022-03-20', 1),

(57, '白夜行', 22.50, '东野圭吾', '南海出版公司', '2021-11-20', 3),

(58, 'JavaScript高级编程', 46.80, 'Zakas', '电子工业出版社', '2020-09-10', 1),

(59, '百年孤独', 38.00, '加西亚·马尔克斯', '南海出版公司', '2019-05-12', 3),

(60, 'Python深度学习', 36.50, '伊恩·古德费洛 / 布拉姆·鲁滨斯', '机械工业出版社', '2021-04-15', 13),

(61, '机器学习实战', 42.80, '彼得·哈林顿 / 德里克·班汀利', '电子工业出版社', '2020-09-25', 13),

(62, '深度学习', 55.50, '伊恩·古德费洛 / 哈罗德·埃尔南德斯 / 弗朗索瓦·肖莱', '人民邮电出版社', '2022-03-05', 13),

(63, 'Python数据分析', 39.90, '麦可•梅勒 / 莫琳•凯利', '机械工业出版社', '2021-08-10', 14),

(64, '机器学习', 45.80, 'Tom M. Mitchell', '清华大学出版社', '2020-11-20', 13),

(65, '数据挖掘概念与技术', 56.50, 'Jiawei Han / Micheline Kamber / Jian Pei',

'电子工业出版社', '2021-09-15', 15),

(66, '计算机视觉', 49.50, 'Richard Szeliski', '机械工业出版社', '2022-02-28', 13),

(67, '深度学习之PyTorch', 36.00, 'I. Antiga / L. E. G. Ortiz / E. Conti / E. L. Denton',

'机械工业出版社', '2021-07-10', 13),

(68, '神经网络与深度学习', 28.90, 'Michael Nielsen', '人民邮电出版社', '2020-03-30', 13),

(69, '自然语言处理', 42.00, 'Jacob Eisenstein / Emily Bender / Noah A. Smith / Yejin Choi',

'清华大学出版社', '2021-12-01', 13),

(70, '动手学深度学习', 49.90, '李沐 / 阿斯顿·张 / 云开发团队', '电子工业出版社', '2021-06-15', 13),

(71, '统计学习方法', 38.50, '李航', '清华大学出版社', '2022-04-10', 13),

(72, '深度学习入门', 32.80, '斋藤康毅', '人民邮电出版社', '2020-09-08', 13),

(73, '模式识别与机器学习', 58.50, 'Christopher M. Bishop', '电子工业出版社', '2021-05-15', 13),

(74, 'Python人工智能开发实战', 35.90, '陆运坤', '机械工业出版社', '2020-11-20', 13),

(75, '计算机视觉：算法与应用', 49.00, 'Richard Szeliski', '机械工业出版社', '2022-01-30', 13),

(76, '神经网络与深度学习', 29.90, 'Michael Nielsen', '人民邮电出版社', '2021-08-10', 13),

(77, '机器学习实战', 42.80, '彼得·哈林顿 / 德里克·班汀利', '电子工业出版社', '2020-09-25', 13),

(78, '自然语言处理入门', 36.00, '黄峰达 / 陈俊廷', '电子工业出版社', '2021-11-10', 13),

(79, '图解深度学习', 45.50, '中山恭平', '机械工业出版社', '2021-02-20', 13),

(80, '机器学习：算法、理论与应用', 56.90, '周志华', '清华大学出版社', '2022-03-25', 13),

(81, 'Python深度学习', 36.50, '伊恩·古德费洛 / 布拉姆·鲁滨斯', '机械工业出版社', '2021-04-15', 13),

(82, '强化学习导论', 33.50, 'Richard S. Sutton / Andrew G. Barto',

'电子工业出版社', '2020-12-08', 13),

(83, '模式识别', 48.00, '贺涛 / 刘洪波', '电子工业出版社', '2022-05-30', 13),

(84, 'TensorFlow实战', 32.50, '黄文坚 / 唐源 / 宋亚轩', '人民邮电出版社', '2021-03-12', 13),

(85, '数据挖掘导论', 55.00, 'Pang-Ning Tan / Michael Steinbach / Vipin Kumar',

'机械工业出版社', '2022-02-15', 15),

(86, 'Python网络神经', 38.90, '伊恩·古德费洛 / 布拉姆·鲁滨斯', '机械工业出版社', '2021-09-10', 13),

(87, '机器学习：实用案例解析', 29.80, 'Drew Conway / John Myles White',

'清华大学出版社', '2020-07-25', 13),

(88, '深度学习之TensorFlow：入门、原理与应用', 42.00,

'李岳 / 熊云飞 / 刘鹏', '人民邮电出版社', '2021-12-30', 13),

(89, '机器学习实战', 39.90, '彼得·哈林顿 / 德里克·班汀利', '电子工业出版社', '2021-01-15', 13),

(90, '人类简史', 33.80, '尤瓦尔·赫拉利', '中信出版社', '2021-09-15', 2),

(91, '明朝那些事儿', 28.50, '当年明月', '湖南文艺出版社', '2020-07-08', 2),

(92, '百年孤独', 38.00, '加西亚·马尔克斯', '南海出版公司', '2019-05-12', 3),

(93, '明朝那些事儿', 32.00, '当年明月', '湖南文艺出版社', '2020-11-25', 2),

(94, '文明的冲突', 29.50, '塞缪尔·P·亨廷顿', '中信出版社', '2020-09-20', 2),

(95, '人类群星闪耀时', 34.90, '史蒂夫·约翰逊', '中信出版社', '2021-04-10', 2),

(96, '大秦帝国', 31.80, '孙皓晖', '中信出版社', '2020-12-15', 2),

(97, '慢读中国史', 27.50, '吕思勉', '湖南人民出版社', '2019-11-08', 2),

(98, '世界通史', 55.50, '吕思勉', '中国青年出版社', '2022-03-25', 2),

(99, '万历十五年', 32.90, '黄仁宇', '上海人民出版社', '2021-08-12', 2),

(100, '今日简史', 32.30, '尤瓦尔·赫拉利', '中信出版社', '2021-08-12', 2),

]

def insert_data(tbl_name: str, columns: list, data: list) -> None:

column = ','.join([f'`{col}`' for col in columns])

value = ','.join(['%s' for _ in range(len(columns))])

try:

cursor.execute("SET FOREIGN_KEY_CHECKS = 0")

cursor.execute(f"TRUNCATE TABLE {tbl_name}")

cursor.execute("SET FOREIGN_KEY_CHECKS = 1")

effect_row = cursor.executemany(

f"INSERT INTO `{tbl_name}` ({column}) VALUES ({value})", data)

print(f"{tbl_name}表成功插入{effect_row}行数据")

except Exception as e:

print(f"{tbl_name}表插入数据失败", e)

finally:

conn.commit()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、插入数据

# 3.1、插入书籍类型数据

insert_data('book_types', ['type_id', 'book_type'], book_types)

# 3.2、插入书籍信息数据

insert_data('books', ['book_id', 'name', 'price', 'author',

'publishing_house', 'publishing_date', 'type_id'], books)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

 新增数据insert_data.py

#!/apps/venv/bin/pythonimport pymysqlimport yamldef config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

users = [

(1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David'), (5, 'Eve'), (6, 'Fiona'), (7, 'George'), (8, 'Hannah'), (9,

'Isaac'), (10, 'Jessica'), (11, 'Kevin'), (12, 'Linda'), (13, 'Michael'), (14, 'Natalie'), (15, 'Oliver')

]

orders = [

(2), (1), (3), (3), (4), (7), (8), (10), (12), (15), (7), (2), (6)

]

order_items = [

(1, 3, 1), (1, 7, 8), (1, 8, 2), (2, 3, 5), (2, 1, 8), (3, 99, 2), (4, 23, 1), (5, 7, 8), (6, 23, 2), (7,

13, 2), (8, 13, 1), (9, 57, 8), (10, 68, 3), (11, 38, 2), (12, 88, 7), (13, 3, 1), (4, 17, 8), (7, 78, 1)

]

def insert_data(tbl_name: str, columns: list, data: list) -> None:

column = ','.join([f'`{col}`' for col in columns])

value = ','.join(['%s' for _ in range(len(columns))])

try:

cursor.execute("SET FOREIGN_KEY_CHECKS = 0")

cursor.execute(f"TRUNCATE TABLE {tbl_name}")

cursor.execute("SET FOREIGN_KEY_CHECKS = 1")

effect_row = cursor.executemany(

f"INSERT INTO `{tbl_name}` ({column}) VALUES ({value})", data)

print(f"{tbl_name}表成功插入{effect_row}行数据")

except Exception as e:

print(f"{tbl_name}表插入数据失败", e)

finally:

conn.commit()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、新增数据

# 3.1、新增用户数据

insert_data('users', ['user_id', 'user_name'], users)

# 3.2、新增订单数据

insert_data('orders', ['user_id'], orders)

# 3.3、新增订单项数据

insert_data('order_items', ['order_id',

'book_id', 'quantity'], order_items)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

查询数据select_data.py 

# !/apps/venv/bin/pythonimport pymysql

import yamldef config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

def sql_read(sql_info: str) -> ():

try:

cursor.execute(sql_info)

print(cursor.fetchall())

except Exception as e:

print(f"SQL执行失败", e)

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、查询订单信息

sql_order_item_id = """

SELECT bt.book_type,

b.`name`,

b.author,

b.`stock` AS '库存',

u.user_name,

oi.quantity AS '购买数量'

FROM orders o

LEFT JOIN users u ON o.user_id= u.user_id

LEFT JOIN order_items oi ON o.order_id= oi.order_id

LEFT JOIN books b ON oi.book_id= b.book_id

LEFT JOIN book_types bt ON b.type_id= bt.type_id;

"""

sql_read(sql_order_item_id)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

更新数据update_data.py

# !/apps/venv/bin/pythonimport pymysql

import yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, passworddef sql_write(sql_info: str) -> None:

try:

cursor.execute(sql_info)

print("SQL执行成功")

except Exception as e:

print(f"SQL执行失败", e)

finally:

conn.commit()

def sql_read(sql_info: str) -> ():

try:

cursor.execute(sql_info)

return cursor.fetchall()

except Exception as e:

print(f"SQL执行失败", e)

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、Bob将自己的订单《三体》都改为5本

sql_order_item_id = """

SELECT oi.`order_item_id`

FROM orders o

LEFT JOIN users u ON o.user_id= u.user_id

LEFT JOIN order_items oi ON o.order_id= oi.order_id

LEFT JOIN books b ON oi.book_id= b.book_id

WHERE b.name= '三体'

AND u.`user_name`= 'Bob'

"""

# Bob购买《三体》的订单项ID

order_item_ids = ','.join([str(j) for i in sql_read(sql_order_item_id) for j in i])

sql = f"""

UPDATE `order_items`

SET `quantity`= 5

WHERE `order_item_id` IN ({order_item_ids})

"""

sql_write(sql)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

删除数据delete.py

# !/apps/venv/bin/pythonimport pymysqlimport yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

def sql_write(sql_info: str) -> None:

try:

cursor.execute(sql_info)

print("SQL执行成功")

except Exception as e:

print(f"SQL执行失败", e)

finally:

conn.commit()

def sql_read(sql_info: str) -> ():

try:

cursor.execute(sql_info)

return cursor.fetchall()

except Exception as e:

print(f"SQL执行失败", e)

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、Bob不买《围城》了

sql_order_item_id = """

SELECT oi.`order_item_id`

FROM orders o

LEFT JOIN users u ON o.user_id= u.user_id

LEFT JOIN order_items oi ON o.order_id= oi.order_id

LEFT JOIN books b ON oi.book_id= b.book_id

WHERE b.name= '围城'

AND u.`user_name`= 'Bob'

"""

# Bob购买《围城》的订单项ID

order_item_ids = ','.join([str(j) for i in sql_read(sql_order_item_id) for j in i])

sql = f"DELETE FROM `order_items` WHERE `order_item_id` IN ({order_item_ids})"

sql_write(sql)

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

删除表格delete_table.py

#!/apps/venv/bin/pythonimport pymysqlimport yaml

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

def drop_table(tbl_name: str) -> None:

try:

cursor.execute(f"DROP TABLE IF EXISTS {tbl_name}")

print(f"删除{tbl_name}表成功")

except Exception as e:

print(f"删除{tbl_name}表失败", e)

finally:

conn.commit()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

conn = pymysql.connect(host=db_host, port=3306, user=db_user,

passwd=db_passwd, db='commodity', charset="utf8")

# 2、创建游标

cursor = conn.cursor()

# 3、删除表

drop_table('tbl_test')

# 4、关闭游标

cursor.close()

# 5、关闭连接

conn.close()

nginx配置

yum install -y nginx

systemctl start nginx

systemctl status nginx

wget 'https://labfileapp.oss-cn-hangzhou.aliyuncs.com/clouder/simsun.tar.gz'

yum install fontconfig -y

yum install mkfontscale -y

# 新建目录

mkdir /usr/share/fonts/chinese#解压中文字体包

tar -xzf simsun.tar.gz #移动中文字体文件

mv simsun/* /usr/share/fonts/chinese# 更改目录权限

chmod -R 755 /usr/share/fonts/chinese/

# 创建可扩展字体文件的索引

mkfontscale# 创建字体文件的索引

mkfontdir# 创建字体信息缓存文件

fc-cache

# 查看所有字体列表

fc-list# 查看中文字体命令及执行结果

fc-list :lang=zh

数据分析data_analysis.py

# !/apps/venv/bin/pythonimport yamlimport pandas as pdfrom sqlalchemy import create_engineimport matplotlib.pyplot as pltimport matplotlib.font_manager as font_manager

# -------- 开始# 检查是否包含雅黑、黑体、仿宋、宋体

font_path = font_manager.findSystemFonts(fontpaths=None, fontext='ttf')

font_path = [f for f in font_path if any(font_name in f.lower() for font_name in ['msyh', 'simhei', 'heiti', 'simsun'])]

if font_path:

plt.rcParams['font.sans-serif'] = font_manager.FontProperties(fname=font_path[0]).get_name()# -------- 结束

def config() -> [str, str, str]:

with open('/apps/account.yml', 'r') as f:

conf = yaml.load(f, Loader=yaml.FullLoader)

host = conf['database']['host']

username = conf['database']['username']

password = conf['database']['password']

return host, username, password

def func_book_type_quantity(sql_info, engine):

conn = engine.connect()

try:

df = pd.read_sql(sql_info, conn)

# 每种书的销量

book_type_quantity = df.groupby('book_type')['quantity'].sum()

# 准备绘图数据

df = pd.DataFrame(book_type_quantity)

df.plot.bar(rot=0) # rot设置轴标签旋转度数

# 设置标题和标签

plt.title('每类书的销量')

plt.xlabel('类别')

plt.ylabel('销量')

# 调整 y 轴刻度范围

plt.ylim(0, 35)

# 保存图片

plt.savefig('/usr/share/nginx/html/booktype_quantity.png')

except Exception as e:

raise Exception("读取数据时发生错误：" + str(e))

finally:

conn.close()

if __name__ == "__main__":

# 1、创建连接

db_host, db_user, db_passwd = config()

ms_engine = create_engine(f"mysql+pymysql://{db_user}:{db_passwd}@{db_host}:3306/commodity")

# 2、每类书销量大于10作者不是某某某的书籍销售情况

sql = """

SELECT bt.book_type,

b.`name`,

b.author,

b.publishing_house,

u.user_name,

oi.quantity

FROM orders o

LEFT JOIN users u ON o.user_id= u.user_id

LEFT JOIN order_items oi ON o.order_id= oi.order_id

LEFT JOIN books b ON oi.book_id= b.book_id

LEFT JOIN book_types bt ON b.type_id= bt.type_id

WHERE b.type_id IN(

SELECT b.type_id

FROM order_items oi

LEFT JOIN books b ON oi.book_id= b.book_id

GROUP BY b.type_id

HAVING SUM(oi.quantity)> 10)

AND b.`author`<> '某某某'

ORDER BY b.type_id DESC

"""

# 3、计算每类书的销量

func_book_type_quantity(sql, ms_engine)

http://ip/booktype_quantity.png

​