数据库day06

import openpyxl
import pymysql
from openpyxl.cell import Cell
from openpyxl.styles import Alignment, Font
from pymysql.cursors import Cursor


# 将数据写入excel文件中
"""
工资簿 - workbook - 一个excel文件
工作表 - sheet
单元格 - cell - 行和列交汇的位置，保存数据
"""



wb = openpyxl.Workbook()
# 获取默认的工作表
sheet = wb.active
# 创建一个新的工作表
# sheet = wb.create_sheet('aaa')
# sheet['C4'] = 'hello, world'
# 将列表或元组作为一行写入表格中
# sheet.append(('部门编号', '部门名称', '部门所在地'))
# sheet.append((10, '研发1部', '北京'))
# sheet.append((20, '运营部', '上海'))

sheet.append(('员工', '姓名', '职位', '月薪', '补贴', '部门名称', '所在地'))
# 设置行高
sheet.row_dimensions[1].height = 24
col_width = [12, 16, 16, 12, 12, 16, 18]
# 设置列宽
for idx, col_index in enumerate('ABCDEFG'):
    sheet.column_dimensions[col_index].width = col_width[idx]
# 对齐方式和字体
alignment = Alignment(horizontal='center')

font = Font(color='0000ff', size=18, name='FZJKJW--GB1-0')
# 在单元格上指定对齐方式和字体
for cell in sheet[1]:    # type: Cell
    cell.font = font
    cell.alignment = alignment

# 1. 创建连接对象
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='123456',
                       database='hrs', charset='utf8mb4')
# print(conn)

try:
    # 2. 获取游标对象
    with conn.cursor() as cursor:    # type: Cursor
        # 3. 通过游标对象执行SQL语句
        affected_rows = cursor.execute(
            'select ename, job, mgr, sal, comm, dname, dloc dno from tb_emp natural join tb_dept'
            )
        # 4. 通过游标对象抓取数据
        while data := cursor.fetchone():
            # print(data)
            sheet.append(data)
        wb.save('人力资源数据.xlsx')

except pymysql.MySQLError as err:
    print(err)

finally:
    # 5. 关闭连接
    conn.close()

create table `tb_sales`
(
`id` bigint unsigned auto_increment comment '编号',
`sales_date` date not null comment '销售日期',
`city` varchar(20) not null comment '城市',
`category` varchar(20) not null comment '产品类型',
`salesman` varchar(20) not null comment '销售员',
`quantity` integer not null comment '数量',
`price` decimal(10,2) not null comment '单价',
primary key (`id`)
) engine=innodb comment '销售数据表';

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet

# 加载工作簿
wb = openpyxl.load_workbook('口罩销售数据.xlsx', data_only=True)
# 获取工作表
sheet = wb['销售数据']  # type: Worksheet
# 通过对行和列的循环，获取单元格数据
for row in range(3, sheet.max_row + 1):
    for col in range(1, sheet.max_column):
        cell = sheet.cell(row, col)
        if cell.data_type == 'd':
            value = cell.value.strftime('%Y-%m-%d')
        else:
            value = cell.value
        print(value, end='t')
    print()

create table `tb_top_movie`
(
`mov_id` bigint unsigned auto_increment comment '编号',
`mov_title` varchar(200) not null comment '标题',
`mov_rating_num` decimal(3,1) not null comment '评分',
`mov_comments_count` bigint not null comment '评论数',
primary key (`mov_id`)
) engine=innodb auto_increment=1001 comment '电影数据表';

00 - Bad request.
# 401 - Unauthorized.
# 403 - Forbidden.
# 404 - Not Found.
# 405 - Method not allowed.
# 418 - I am a teapot.

import bs4
import pymysql
import requests
from pymysql.cursors import Cursor


def fetch_page(session, url):
    """抓取页面
    :param session: Session对象
    :param url: 统一资源定位符
    :return: 页面的HTML代码
    """
    resp = session.get(url=url)
    return resp.text if resp.status_code == 200 else ''


def parse_page(html_code):
    """解析页面
    :param html_code: 页面的HTML代码
    :return: 从页面解析出来的数据
    """
    soup = bs4.BeautifulSoup(html_code, 'html.parser')
    movie_items_list = soup.select('#content > div > div.article > ol > li')
    data = []
    for movie_item in movie_items_list:
        title = movie_item.select_one('div > div.info > div.hd > a > span.title').text
        rating_num = movie_item.select_one('div > div.info > div.bd > div > span.rating_num').text
        comments_count = movie_item.select_one('div > div.info > div.bd > div > span:nth-child(4)').text[:-3]
        data.append((title, rating_num, comments_count))
    return data


def save_to_db(conn, data):
    """将数据保存到数据库
    :param conn: 数据库连接
    :param data: 数据
    """
    with conn.cursor() as cursor:  # type: Cursor
        cursor.executemany(
            'insert into tb_top_movie (mov_title, mov_rating_num, mov_comments_count) '
            'values (%s, %s, %s)',
            data
        )
    conn.commit()


def main():
    session = requests.Session()
    session.headers = {'User-Agent': 'Baiduspider'}
    conn = pymysql.connect(host='localhost', port=3306,
                           user='guest', password='Guest.618',
                           database='hrs', charset='utf8mb4')
    try:
        for page in range(10):
            url = f'https://movie.douban.com/top250?start={25 * page}'
            html_code = fetch_page(session, url)
            data = parse_page(html_code)
            save_to_db(conn, data)
    finally:
        conn.close()


if __name__ == '__main__':
    main()

1）生成依赖文件：

pip freeze > requirements.txt

2）安装依赖文件里的包：

pip install -r requirements.txt