DW-32-rec_sys-Task02

Task02 数据库的基本使用

1 数据库在项目中的用途

• 来自队长大大的数据库思路整理——
• MySQL数据库：用于存储结构化数据，包括用户信息和用户阅读日志
• MongoDB数据库：用于存储爬取到的新浪新闻数据，以及画像数据（用户画像、新闻特征画像、备份前端展示的新闻画像）
• Redis内存数据库：用于存储变化快的数据，主要存储新闻动态画像数据（即用户对新闻的行为数据）、热门页列表数据、推荐页列表数据、用户曝光表、新闻详情信息

MySQL和Redis以前用过，这次主要的想通过例子训练一下用法，MongoDB首次接触，还是要从头学起

2 MySQl数据库实战例子

• 示例1：

  在这个示例中，我们将做两件事情：创建表和插入数据。

import pymysql

# 以admin身份连接到数据库shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

# 创建游标
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

# 1. 创建了一个表
sql = """
CREATE TABLE Employee(
    id INT PRIMARY KEY,
    name CHAR(15) NOT NULL
    )
    """

# 提交执行
cursor.execute(sql)

# 2. 往表中插入数据
sql = "INSERT INTO Employee (id, name) VALUES (%s, %s)"
values = [(1, 'XiaoBai'),
          (2, 'XiaoHei'),
          (3, 'XiaoHong'),
          (4, 'XiaoMei'),
          (5, 'XiaoLi')]

try:
		# 通过executemany可以插入多条数据
    cursor.executemany(sql, values)
    # 提交事务
    connection.commit()
except:
    connection.rollback()


# 3. 关闭光标及连接
cursor.close()
connection.close()

• 示例2

  在示例1的基础上，我们继续执行查询工作。

import pymysql

# 以admin身份连接到数据库shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

with connection:
    # 创建游标
    cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

    # 1. 通过fetchone只查询一条
    cursor.execute("SHOW CREATE TABLE Employee")
    result = cursor.fetchone()
    print(f'查询结果1： \n{
     result}')

    # 2. 通过fetchmany查询size条
    cursor.execute("DESC Employee")
    result = cursor.fetchmany(size=2)
    print(f'查询结果2： \n{
     result}')

    # 3. 通过fetchall查询所有
    cursor.execute("SELECT * FROM Employee")
    result = cursor.fetchall()
    print(f'查询结果3： \n{
     result}')

    # 4. 通过scroll回滚到第0条进行查询
    cursor.scroll(0, mode='absolute')
    result = cursor.fetchone()
    print(f'查询结果4： \n{
     result}')
		
    # 5. 通过scroll跳过2条进行查询
    cursor.scroll(2, mode='relative')
    result = cursor.fetchone()
    print(f'查询结果5： \n{
     result}')

    cursor.close()

​ 控制台打印结果如下：

查询结果1： 
{
   'Table': 'Employee', 'Create Table': 'CREATE TABLE `Employee` (\n  `id` int NOT NULL,\n  `name` char(15) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'}
查询结果2： 
[{
   'Field': 'id', 'Type': 'int', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {
   'Field': 'name', 'Type': 'char(15)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''}]
查询结果3： 
[{
   'id': 1, 'name': 'XiaoBai'}, {
   'id': 2, 'name': 'XiaoHei'}, {
   'id': 3, 'name': 'XiaoHong'}, {
   'id': 4, 'name': 'XiaoMei'}, {
   'id': 5, 'name': 'XiaoLi'}]
查询结果4： 
{
   'id': 1, 'name': 'XiaoBai'}
查询结果5： 
{
   'id': 4, 'name': 'XiaoMei'}

• 示例3：

  ​ 该示例将演示SQL注入的问题。先建立一个表并插入数据：

import pymysql

# 以admin身份连接到数据库shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

# 创建游标
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

sql = """
        CREATE TABLE UserInfo(
          id INT PRIMARY KEY,
          name VARCHAR(15),
          password CHAR(15) NOT NULL
          )
    """

cursor.execute(sql)

sql = "INSERT INTO UserInfo (id, name, password) VALUES (%s, %s, %s)"
values = [(1, 'XiaoBai', '123'),
          (2, 'XiaoHei', '234'),
          (3, 'XiaoHong', '567'),
          (4, 'XiaoMei',