10.MySQL数据库操作

数据库

数据库概念及作用

数据库概念：一些特殊格式的文件的集合
数据库的作用:用来存储各种数据
- 数据库的使用优势：持久存储、存取效率高、支持可扩展
  ACID是衡量事务的四个特性：
- 原子性（Atomicity，或称不可分割性）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
- 原子性：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log
- 持久性：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log
- 隔离性：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）
- 一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

数据库分类及特点

关系型数据库：基于关系模型建立，用二维表进行数据存储的数据库：MySQL Oracle
非关系型数据库：不是基于二维表，基于key-value 方式存储：MongoDB Redis

数据库管理系统

数据库管理系统
数据库管理系统管理数据库的一套软件系统，简称DBMS
数据库管理系统构成：
- 客户端
- 服务端
- 数据库文件
关系型数据库管理系统： RDBMS
SQL
- 作用：客户端跟服务端通信的特殊 “语言”
- 含义：结构化查询语言
- 分类：
- DQL 数据查询语言，用来查询
- DML 数据操作语言，用来增删改查
- DDL 数据定义语言，创建、删除
- DCL 数据库控制语言
数据库的核心元素
- 数据库：数据库系统
- 数据表：二维表，（行、列）
- 记录（二维表的一行，要求数据要完整）
- 字段（二维表的列，要求一列的数据类型要一致）
数据保存的流程
- 创建数据库 --> 创建数据表—>字段 ---> 保存数据

数据完整性

完整性的作用：保证数据的正确性
实体完整性
- 主键约束：不能为空，也不能重复 primary key
- 唯一约束: 能为空，但是不能重复 unique key
域完整性约束
- 非空约束: 该字段信息不能为空 not null
- 默认约束：这个字段可以设置默认值 default
参照完整性
- 外键约束: 建立表和表外键的关系 foreign key
用户定义完整性
- 字段的值必须是设定的范围之内

# 创建到MySQL的数据链接
from pymysql import connect
from cryptography import *

# 获取到MySQL数据库的链接对象
conn = connect(
    host='home.vimll.com',
    port=33066,
    user='python',
    password='xxxxxxxxxxxx',
    database="py_sql"
)

# 打印MySQL数据库软件信息
print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()
conn.select_db("py_sql")

# 使用游标对象，执行sql语句
# cursor.execute("create table test_pymysql(id INT, info VARCHAR(20));")

# 查询
cursor.execute("select * from goods order by id desc limit 3")
# cursor.execute("show tables")
# print(cursor.fetchone())
# print(cursor.fetchmany())
# print(cursor.fetchall())
results: tuple = cursor.fetchall()
for r in results:
    print(r)

# 关闭游标对象
cursor.close()
# 关闭到数据库的链接
conn.close()

8.0.33
(23, '娃哈哈AD钙奶', '饮料', '娃哈哈', Decimal('9.900'), b'\x01', b'\x00')
(22, '娃哈哈AD钙奶', '饮料', '娃哈哈', Decimal('9.900'), b'\x01', b'\x00')
(20, 'x3250 m4机架式服务器', '服务器/⼯作站', 'ibm', Decimal('6888.000'), b'\x01', b'\x00')

增删改

# 创建到MySQL的数据链接
from pymysql import connect
from cryptography import *

# 获取到MySQL数据库的链接对象
conn = connect(
    host='home.vimll.com',
    port=33066,
    user='python',
    password='xxxxxxxxxxxx',
    database="py_sql"
)

# 打印MySQL数据库软件信息
print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()
conn.select_db("py_sql")

# 定义执行sql语句
sql = "update goods set price = 99.000 where id = 21"
# 执行
row = cursor.execute(sql)
# 执行结果
print(f"影响{row}行")
# 提交
conn.commit()

# 定义执行sql语句
sql = "insert into goods values (0, '娃哈哈AD钙奶', '饮料', '娃哈哈', '9.9', default, default);"
# 执行
row = cursor.execute(sql)
# 执行结果
print(f"影响{row}行")
# 提交
conn.commit()

# 定义执行sql语句
sql = "delete from goods where id = 21"
# 执行
row = cursor.execute(sql)
# 执行结果
print(f"影响{row}行")
# 提交
conn.commit()

# 关闭游标对象
cursor.close()
# 关闭到数据库的链接
conn.close()

8.0.33
影响0行
影响1行
影响1行

SQL防注入

防注入的思路：
- sql中需要变化的地方，可以占位符 %s %d...
- sql ="select * from goods where name = %s order by id desc"
- 注意：SQL 可以出现多个占位符，后续列表中元素的个数要与之对应
- 把参数封装到列表中：params = [input_name]
- 把列表传递给 execute(sql, 列表)：result = cur.execute(sql, params)

# 创建到MySQL的数据链接
from pymysql import connect
from cryptography import *

# 获取到MySQL数据库的链接对象
conn = connect(
    host='home.vimll.com',
    port=33066,
    user='python',
    password='xxxxxxxxxxxx',
    database="py_sql"
)

# 打印MySQL数据库软件信息
print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()
conn.select_db("py_sql")

# 执行的sql语句
sql = "select * from goods where id = '%s' limit 3"
params = [int(input("输入你要查询的商品编号："))]

# 查询
cursor.execute(sql, params)
results: tuple = cursor.fetchall()
for r in results:
    print(r)

# 关闭游标对象
cursor.close()
# 关闭到数据库的链接
conn.close()

爬虫实战:爬取数据并保存到数据库中

"""
定义函数，实现获取影片地址 get_movie_link
1、设置爬去的电影列表页面
2、打开电影列表页，获取数据，并解码得到网页html文本内容
3、使用正则匹配获得 页面中的所有的影片名称和对应内容页连接
4、循环打开内容页，获取下载地址
5、保存影片名称和地址到字典中

定义主函数main 调用 get_movie_link 函数，获取地址
"""
import re
import urllib.request
import threading
from pymysql import connect
from cryptography import *

# 定义字典保存下载的影片信息
class Spider(object):

    def __init__(self):
        self.film_dict = {}
        self.i = 1
        self.lock1 = threading.Lock()

    def start(self):

        # 调用下载函数，获取下载连接
        for page in range(2, 8):
            t1 = threading.Thread(target=self.get_movie_link, args=(page,))
            t1.start()
            t1.join()
        # 得到字典对应的数组
        list1 = self.film_dict.items()

        # 获取到MySQL数据库的链接对象
        conn = connect(
            host='home.vimll.com',
            port=33066,
            user='python',
            password='xxxxxxxxxxxx',
            database="movie_db"
        )

        # 打印MySQL数据库软件信息
        print(conn.get_server_info())

        # 获取游标对象
        cursor = conn.cursor()
        conn.select_db("movie_db")

        # 遍历下载字典，获取影片名称、下载地址
        for film_name, film_download_url in list1:
            print(film_name, "|", film_download_url)
            """检测数据是否已经存在"""
            # 1）定义sql 根据影片名称和地址 查询
            sql = "select film_name from movie_link where film_name = %s"
            # 2）执行查询，并获取查询的记录数
            ret = cursor.execute(sql, film_name)
            # 3) 如果获取的记录数 > 0     已存在，继续执行
            # 4) 如果获取的记录数 = 0     执行sql添加数据
            if ret:
                print("保存失败!影片:[%s]" % film_name)
                continue
            else:
                # 执行的sql语句
                sql = "INSERT INTO movie_link(film_name,film_link) VALUES(%s,%s)"
                # 执行sql
                ret = cursor.execute(sql, [film_name, film_download_url])
                # 如果插入成功，给出提示
                if ret:
                    print("保存成功！影片[%s]" % film_name)
                conn.commit()

        # 关闭游标对象
        cursor.close()
        # 关闭到数据库的链接
        conn.close()

    def get_movie_link(self, page):
        # 获取列表页影片信息
        # 定义列表页的地址
        film_list_url = "https://www.ygdy8.net/html/gndy/dyzz/list_23_%d.html" % page
        # 打开URL，获取数据
        response_list = urllib.request.urlopen(film_list_url)
        # 通过read()读取网络资源数据
        response_list_data = response_list.read()
        response_list_txt = response_list_data.decode("GBK")
        # 使用正则得到所有影片内容地址
        url_list = re.findall(r"<a href=\"(.*)\" class=\"ulink\">\w+《(.*)》", response_list_txt)
        # 使用循环遍历
        for content_url, film_name in url_list:
            content_url = "https://www.ygdy8.net" + content_url
            # print(f"影片名称：<<{film_name}>> -> 影片地址：{content_url}")
            response_content = urllib.request.urlopen(content_url)
            response_content_txt = response_content.read().decode("GBK")
            # print(response_content_txt)
            response_content_bt = re.search(r"<a target=\"_blank\" href=\"(.*)\"><strong>", response_content_txt)
            # 影片名与磁链保存至字典
            self.film_dict[film_name] = response_content_bt.group(1)
            # print(film_dict)
            # print(f"影片名称：<<{film_name}>> -> 影片磁链地址：{response_content_bt.group(1)}")
            # 影片名与磁链保存至文件
            # with open("./电影下载.txt", "a", encoding="UTF-8") as f:
            #     f.write(f"影片名称：<<{film_name}>> -> 影片磁链地址：{response_content_bt.group(1)}\n")

def main():
    film_spider = Spider()
    film_spider.start()

if __name__ == '__main__':
    main()

爬虫实战:展示电影数据到网页中

# 1、导入模块
import socket
from pymysql import connect
from cryptography import *

# 定义函数 request_handler()
def request_handler(new_client_socket, ip_port):
    # 7、接收客户端浏览器发送的请求协议
    request_data = new_client_socket.recv(1024).decode()
    # print(request_data)
    # 8、判断协议是否为空
    if not request_data:
        print(f"客户端{str(ip_port)}已经下线！")
        new_client_socket.close()
        return
    else:
        # 9、拼接响应的报文
        # 9.1 响应行
        response_line = "HTTP/1.1 200 OK\r\n"
        # 9.2 响应头
        response_header = "Server:Python20WS/2.1\r\n"
        response_header +=  "Content-Type: text/html;charset=utf-8\r\n"
        # 9.3 响应空行
        response_blank = "\r\n"

        # 连接数据库
        # 获取到MySQL数据库的链接对象
        conn = connect(
            host='home.vimll.com',
            port=33066,
            user='python',
            password='xxxxxxxxxxxx',
            database="movie_db"
        )
        cursor = conn.cursor()
        # 执行的sql语句
        sql = "SELECT * from movie_link order by id desc"
        # 执行sql
        cursor.execute(sql)
        # 返回所有数据
        result_list = cursor.fetchall()
        response_body = ''
        for row in result_list:
            # <a href={row[2]}>{row[2]}</a> 超链接
            response_body += f"{row[0]}.{row[1]}    下载地址：[<a href='{row[2]}'>{row[2]}</a>] <br>"

        cursor.close()
        conn.close()
        # print(response_body)
        # # 9.4 响应的主体
        # with open("D:/dist/index.html", "rb") as file:
        #     response_body = file.read()
        # 9.5 响应报文拼接
        response_data = response_line.encode() + response_header.encode() + response_blank.encode() + response_body.encode()
        # 10、发送响应报文
        new_client_socket.send(response_data)
        # 11、关闭当前连接
        new_client_socket.close()

def main():
    """主函数段"""
    # 2、创建套接字
    tcp_server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    # 3、设置地址重用
    tcp_server_socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, True)
    # 4、绑定端口
    tcp_server_socket.bind(("", 8080))
    # 5、设置监听，让套接字由主动变为被动接收
    tcp_server_socket.listen(128)

    # 可循环访问
    while True:
        # 6、接受客户端连接
        new_client_socket, ip_port = tcp_server_socket.accept()
        # 调用函数
        request_handler(new_client_socket, ip_port)

    # 12、关闭操作
    tcp_server_socket.close()

if __name__ == '__main__':
    main()