python接入MySQL

一、python接入MySQL数据库实现insert操作

import pymysql

no = int(input('部门编号：'))
name = input('部门名称：')
location = input('部门所在地：')
# 注意一定不要使用字符串拼接或格式化的方式来动态生成sql语句，如果这么做了，就会导致数据库有sql注射攻击的风险

# 创建连接
conn = pymysql.connect(host='localhost', port=3306,
                       user='guset', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 获取游标对象
    cursor = conn.cursor()
    # 通过游标对象向数据库发出sql
    affected_rows = cursor.execute('insert into tb_dept values (%s, %s, %s)',
                                   (no, name, location))
    if affected_rows == 1:
        print('新增部门成功！')
    # 事务提交
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 事务回滚
    conn.rollback()
finally:
    # 关闭连接释放资源
    conn.close()

二、python接入MySQL数据库实现select操作

import pymysql

# 创建连接
conn = pymysql.connect(host='localhost', port=3306,
                       user='guset', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 获取游标对象
    cursor = conn.cursor()
    # 通过游标对象向数据库发出sql
    affected_rows = cursor.execute('select dno, dname, dloc from tb_dept')
    # 通过游标对象抓取数据
    while row := cursor.fetchone():
        print(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    # 关闭连接释放资源
    conn.close()

三、数据持久化到MySQL

import pymysql
import requests
from bs4 import BeautifulSoup
from pymysql.connections import Connection


def fetch_page(url):
    """
    抓取页面
    :param url: 网页地址
    :return: 网页的HTML代码
    """
    Headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36 Edg/112.0.1722.34'
    }
    response = requests.get(url=url, headers=Headers)
    return response


def parse_page(response):
    """
    解析页面
    :param response: 网页源码
    :return: 解析到的数据
    """
    soup = BeautifulSoup(response.text, 'html.parser')
    lilist = soup.select('html > body > div#wrapper ol > li')
    fetched_data = []
    for i in lilist:
        # 电影标题
        movieTitle = i.select('li > div.item > div.info > div.hd > a > span.title')
        movieList = [i.text for i in movieTitle]
        movieTitle = ''.join(movieList).replace(' ', ' ')
        # 电影评分
        movieScore = i.select_one('li div.bd > div.star > span.rating_num').text
        # 电影评论人数
        commPeople = i.select_one('li div.bd > div.star > span:nth-child(4)').text[:-3]
        # 中心思想
        middIdea = i.select_one('li div.bd > p.quote > span.inq')
        middIdea = middIdea.text if middIdea != None else ''
        fetched_data.append((movieTitle, movieScore, commPeople, middIdea))
    return fetched_data


def write_to_db(conn: Connection, data):
    """
    将数据写入数据库二维表
    :param conn: 数据库连接对象
    :param data: 保存数据的列表
    """
    try:
        with conn.cursor() as cursor:
            cursor.executemany(
                'insert into tb_movie '
                '   (mov_title, mov_rank, mov_comments_count, mov_gist) '
                'values '
                '   (%s, %s, %s, %s)',
                data
            )
            conn.commit()
    except pymysql.MySQLError as err:
        conn.rollback()
        raise err


def main():
    conn = pymysql.connect(host='localhost', port=3306,
                           user='guset', password='Guest.618',
                           database='hrs', charset='utf8mb4')
    try:
        for page in range(10):
            response = fetch_page(f'https://movie.douban.com/top250?start={page * 25}')
            data = parse_page(response)
            write_to_db(conn, data)
    finally:
        conn.close()


if __name__ == '__main__':
    main()

四、将二维表的数据导出到excel文件

import openpyxl
import pymysql
from openpyxl.worksheet.worksheet import Worksheet

# 创建excel工作簿
wb = openpyxl.Workbook()
# 获取默认的工作表
sheet = wb.active # type:Worksheet
# 修改工作表的名字
sheet.title = '员工信息表'
# 添加表头
sheet.append(('编号', '姓名', '职位', '月薪', '部门', '部门所在地'))

# 创建连接
conn = pymysql.connect(host='localhost', port=3306,
                       user='guset', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 获取游标对象
    with conn.cursor() as cursor:
        # 通过游标对象向数据库发出sql
        cursor.execute('select eno, ename, job, sal, dname, dloc from tb_emp natural join tb_dept')
        # 通过游标对象抓取数据
        while row := cursor.fetchone():
            print(row)
            # 将元组直接作为行写入excel工作表
            sheet.append(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    # 关闭连接释放资源
    conn.close()
    # 保存工作簿
    wb.save('人力资源管理.xlsx')

五、从excel工作表读取数据写入数据库二维表中

import openpyxl
import pymysql
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

conn = pymysql.connect(host='localhost', port=3306,
                           user='guset', password='Guest.618',
                           database='hrs', charset='utf8mb4')

# 读文件加载工作簿对象
wb = openpyxl.load_workbook('人力资源管理.xlsx')  # type:Workbook
sheet = wb['员工信息表']  # type:Worksheet
# 注意：openpyxl操作行和列的时候索引从1开始
for i in range(2, sheet.max_row + 1):
    data = []
    for j in range(1, sheet.max_column + 1):
        # 通过行和列获取对应的单元格
        cell = sheet.cell(i, j)
        data.append(cell.value)
    print(data)
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                'insert into tb_emp_dept '
                '   (eno, ename, job, sal, dname, dloc) '
                'values '
                '   (%s, %s, %s, %s, %s, %s)',
                data
            )
            conn.commit()
    except pymysql.MySQLError as err:
        conn.rollback()
        raise err

六、作业

1.建表语句

create table tb_sales
(
    order_id int not null auto_increment,
    date date not null,
    city varchar(5) not null,
    channl varchar(5) not null,
    sal_order varchar(255) not null,
    brand varchar(10) not null,
    price int not null,
    quantity int not null,
    primary key (order_id)
);

2.python代码

import openpyxl
import pymysql
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

conn = pymysql.connect(host='localhost', port=3306,
                           user='guset', password='Guest.618',
                           database='hrs', charset='utf8mb4')

# 读文件加载工作簿对象
wb = openpyxl.load_workbook('2020年销售数据.xlsx')  # type:Workbook
sheet = wb['data']  # type:Worksheet
# 注意：openpyxl操作行和列的时候索引从1开始
for i in range(2, sheet.max_row + 1):
    data = []
    for j in range(1, sheet.max_column + 1):
        # 通过行和列获取对应的单元格
        cell = sheet.cell(i, j)
        data.append(cell.value)
    print(data)
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                'insert into tb_sales '
                '   (date, city, channl, sal_order, brand, price, quantity) '
                'values '
                '   (%s, %s, %s, %s, %s, %s, %s)',
                data
            )
            conn.commit()
    except pymysql.MySQLError as err:
        conn.rollback()
        raise err