二阶段day5

python连接数据库进行增删查改

添加表数据

import pymysql
no = input("部门编号:")
name = input("部门名称:")
location = input("部门所在地:")

# 1.创建连接--->connection
conn = pymysql.connect(host='ip或localhost', port=3306, user='yangjiao_copy'
                       , password='xxxxx', database='hrs', charset='utf8mb4')
# print(conn)
try:
    # 2. 获取游标对象（Cursor）
    with conn.cursor() as cursor:
        # 3.向数据库发出sql语句
        # %s安全占位符,无论什么类型
        a_row = cursor.execute(
            'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s)',
            (no,name,location)
        )
        if a_row ==1:
            print('添加成功')
    # 4.提交事务
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    #5.回滚
    conn.rollback()
finally:
    conn.close()

删除表数据

# 删除数据
conn = pymysql.connect(host='ip', port=3306, user='yangjiao_copy'
                       , password='xxxxxx', database='hrs', charset='utf8mb4')
# print(conn)
no_d = input('删除部门编号：')
try:
    # 2. 获取游标对象（Cursor）
    with conn.cursor() as cursor:
        # 3.向数据库发出sql语句
        # %s安全占位符,无论什么类型
        a_row = cursor.execute(
            'delete from tb_dept where `dno`=%s',
            (no_d,)
        )
        if a_row == 1:
            print('删除成功')
    # 4.提交事务
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 5.回滚
    conn.rollback()
finally:
    conn.close()

当删除的部门出现如下提示：(1217, ‘Cannot delete or update a parent row: a foreign key constraint fails’)

其原因是因为，在创建该表时references后默认是restrict，做了外键约束，不能删除，但如果是cascade的话就可以删除和更新，同时与之相关联的数据页删除和修改

更新数据：

conn = pymysql.connect(host='ip', port=3306, user='yangjiao_copy'
                       , password='xxxxx', database='hrs', charset='utf8mb4')
# print(conn)
no = input("部门编号:")
name = input("部门名称:")
location = input("部门所在地:")
try:
    # 2. 获取游标对象（Cursor）
    with conn.cursor() as cursor:
        # 3.向数据库发出sql语句
        # %s安全占位符,无论什么类型
        a_row = cursor.execute(
            # 'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s)',
            # (no, name, location)
            'update tb_dept set dloc = %s,dname = %s where dno = %s',
            (location,name,no)
        )
        if a_row == 1:
            print('更新成功')
    # 4.提交事务
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 5.回滚
    conn.rollback()
finally:
    conn.close()

查询数据：

from pymysql.cursors import Cursor
conn = pymysql.connect(host='ip',password='xxxxx6'
                       ,port=3306,user='yangjiao_copy'
                       ,database = 'hrs',charset='utf8mb4')
try:
   # ymysql.cursors.DictCursor使得查询出来的数据是字典
    with conn.cursor(ymysql.cursors.DictCursor) as cursor:  #type:pymysql
        cursor.execute(
            'select dno,dname,dloc from tb_dept'
        )
        # 通过游标抓取数据
        print(cursor.fetchall())
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()

显示如下：

将mysql数据写入Excel

import pymysql
import openpyxl
from openpyxl.worksheet.worksheet import Worksheet #加上有代码提示
from pymysql.cursors import Cursor #加上有代码提示
'''
# 创建工作部
wb = openpyxl.Workbook()
# 获取默认的工作表
sheet = wb.active #type: Worksheet
# 给默认表改名字
sheet.title = '员工'

# 创建新工作表
# sheet = wb.create_sheet('员工')

# 添加表头
sheet.append(('编号','姓名','职位','月薪','补贴','所在部门'))

conn = pymysql.connect(host='ip',password='ccccc'
                       ,port=3306,user='yangjiao_copy'
                       ,database = 'hrs',charset='utf8mb4')
try:
    with conn.cursor() as cursor:  #type:pymysql
        cursor.execute(
            'select eno,ename,job,sal,comm,dname'
            ' from tb_emp natural join tb_dept'
        )
        while row := cursor.fetchone():
            sheet.append(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    wb.save('员工信息表.xlsx')
    conn.close()

将Excel写入mysql

import openpyxl
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.workbook import Workbook
import pymysql
from pymysql.cursors import Cursor

conn = pymysql.connect(host='ip地址或者localhost', password='xxxx'
                           , port=3306, user='yangjiao_copy'
                           , database='hrs', charset='utf8mb4')
wb = openpyxl.load_workbook('人力资源信息表.xlsx') #type:Workbook

sheet1 = wb['部门'] #type:Worksheet# 标记不要写成workbook
sheet2 = wb['员工'] #type:Worksheet

rows_iter1 = sheet1.iter_rows()
rows_iter2 = sheet2.iter_rows()
next(rows_iter2)
next(rows_iter1)
try:
    with conn.cursor() as cursor:  # type:pymysql
        # 对表单的头进行迭代
        for row1 in rows_iter1:
            params = []
            for cell in row1:  # type:Cell
                params.append(cell.value)
            cursor.execute(
                'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s) ',
                params
            )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()
to tb_dept (dno,dname,dloc) values (%s,%s,%s) ',
                params
            )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()