MySQL操作（python,excel）

• 用代码向数据库添加数据

import pymysql
# 向关系型数据库的二维表中插入数据
no = int(input('部门编号：'))
name = input('部门名称：')
location = input('部门所在地：')

# 第一步：建立数据库连接
# host --->主机 - 确定要连接哪一台服务器（IP地址或主机名）
# port --->端口 - 确定连接服务器上的哪个服务 （端口是用来区分不同的服务的）
# user 和 password ---> 用户名和口令（提示：一般不能用超级管理员账号）
# database ---> 连接的数据库
# charset ---> 使用的字符编码方式
conn = pymysql.connect(
	host='10.7.174.56', port=3306, user='guest', password='Guest.618', database='hrs', charset='utf8mb4'
)

try:
    # 第二步：获得游标对象
    with conn.cursor() as cursor:
        # 第三步：通过游标向数据库服务器发出SQL语句，获取执行结果
        affected_rows = cursor.execute(
        	'insert into tb_dept (dno, dname, dloc) 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()

• 删除数据库的数据

import pymysql

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

# 第一步：创建连接
conn = pymysql.connect(host='10.7.174.103', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 第二步：获得游标对象
    with conn.cursor() as cursor:
        # 第三步：通过游标向数据库服务器发出SQL语句，获取执行结果
        affected_rows = cursor.execute(
            'delete from tb_dept where dno=%s',
            (no, )
        )
        if affected_rows == 1:
            print('删除部门成功！')
    # 第四步：提交（操作成功）
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 第四步：回滚（操作失败）
    conn.rollback()
finally:
    # 第五步：关闭连接（释放资源）
    conn.close()

• 更新数据库数据

import pymysql

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

# 第一步：创建连接
conn = pymysql.connect(host='10.7.174.103', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 第二步：获得游标对象
    with conn.cursor() as cursor:
        # 第三步：通过游标向数据库服务器发出SQL语句，获取执行结果
        affected_rows = cursor.execute(
            'update tb_dept set dname=%s, dloc=%s where dno=%s',
            (name, location, no)
        )
        if affected_rows == 1:
            print('更新部门信息成功！')
    # 第四步：提交（操作成功）
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 第四步：回滚（操作失败）
    conn.rollback()
finally:
    # 第五步：关闭连接（释放资源）
    conn.close()
    

• 把数据库的数据以字典的方式返回

import pymysql

"""
修改数据
"""
# 第一步：创建链接
conn = pymysql.connect(host='10.7.174.56', port=3306, user='guest', password='Guest.618', database='hrs',
                       charset='utf8mb4')

try:
    # 第二步：获得游标对象
    with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
        # 第三步：通过游标执行SQL
        cursor.execute(
            'select dno as no, dname as name, dloc as location from tb_dept'
        )
        # 第四步：通过游标抓取数据（查询结果）
        # fetchone / fetchmany / fetchall
        # 提示：如果查询数据体量很大，那么最好不使用fetchall，
        # 否则程序可能因为内存不足而崩溃，而且加载数据的时间也会特别长
        for row in iter(lambda: cursor.fetchone(), None):
            print(row)
# except指定的异常类型是父类型，那么可以捕获到子类型的异常
# 因为异常捕获需要遵循面向对象编程里氏替换原则（LSP）
# 任何时候都可以用子类型替换掉父类型，父类型未必能替换子类型
except pymysql.MySQLError as err:
    print(err)
finally:
    # 第五步
    conn.close()

• 从数据库中读取员工的编号、姓名、职位、月薪和部门名称，写入excel文件

import pymysql
import openpyxl

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

try:
        with conn.cursor() as cursor:
        cursor.execute(
            'select eno, ename, job, sal, dname from tb_emp t1 inner join tb_dept t2 on t1.dno=t2.dno'
        )
        wb = openpyxl.Workbook()
        # ws = wb.create_sheet('员工表')
        ws = wb.active
        titles = ('工号', '姓名', '职位', '月薪', '部门')
        for col_idx, col_name in enumerate(titles):
            ws.cell(1, col_idx + 1, col_name)
        for row_idx, emp_row in enumerate(cursor.fetchall()):
            for col_idx, col_value in enumerate(emp_row):
                ws.cell(row_idx+2, col_idx+1, col_value)
        wb.save('人力资源管理.xlsx')
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()

• 从excel文件中读取数据写入数据库

import pymysql
import openpyxl

wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
ws = wb.active
params = []
for row_idx in range(2, ws.max_row + 1):
    values = []
    for col_idx in range(1, ws.max_column):
        values.append(ws.cell(row_idx, col_idx).value)
    params.append(values)

conn = pymysql.connect(host='10.7.174.56', port=3306, user='guest', password='Guest.618', database='stock',
                       charset='utf8mb4')

try:
    with conn.cursor() as cursor:
        # 执行批量插入操作
        cursor.executemany(
            'insert into tb_baba_stock '
            '   (trade_date, high_price, low_price, open_price, close_price, trade_volume) '
            'values '
            '   (%s, %s, %s, %s, %s, %s)',
            params
        )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()