Python与MySQL交互：MySQL Connector模块（七）_import mysql.connector

MySQL Connector模块

MySQL Connector是MySQL官方的驱动模块，兼容性好

[下载地址](MySQL :: Download MySQL Connector/Python (Archived Versions))

命令行直接安装：pip install mysql-connector-python

1 创建连接

方式一：

import mysql.connector

con = mysql.connector.connect(
    host="localhost", port="3306",
    user="root", password="123456",
    database="study"
)
1
2
3
4
5
6
7

方式二：

import mysql.connector

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "vaga"
}
con = mysql.connector.connect(**config)
1
2
3
4
5
6
7
8
9
10

2 游标

MySQL Connector里面的游标（Cursor）用来执行SQL语句，而且查询的结果集也会保存在游标之中。

# 创建游标
# params可以是元组，列表，字典
cursor=con.cursor()
cursor.execute(sql_query, params)
# params_list是一个包含参数值的列表的列表或元组的列表， %s为占位符，防止SQL注入攻击
cursor.executemany(sql_query, params_list)
1
2
3
4
5
6

例1：

import mysql.connector

con = mysql.connector.connect(
    host="localhost", port="3306",
    user="root", password="123456",
    database="study"
)

# 创建游标
cursor=con.cursor()
sql = "select empno, ename, hiredate from t_emp;"
cursor.execute(sql)

for one in cursor:
    print(one[0], one[1], one[2])

con.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

例2：

cursor = con.cursor()
sql = "insert into t_dept(deptno, dname, loc) values (%s, %s, %s);"
data=[
    [100, "A部门", "北京"],
    [110, "B部门", "上海"]
]
cursor.executemany(sql, data)
1
2
3
4
5
6
7

3 预编译SQL

SQL注入攻击： 由于SQL语句是解释型语言，所以在拼接SQL语句的时候，容易被注入恶心的SQL语句。

例如：一旦执行sql该语句就会执行，说白了，是用户输入恶意信息对数据库进行操作。

id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id="+id
1
2

为防止SQL注入攻击可采用预编译机制

预编译机制：

SQL预编译机制抵御注入攻击： SQL语句编译的过程中，关键字 已经被解析过了，所以向编译后的SQL语句传入参数，都被当做字符串处理，数据库不会解析其中注入的SQL语句。

例子：

import mysql.connector

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "vaga"
}
con = mysql.connector.connect(**config)
username="1 OR 1=1"
password="1 OR 1=1"

sql = "select count(*) from t_user where username=%s " \
      " and aes_decrypt(unhex(password), 'HelloWorld')=%s;"

cursor = con.cursor()

# 让sql先编译，然后再传递给它值，防止注入攻击
cursor.execute(sql, (username, password))

print(cursor.fetchone()[0])
con.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4 事务控制

Connector提供了事务控制函数

开启事务： con.start_transaction([事务的隔离级别])

提交事务：con.commit()

回滚事务：con.rollback()

其中con为数据库连接对象

5 异常处理

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost",
        port=3306,
        user="root",
        password="123456",
        database="study"
    )
    cursor = con.cursor()
    sql = "insert into t_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)" \
          "values (%s, %s, %s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (9600, "赵娜", "SALESMAN", None, "1985-12-1", 2500, None, 10))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
finally:
    # 检查当前命名空间中是否存在名为 con 的变量或对象
    if "con" in dir():
        con.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

6 数据库连接池

数据库连接的昂贵之处： 数据库连接是一种关键的、有限的、昂贵的资源，在并发执行的应用程序中体现的尤为突出。

TCP连接需要三次握手，四次挥手，然后数据库还要验证用户信息

TCP建立连接的时候会占用线程，有等待时间，浪费线程资源，硬件开销和时间成本高

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

启用数据库连接池后不用关闭数据库连接，连接池会自动回收

例子：

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}
try:
    # 创建连接池
    pool=mysql.connector.pooling.MySQLConnectionPool(
        **config,       # 连接的数据库
        pool_size=10    # 连接池的大小
    )
    con = pool.get_connection()     # 获取连接池中的一个连接
    con.start_transaction()         # 开启事务
    cursor = con.cursor()           # 获取游标
    sql = "update t_emp set sal=sal+%s where deptno=%s"
    cursor.execute(sql, (200, 20))  # 执行sql
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

7 CRUD操作

增加：

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}

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(deptno, dname, loc) values (%s, %s, %s);"
    data=[
        [100, "A部门", "北京"],
        [110, "B部门", "上海"]
    ]
    cursor.executemany(sql, data)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

读取：

import mysql.connector

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "vaga"
}
con = mysql.connector.connect(**config)
username="1 OR 1=1"
password="1 OR 1=1"

sql = "select count(*) from t_user where username=%s " \
      " and aes_decrypt(unhex(password), 'HelloWorld')=%s;"

cursor = con.cursor()

# 让sql先编译，然后再传递给它值，防止注入攻击
cursor.execute(sql, (username, password))

print(cursor.fetchone()[0])
con.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

修改：

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}
try:
    # 创建连接池
    pool=mysql.connector.pooling.MySQLConnectionPool(
        **config,       # 连接的数据库
        pool_size=10    # 连接池的大小
    )
    con = pool.get_connection()     # 获取连接池中的一个连接
    con.start_transaction()         # 开启事务
    cursor = con.cursor()           # 获取游标
    sql = "update t_emp set sal=sal+%s where deptno=%s"
    cursor.execute(sql, (200, 20))  # 执行sql
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

删除：

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    # con.start_transaction()
    cursor = con.cursor()
    # sql = "delete e,d from t_emp e join t_dept d on e.deptno=d.deptno where d.deptno=20;"
    # truncate不依赖于事务
    sql = "truncate table t_emp;"
    cursor.execute(sql)
    # con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

8 案例1

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

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    # 数据表的结构来自于查询,用t_emp的表结构
    # ddl语句和事务管理没关系
    '''
    create table t_emp_new as (select * from t_emp);
    创建新表并将复制t_emp表中的信息，表结构和数据
    create table t_emp_new like t_emp;
    创建t_emp_new表，表结构是t_emp的表结构，无数据
    '''
    # sql = "create table t_emp_new as (select * from t_emp);"

    sql = "drop table t_emp_new;"
    cursor.execute(sql)
    sql = "create table t_emp_new like t_emp;"
    cursor.execute(sql)
    
    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 += ")"
    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)

    # 让这些员工隶属于sales部门
    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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

9 案例2

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

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "study"
}

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27