#!/user/bin/env python# -*- coding: utf-8 -*-import mysql.connector.pooling
config ={"host":"localhost","port":"3306","user":"root","password":"123234","database":"demo"}try:
pool = mysql.connector.pooling.MySQLConnectionPool(**config,
pool_size=10)
con = pool.get_connection()
con.start_transaction()
cursor = con.cursor()# sql = "CREATE TABLE t_emp_new AS (SELECT * FROM t_emp)" # 创建表并将t_emp的数据复制到t_emp_new
sql ="DROP TABLE IF EXISTS t_emp_new"
cursor.execute(sql)
sql ="CREATE TABLE t_emp_new LIKE t_emp"# 只复制t_emp的表结构
cursor.execute(sql)"""
使用INSERT语句,把部门平均底薪超过公司平均底薪的这样部门里的
员工信息导入到t_emp_new表里面,并且让这些员工隶属于sales部门
"""
sql ="SELECT AVG(sal) AS avg FROM t_emp"
cursor.execute(sql)
temp = cursor.fetchone()
avg = temp[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 inrange(len(temp)):
one = temp[index][0]if index <len(temp)-1:
sql +=str(one)+","else:
sql+=str(one)
sql+=")"print(sql)
cursor.execute(sql)
sql ="DELETE FROM t_emp WHERE deptno IN ("for index inrange(len(temp)):
one = temp[index][0]if index <len(temp)-1:
sql+=str(one)+","else:
sql+=str(one)
sql+=")"print(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])
con.commit()except Exception as e:if"con"indir():
con.rollback()print(e)
案例二练习
编写一个INSERT语句向部门表插入两条记录,每条记录都在部门原有最大主键值的基础上+10
sql语句:
DELETEFROM t_emp WHERE deptno IN(40,60);INSERTINTO t_dept
(SELECTMAX(deptno)+10,"A部门","北京"FROM t_dept UNIONSELECTMAX(deptno)+20,"B部门","北京"FROM t_dept);
py语句:
#!/user/bin/env python# -*- coding: utf-8 -*-import mysql.connector.pooling
config ={"host":"localhost","port":"3306","user":"root","password":"123456","database":"demo"}try:
pool = mysql.connector.pooling.MySQLConnectionPool(**config,
pool_size=10)
con = pool.get_connection()
con.start_transaction()
cursor = con.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部门","上海"))
con.commit()except Exception as e:if"con"indir():
con.rollback()print(e)