Python-MySQL Connector模块练习

案例一练习

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

#!/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 in range(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 in range(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" in dir():
        con.rollback()
    print(e)

案例二练习

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

DELETE FROM t_emp WHERE deptno IN (40,60);

INSERT INTO t_dept 
(SELECT MAX(deptno)+10,"A部门","北京" FROM t_dept UNION
SELECT MAX(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" in dir():
        con.rollback()
    print(e)