MySQL与Python交互

主要内容：MySQL-Connector模板：数据库连接池、预编译SQL、CRUD操作、事务管理、异常处理
1、创建连接：

import mysql.connector
con = mysql.connector.connect(
    host="****",port="****",
    user="****",password="****",
    database="****"
)

import mysql.connector
config={
    "host":"****",
    "port":****,
    "user":"****",
    "password":"****",
    "database":"****"
}
con=mysql.connector.connect(**config)  # 创建连接

2、游标（Cursor）
MySQL Connector里面的游标用来执行SQL语句，而且查询的结果集也会保存在游标之中cursor = con.cursor() cursor.execute( sql 语句)
3、执行SQL语句

4、事务控制

5、异常的处理

6、数据库连接池
数据库连接池：预先创建一些数据库连接，然后缓存起来，避免了程序语言反复创建和销毁连接昂贵代价

7、循环执行SQL语句
游标对象中的executeMany()函数可以反复执行一条SQL语句

示例1：

import mysql.connector.pooling
config={
    "host":"****",
    "port":****,
    "user":"****",
    "password":"****",
    "database":"****"
}
try:
    pool=mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()  # 获得连接池的连接
    con.start_transaction()     # 开始事务
    cursor=con.cursor()        # 定义游标
    # sql语句
    sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s)"
    data = [
        [100,"A部门","北京"], [110,"B部门","上海"]
    ]
    cursor.executemany(sql,data)     # 执行SQL语句
    con.commit()              # 事务的提交
except Exception as e:
    # 回滚
    if "con" in dir():  # 判断连接是否存在
        con.rollback()
    print(e)

示例2：

import mysql.connector.pooling
config={
   "host":"****",
    "port":****,
    "user":"****",
    "password":"****",
    "database":"****"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con=pool.get_connection()
    con.start_transaction()
    cursor=con.cursor()
    sql = "DROP TABLE t_emp_new"
    cursor.execute(sql)
    sql = "CREATE TABLE t_emp_new LIKE 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])
    # for one in cursor:
    #     print(one[0])
    temp = cursor.fetchall()   # fetchall 取多条记录
    # 查询员工并写入到t_emp_new表里面
    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)

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