数据库day05

1. 连接MySQL数据库插入数据

经验：一定不能够使用字符串拼接或者格式化等方式组装动态的SQL，否则就会直面SQL注射攻击。

import pymysql

from pymysql.cursors import Cursor

no = int(input('编号: '))
name = input('名称: ')
location = input('所在地: ')

# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. 获取游标对象
    with conn.cursor() as cursor:  # type: Cursor
        # 3. 通过游标对象执行SQL语句
        affected_rows = cursor.execute(
            'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',
            (no, name, location)
        )
        if affected_rows == 1:
            print('添加部门成功！！！')
    # 4. 手动提交（让之前的操作生效）
    conn.commit()
except pymysql.MySQLError as err:
    # 4. 手动回滚（撤销之前做的操作）
    conn.rollback()
    print(err)
finally:
    # 5. 关闭连接
    conn.close()

2. 删除数据

import pymysql
from pymysql.cursors import Cursor

no = int(input('编号: '))

# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. 获取游标对象
    with conn.cursor() as cursor:  # type: Cursor
        # 3. 通过游标对象执行SQL语句
        affected_rows = cursor.execute(
            'delete from tb_dept where dno = %s',
            (no, )
        )
        if affected_rows == 1:
            print('删除部门成功！！！')
    # 4. 手动提交（让之前的操作生效）
    conn.commit()
except pymysql.MySQLError as err:
    # 4. 手动回滚（撤销之前做的操作）
    conn.rollback()
    print(err)
finally:
    # 5. 关闭连接
    conn.close()

3. 更新数据

import pymysql
from pymysql.cursors import Cursor

no = int(input('编号: '))
name = input('名称: ')
location = input('所在地: ')

# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. 获取游标对象
    with conn.cursor() as cursor:  # type: Cursor
        # 3. 通过游标对象执行SQL语句
        affected_rows = cursor.execute(
            'update tb_dept set dname = %s, dloc = %s where dno = %s',
            (name, location, no)
        )
        if affected_rows == 1:
            print('更新部门成功！！！')
    # 4. 手动提交（让之前的操作生效）
    conn.commit()
except pymysql.MySQLError as err:
    # 4. 手动回滚（撤销之前做的操作）
    conn.rollback()
    raise err
finally:
    # 5. 关闭连接
    conn.close()

4. 查询数据

表 - 关系（relation）
行 - 记录 / 元组（tuple）
列 - 字段 / 属性（attribute）
行数 - 势（cardinality）
列数 - 度（degree）

import pymysql
from pymysql.cursors import Cursor, DictCursor

# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. 获取游标对象
    with conn.cursor(DictCursor) as cursor:  # type: Cursor
        # 3. 通过游标对象执行SQL语句
        cursor.execute(
            'select dno as no, dname as name, dloc as location from tb_dept'
        )
        # 4. 通过游标对象抓取数据
        while data := cursor.fetchone():
            print(data)
except pymysql.MySQLError as err:
    print(err)
finally:
    # 5. 关闭连接
    conn.close()

5. 把员工表数据写入CSV文件

import csv
import pymysql
from pymysql.cursors import Cursor


file = open('emp.csv', 'w', encoding='utf-8')
writer = csv.writer(file)
writer.writerow(('eno', 'ename', 'job', 'mgr', 'sal', 'comm', 'dno'))
# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. 获取游标对象
    with conn.cursor() as cursor:  # type: Cursor
        # 3. 通过游标对象执行SQL语句
        cursor.execute(
            'select eno, ename, job, mgr, sal, comm, dno from tb_emp'
        )
        # 4. 通过游标对象抓取数据
        while data := cursor.fetchone():
            writer.writerow(data)
except pymysql.MySQLError as err:
    print(err)
finally:
    file.close()
    conn.close()

6. 将CSV文件的数据读到MySQL的表中

create table `tb_score`
(
`id` bigint unsigned not null comment '编号',
`name` varchar(10) not null comment '姓名',
`birthday` date not null comment '出生日期',
`company` varchar(200) not null comment '公司名称',
`score` decimal(5,2) not null comment '落户积分',
primary key (id)
) engine=innodb comment '落户积分表';

-- utf-8-sig ---> 带字节序签名的utf-8（utf-8 with BOM）

import csv
import pymysql


file = open('2018年北京积分落户数据.csv', 'r', encoding='utf-8-sig')
reader = csv.reader(file)
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    next(reader)
    with conn.cursor() as cursor:
        params = []
        for row in reader:
            row[2] += '-01'
            params.append(row)
        cursor.executemany(
            'insert into tb_score values (%s, %s, %s, %s, %s)',
            params
        )
    conn.commit()
finally:
    file.close()
    conn.close()

7. 查找算法

算法时间复杂度表示 - Big O Natation
# 
# 需求：从列表中查找指定的元素，找到了返回下标，找不到返回-1
# 场景1：列表中的元素是无序的
# 场景2：列表中的元素是有序的
# 
# 冒泡排序 - 相邻元素两两比较，前面比后面的大就交换元素
# 35  92  12  67  55  87  43
# 35  12  67  55  87  43  92
# 12  35  55  67  43  87
# 12  35  55  43  67
# 12  35  43  55
# 12  35  43
# 12  35

# 渐近时间复杂度 - O(N) - 线性时间复杂度
import random


def seq_search(items: list, key, pos=0) -> int:
    """顺序查找
    :param items: 无序的列表
    :param key: 待查找的元素
    :param pos: 查找的起始位置
    :return: 找到了返回索引（下标），找不到返回-1
    """
    for index, item in enumerate(items[pos:]):
        if item == key:
            return index
    return -1


# 渐近时间复杂度 - O(log2N) - 对数时间复杂度
def bin_search(items: list, key) -> int:
    """折半查找（二分查找）
    :param items: 有序的列表
    :param key: 待查找的元素
    :return: 找到了返回索引（下标），找不到返回-1
    """
    start, end = 0, len(items) - 1
    while start <= end:
        mid = (start + end) // 2
        if key > items[mid]:
            start = mid + 1
        elif key < items[mid]:
            end = mid - 1
        else:
            return mid
    return -1


# 渐近时间复杂度 - O(N^2) - 平方时间复杂度
# 1. 函数的无副作用设计
# 2. 针对特殊情况的优化 - [9, 2, 3, 4, 5, 6, 7, 8, 1]
# 3. 通过高阶函数实现跟 > 运算符的解耦合
def bubble_sort(items: list, *, gt=lambda x, y: x > y):
    items, n = items[:], len(items)
    for i in range(1, n):
        swapped = False
        for j in range(0, n - i):
            if gt(items[j], items[j + 1]):
                items[j], items[j + 1] = items[j + 1], items[j]
                swapped = True
        if not swapped:
            break
    return items


class Person:

    def __init__(self, name, age):
        self.name = name
        self.age = age

    def __repr__(self):
        return f'{self.name}: {self.age}'


def main():
    nums = [35, 92, 12, 67, 55, 87, 43]
    sorted_nums = bubble_sort(nums)
    print(nums)
    print(sorted_nums)

    persons = [Person('Lee', 28), Person('Luo', 42), Person('An', 39), Person('Zhang', 100)]
    sorted_persons = bubble_sort(persons, gt=lambda o1, o2: o1.age > o2.age)
    print(persons)
    print(sorted_persons)


if __name__ == '__main__':
    main()

8. 斐波拉切数列

思路：空间换时间
# 
# f(n) = f(n - 1) + f(n - 2)
# 
# 1 1 2 3 5 8 13 21 34 55 ……

from functools import lru_cache


# 评估该函数的渐近时间复杂度
# O(2^N) ---> O(N)
@lru_cache()
def fib(n):
    if n in (1, 2):
        return 1
    return fib(n - 1) + fib(n - 2)


for i in range(1, 121):
    print(i, fib(i))