Pycharm访问MySQL数据库·下

1.数据库连接池

1.1 什么是连接池

连接池（Connection Pool）是一种用于管理数据库连接的技术。它通过预先创建一组数据库连接，并在需要时将这些连接提供给应用程序，从而避免了频繁地打开和关闭数据库连接的开销。

连接池在代码中的作用：

资源复用：连接池预先创建了一定数量的数据库连接，并存储在池中。当应用程序需要与数据库交互时，它可以从连接池中获取一个已经建立的连接，而不是每次都创建新的连接。这样可以显著减少创建和销毁连接的开销。

1.2 连接池案例

# 构建连接池工具
import mysql.connector.pooling
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "888888",
    "database": "demo"
}
try:
    #创建连接池 pool_size=10：连接池的连接数量
    pool = mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=10)
    #从连接池中获得连接项
    conn = pool.get_connection()
    conn.start_transaction() # 开启事务
    cursor = conn.cursor()
    sql = "update t_emp set sal = %s where ename = %s"
    cursor.execute(sql,(2000,"ZOYA"))
    conn.commit()
except Exception as e:
    print(e) # 打印异常信息，用于分析异常问题
    if "conn" in dir():
        conn.rollback()

2.Connector删除数据

2.1 Truncate table与Delete

截断数据表（TRUNCATE TABLE）是一个SQL命令，用于快速删除数据库表中的所有行。

截断数据表用于整表删除,不能回滚，且不能写where条件。

与DELETE相比，速度远远高于delete,数据量越多效果越明显。

2.2 删除表实例

import mysql.connector.pooling
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "888888",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=10)
    conn = pool.get_connection()
    conn.start_transaction()
    cursor = conn.cursor()
    # sql = "delete from t_emp"
    # 截断数据表
    sql = "truncate table t_emp"
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    print(e) # 打印异常信息，用于分析异常问题
    if "conn" in dir():
        conn.rollback()

3.循环执行SQL语句

import mysql.connector.pooling
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "888888",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=10)
    conn = pool.get_connection()
    conn.start_transaction()
    cursor = conn.cursor()
    # 循环执行sql语句
    sql = "insert into t_dept(deptno,dname,loc)values(%s,%s,%s)"
    data = [[120,"C部门","沈阳"],[130,"D部门","大连"]]
    cursor.executemany(sql,data) # 执行循环操
    conn.commit()
except Exception as e:
    print(e) # 打印异常信息，用于分析异常问题
    if "conn" in dir():
        conn.rollback()

4.综合练习

4.1 练习一

使用INSERT语句，把部门平均底薪超过公司平均底薪的这个部门里的员工信息导入到t_emp_new表里面，并且让这些员工隶属于sales部门。

编程思路：

1. 构建连接池：使用mysql.connector.pooling.MySQLConnectionPool创建一个连接池，配置信息包括主机、端口、用户名、密码和数据库名。
2. 获取连接并开启事务：从连接池中获取一个连接，并开启事务。
3. 删除数据表t_emp_new：为了避免重名，首先删除已存在的t_emp_new表。
4. 创建数据表t_emp_new：创建一个新的表t_emp_new，结构与t_emp表相同。
5. 计算平均底薪：查询t_emp表的平均底薪。
6. 查询大于平均底薪的部门：查询t_emp表中，平均底薪大于或等于上一步计算出的平均底薪的部门编号。
7. 向新表中添加数据：将上一步查询出的部门的数据插入到t_emp_new表中。
8. 删除原表t_emp中的数据：删除t_emp表中大于平均底薪的部门信息。注意：这里有一个小错误，表名应该是t_emp而不是t_emp_。
9. 查询部门编号：查询t_emp表中部门名为SALES的部门编号。
10. 更新表中的部门编号：更新t_emp_new表中的所有记录的部门编号为上一步查询出的SALES部门编号。
11. 异常处理：捕获并打印异常信息，如果发生异常则回滚事务。

import mysql.connector.pooling
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "888888",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=10)
    conn = pool.get_connection()
    conn.start_transaction()
    cursor = conn.cursor()
    # 1.删除数据表t_emp_new,避免重名
    sql = "drop table t_emp_new"
    cursor.execute(sql)
    # 2.创建数据表t_emp_new
    # sql = "create table t_emp_new as (select * from t_emp)"
    sql = "create table t_emp_new like t_emp"
    cursor.execute(sql)
    # 3.完成业务 ①获得平均底薪
    sql = "select avg(sal) as avg from t_emp"
    cursor.execute(sql)
    avg = cursor.fetchone()[0]
    # ②查询大于平均底薪的部门
    sql = "select deptno from t_emp group by deptno having avg(sal) >= %s"
    cursor.execute(sql, [avg])
    temp = cursor.fetchall()
    # ③向新表中添加②中查询出的数据
    sql = "insert into t_emp_new select * from t_emp where deptno in ("
    for index in range(len(temp)):
        one = temp[index][0]
        if index < len(temp) - 1:
            sql += str(one)+ ","
        else:
            sql += str(one)
    sql += ")"
    cursor.execute(sql)
    # ④删除原表t_emp大于平均底薪的部门信息,SQL片段
    sql = "delete from t_emp where deptno in ("
    for index in range(len(temp)):
        one = temp[index][0]
        if index < len(temp) - 1:
            sql += str(one) + ","
        else:
            sql += str(one)
    sql += ")"
    cursor.execute(sql)
    # ⑤查询部门编号
    sql = "select deptno from t_dept where dname=%s"
    cursor.execute(sql, ['SALES'])
    deptno = cursor.fetchone()[0]
    # ⑥更新表中的部门编号
    sql = "update t_emp_new set deptno=%s"
    cursor.execute(sql, [deptno])
    conn.commit()
except Exception as e:
    print(e) # 打印异常信息，用于分析异常问题
    if "conn" in dir():
        conn.rollback()

4.2 练习二

编写一个INSERT语句向部门表插入两条记录，每条记录都在部门原有最大主键值的基础上+10。

import mysql.connector.pooling
config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "888888",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=10)
    conn = pool.get_connection()
    conn.start_transaction()
    cursor = conn.cursor()
    #完成业务
    sql = "insert into t_dept (select max(deptno)+10,%s,%s from t_dept union select max(deptno)+20,%s,%s from t_dept)"
    cursor.execute(sql,("A部门","北京","B部门","上海"))
    conn.commit()
except Exception as e:
    print(e) # 打印异常信息，用于分析异常问题
    if "conn" in dir():
        conn.rollback()