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部门。

编程思路：

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

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()