18-Python中的Mysql操作

注：17的linux服务器和mysql环境安装跳过
推荐使用pymysql包来进行数据库操作，首先是安装。

pip install pymysql

使用pymysql操作数据库分为以下步骤：

1. 创建连接。通过pymysql.connect函数进行连接。需要指定的参数见例子1。
2. 获取游标。
3. 发出SQL。
4. 执行增删改的话，需要根据实际情况进行提交或者回滚。
5. 关闭连接。通常在finally中使用close来关闭。

例子1：插入数据

import pymysql

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

# 1.创建连接
conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4")
try:  
    # 2.获取游标对象
    with conn.cursor() as cursor:
        # 3.通过游标对象对数据库服务器发出sql语句
        affected_rows = cursor.execute(
            f"insert into `tb_dept` values (%s,%s,%s)",
            (no, name, location)
        )
        if affected_rows == 1:
            print("新增部门成功")
    # 4.提交
    conn.commit()
except pymysql.MySQLError as err:
    # 4.回滚
    conn.rollback()
    print(type(err), err)
finally:
    # 5.关闭连接
    conn.close()

注意：尽量不要使用root用户连接数据库，这样很危险。比如本例子中，新建了一个用户hellokitty，他只有hrs的权限。

插入多组数据可以尝试如下语句：

with conn.cursor() as cursor:
        affected_rows = cursor.executemany(
            'insert into `tb_dept` values (%s, %s, %s)',
            [(no, name, location),
             (no1, name1, location1),
             (no2, name2, location2)]
        )
        if affected_rows == 3:
            print('新增部门成功!!!')
    conn.commit()

例子2：删除数据

import pymysql

no = int(input("请输入部门编号："))

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4",
                       autocommit=True)
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            "delete from `tb_dept` where `dno`=%s",
            (no,)
        )
        if affected_rows == 1:
            print("删除部门成功")
finally:
    conn.close()

该例子中指定了一个参数：autocommit=True，这样SQL代码就会自动提交。实际环境中不建议这样做。

例子3：更新数据

import pymysql

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

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4")
try:
    with conn.cursor() as cursor:
        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:
    conn.rollback()
    print(type(err), err)
finally:
    conn.close()

例子4：查询数据

import pymysql

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4")
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            "select `dno`,`dname`,`dloc` from `tb_dept`")
        row = cursor.fetchone()
        while row:
            print(row)
            row = cursor.fetchone()
except pymysql.MySQLError as err:
    conn.rollback()
    print(type(err), err)
finally:
    conn.close()

本例子中，使用while循环每次用fetchone获取一条数据然后打印。也提供了fetchall方法可以获取到所有的结果，但是不推荐这样做，因为在实际环境中这样对内存的压力很大。

默认拿到的是元组，如果希望拿到列表，需要指定cursorclass=DictCursor

import pymysql
from pymysql.cursors import DictCursor

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4",
                       cursorclass=DictCursor)
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            "select `dno`,`dname`,`dloc` from `tb_dept`")
        rows = cursor.fetchall()
        for row in rows:
            print(row)
except pymysql.MySQLError as err:
    conn.rollback()
    print(type(err), err)
finally:
    conn.close()

例子5：分页查询

import pymysql

page = int(input("页码:"))
size = int(input("大小:"))

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4")
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'select `eno`, `ename`, `job`, `sal` from `tb_emp` order by `sal` desc limit %s,%s',
            ((page - 1) * size, size)
        )
        for emp_dict in cursor.fetchall():
            print(emp_dict)

except pymysql.MySQLError as err:
    print(type(err), err)
finally:
    conn.close()

案例：将数据库数据导出到excel表

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

conn = pymysql.connect(host="192.168.32.11", port=3306,
                       user="hellokitty", password="123123",
                       database="hrs", charset="utf8mb4")
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'select `eno`, `ename`, `job`, `sal` from `tb_emp`'
        )
        wb = openpyxl.Workbook()  # type:Workbook
        sheet = wb.active  # type: Worksheet
        sheet.cell(1, 1, "员工ID")
        sheet.cell(1, 2, "员工姓名")
        sheet.cell(1, 3, "员工薪水")
        row_index = 2
        row = cursor.fetchone()
        while row:
            for col_index,value in enumerate(row):
                sheet.cell(row_index, col_index + 1, value)
            row = cursor.fetchone()
            row_index += 1

        wb.save("output/tb_emp.xlsx")

except pymysql.MySQLError as err:
    print(type(err), err)
finally:
    conn.close()