01. 数据库连接概述
02. PyMySQL模块
- 2.1 安装PyMySQL
- 2.2 PyMySQL连接数据库
  - 2.2.1 Connection类介绍与基本连接实现
导入PyMySQL模块
获取数据库连接对象。
操作数据库，这里简单打印一下连接对象即可。
关闭数据库连接。
导入MySQLClient模块，MySQLdb就是MySQLClient。

01. 数据库连接概述

任何编程语言都是不能直接操作数据库的，若想让编程语言操作数据库，则需要设置连接池。
Python语言中常用的连接池有：PyMySQL、MySQLClient。

02. PyMySQL模块

2.1 安装PyMySQL
PyMySQL是一个第三方模块，需要使用pip命令进行安装。
```
pip install pymysql
```
2.2 PyMySQL连接数据库

2.2.1 Connection类介绍与基本连接实现
pymysql.Connection()可以创建一个连接类，这个Connection类可以模拟一个数据库对象，从而与DBMS建立连接。
Connection()中所有的参数都使用关键字传值，其中常用的参数有：
- host：数据库的IP地址。
- port：数据库的端口号。
- user：与数据库建立连接时的用户名。
- password：用户名对应的密码。
- database：要连接的数据库。
- charset：数据库的编码格式。
若与数据库成功建立连接，则Connection()会返回一个连接对象，使用连接对象可以对数据库进行操作。
当数据库操作完成后，需要使用连接对象.close()关闭连接，否则会一直占用资源。
示例：连接本地的school数据库。 ```python
导入PyMySQL模块
import pymysql

获取数据库连接对象。

connect = pymysql.Connection( host=’127.0.0.1’, port=3306, user=’root’, password=’123456’, database=’school’, charset=’utf8mb4’ )

操作数据库，这里简单打印一下连接对象即可。

print(connect) #

关闭数据库连接。

connect.close()

<a name="TaPe7"></a>
#### 2.2.2 连接完善
- 2.2.1中代码存在的漏洞描述：
   - 在建立数据库连接到关闭数据库连接中间这段数据库操作的过程中可能会出现异常代码。
   - 对于编程语言而言，出现异常就代表着会终止程序的运行。
   - 也就是说，若操作数据库的程序出现了异常，那么数据库连接就不会被关闭。
   - 数据库的连接数量是有限的，若代码对数据库的连接一直不关闭，则之后其他程序可能就无法与数据库建立正常连接了。
   - 总结：无论操作数据库的程序出不出现异常，数据库连接都必须被关闭。
- 漏洞解决方案：将数据库连接与数据库操作放到`try`中，然后在`finally`中关闭连接。
- 优化2.2.1中的代码：
```python
import pymysql
connect = None  # 定义数据库连接对象
try:
    # 获取数据库连接对象。
    connect = pymysql.Connection(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='school',
        charset='utf8mb4'
    )
    # 操作数据库。
    print(connect)  # <pymysql.connections.Connection object at 0x000002825774AFD0>
except Exception as e:
    # 处理异常
    print(e)
finally:
    # 无论如何都要关闭以获取的连接
    if connect is not None:
        connect.close()

2.3 Python实现数据库的增删改查

2.3.1 增、删、改操作

Python的增、删、改操作类似于TCL事务处理，操作完成之后要提交。
实现思路（增、删、改三者的实现思路是一样的）：
- 首先，通过连接对象的cursor()方法获取游标。（PyMySQL中，SQL语句由游标执行）
- 然后编写SQL语句，完成后调用游标对象.excute(SQL语句)执行SQL语句。
- 在SQL语句执行过程中，需要进行异常捕获操作：
  - 若SQL语句执行过程出现了异常，则调用连接对象.rollback()回滚事务。
  - 若SQL语句执行过程没有出现异常，则调用连接对象.commit()提交事务。
代码实现（增）：（执行完成后可以到MySQL中用SELECT语句验证执行） ```python import pymysql

connect = None try: connect = pymysql.Connection( host=’127.0.0.1’, port=3306, user=’root’, password=’123456’, database=’school’, charset=’utf8mb4’ )

# 若连接存在，则开始增删改查操作。
if connect:
    # 通过连接对象获取操作数据库的游标。
    curser = connect.cursor()
    try:
        # 编写SQL语句。
        insert_sql = "INSERT INTO `class` VALUES (50, '高三5班');"
        # 让游标去执行SQL语句。
        curser.execute(insert_sql)
    except Exception as e:
        print(e)
        connect.rollback()  # 若SQL执行时出现异常，则回滚事务，不进行提交。
    else:
        # 若SQL完全正常执行，则提交事务。
        connect.commit()

except Exception as e: print(e) finally: if connect is not None: connect.close()


- 代码实现（删、改）：（执行完成后可以到MySQL中用SELECT语句验证执行）
```python
import pymysql
connect = None
try:
    connect = pymysql.Connection(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='school',
        charset='utf8mb4'
    )
    if connect:
        curser = connect.cursor()
        try:
            # 删除操作
            delete_sql = "DELETE FROM `class` WHERE cno = 50;"
            curser.execute(delete_sql)
            # 修改操作
            update_sql = "UPDATE `class` SET cname = '高三一班' WHERE cno = 10;"
            curser.execute(update_sql)
        except Exception as e:
            print(e)
            connect.rollback()
    else:
        connect.commit()
except Exception as e:
    print(e)
finally:
    if connect is not None:
        connect.close()

2.3.2 查询操作

SELECT查询操作不是TCL，因此不需要提交或者回滚操作。
查询操作的实现思路：
- 首先还是获取SQL游标，编写SQL语句。
- 接着用游标对象.execute(查询语句)执行SQL，此时SELECT语句execute()会返回查询到的行数。
- 接着调用游标对象的数据获取方法获取实际的记录（查询结果是一个迭代器，数据获取一条就会少一条）
  - curser.fetchone()：获取查询的第一条结果。
  - curser.fetchall()：获取查询的所有结果。
  - curser.fetchmany(n)：获取查询的前n条结果。
- 阿斯顿
查询方式一：游标对象.fetchone()获取查询结果的第一条数据。 ```python import pymysql

connect = None try: connect = pymysql.Connection( host=’127.0.0.1’, port=3306, user=’root’, password=’123456’, database=’school’, charset=’utf8mb4’ )

if connect:
    curser = connect.cursor()  # 获取SQL游标
    select_sql = "SELECT * FROM student;"
    # 执行SQL语句，SELECT语句execute()会返回查询到的行数。
    rows = curser.execute(select_sql)
    print(f"共查询到{rows}行数据。")  # 共查询到12行数据。
    # 方式一：获取查询的第一条结果
    first_data = curser.fetchone()
    print(f"第一条：{first_data}")  # 第一条：(1, '孟胤', datetime.date(2000, 3, 11), '男', 10)

except Exception as e: print(e) finally: if connect is not None: connect.close()


- 查询方式二：`游标对象.fetchall()`获取查询结果的所有数据。
```python
import pymysql
connect = None
try:
    connect = pymysql.Connection(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='school',
        charset='utf8mb4'
    )
    if connect:
        curser = connect.cursor()  # 获取SQL游标
        select_sql = "SELECT * FROM student;"
        # 执行SQL语句，SELECT语句execute()会返回查询到的行数。
        rows = curser.execute(select_sql)
        print(f"共查询到{rows}行数据。")
        # 方式二：获取查询的所有结果
        all_data = curser.fetchall()  # 是一个大元组，可以遍历得到每一条数据
        print("所有数据：")
        for line_data in all_data:
            print(line_data)
except Exception as e:
    print(e)
finally:
    if connect is not None:
        connect.close()
"""
运行结果：
共查询到12行数据。
所有数据：
(1, '孟胤', datetime.date(2000, 3, 11), '男', 10)
(2, '王慧', datetime.date(2001, 5, 17), '女', 10)
(3, '杨美奂', datetime.date(2002, 9, 22), '女', 11)
(4, '纪涵', None, '男', 11)
(5, '安佰杰', datetime.date(2001, 11, 11), '男', 11)
(6, '刘晓慧', datetime.date(2002, 12, 16), '女', 11)
(7, '耿云鹏', datetime.date(2001, 1, 9), '女', 12)
(8, '毕士科', datetime.date(2001, 3, 26), '男', 12)
(9, '魏雷', datetime.date(2002, 10, 11), '男', 12)
(10, '许欣洋', datetime.date(2000, 2, 20), '男', 13)
(11, '狄大帅', datetime.date(2001, 8, 17), '男', 13)
(12, '马大鹏', datetime.date(2000, 9, 22), '男', 13)
"""

查询方式三：游标对象.fetchmany(n)获取查询结果的前n条数据。 ```python import pymysql

connect = None try: connect = pymysql.Connection( host=’127.0.0.1’, port=3306, user=’root’, password=’123456’, database=’school’, charset=’utf8mb4’ )

if connect:
    curser = connect.cursor()  # 获取SQL游标
    select_sql = "SELECT * FROM student;"
    # 执行SQL语句，SELECT语句execute()会返回查询到的行数。
    rows = curser.execute(select_sql)
    print(f"共查询到{rows}行数据。")
    # 方式三：获取查询的前n条结果
    datas = curser.fetchmany(4)
    print("前4条数据：")
    for line_data in datas:
        print(line_data)

except Exception as e: print(e) finally: if connect is not None: connect.close()

“”” 共查询到12行数据。前4条数据： (1, ‘孟胤’, datetime.date(2000, 3, 11), ‘男’, 10) (2, ‘王慧’, datetime.date(2001, 5, 17), ‘女’, 10) (3, ‘杨美奂’, datetime.date(2002, 9, 22), ‘女’, 11) (4, ‘纪涵’, None, ‘男’, 11) “””

<a name="h6qzn"></a>
#### 2.3.3 获取字典类型的查询结果
- 从2.3.2中可以看出，SELECT操作的结果是元组类型的，这是因为游标获取时调用的是`connect.cursor()`这个空参方法。
- 实际上，查询到的数据还可以是字典类型的，只需要调用`connect.cursor(pymysql.cursors.DictCursor)`方法获取游标即可。
```sql
import pymysql
from pymysql.cursors import DictCursor
connect = None
try:
    connect = pymysql.Connection(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='school',
        charset='utf8mb4'
    )
    if connect:
        curser = connect.cursor(DictCursor)  # 获取字典类型的SQL游标
        select_sql = "SELECT * FROM student;"
        rows = curser.execute(select_sql)
        all_datas = curser.fetchall()  # 此时数据是一个字典类型的列表
        print(f"共查询到{rows}条数据：")
        for line_data in all_datas:
            print(line_data)
except Exception as e:
    print(e)
finally:
    if connect is not None:
        connect.close()
"""
共查询到12条数据：
{'sno': 1, 'sname': '孟胤', 'birthday': datetime.date(2000, 3, 11), 'gender': '男', 'cno': 10}
{'sno': 2, 'sname': '王慧', 'birthday': datetime.date(2001, 5, 17), 'gender': '女', 'cno': 10}
{'sno': 3, 'sname': '杨美奂', 'birthday': datetime.date(2002, 9, 22), 'gender': '女', 'cno': 11}
{'sno': 4, 'sname': '纪涵', 'birthday': None, 'gender': '男', 'cno': 11}
{'sno': 5, 'sname': '安佰杰', 'birthday': datetime.date(2001, 11, 11), 'gender': '男', 'cno': 11}
{'sno': 6, 'sname': '刘晓慧', 'birthday': datetime.date(2002, 12, 16), 'gender': '女', 'cno': 11}
{'sno': 7, 'sname': '耿云鹏', 'birthday': datetime.date(2001, 1, 9), 'gender': '女', 'cno': 12}
{'sno': 8, 'sname': '毕士科', 'birthday': datetime.date(2001, 3, 26), 'gender': '男', 'cno': 12}
{'sno': 9, 'sname': '魏雷', 'birthday': datetime.date(2002, 10, 11), 'gender': '男', 'cno': 12}
{'sno': 10, 'sname': '许欣洋', 'birthday': datetime.date(2000, 2, 20), 'gender': '男', 'cno': 13}
{'sno': 11, 'sname': '狄大帅', 'birthday': datetime.date(2001, 8, 17), 'gender': '男', 'cno': 13}
{'sno': 12, 'sname': '马大鹏', 'birthday': datetime.date(2000, 9, 22), 'gender': '男', 'cno': 13}
"""

2.4 SQL注入问题

2.4.1 SQL注入介绍与实验环境准备

SQL注入指Web应用程序对用户输入的数据的合法性没有判断或过滤不严，导致攻击者可以在Web应用程序中事先定义好的查询语句的结尾添加额外的SQL语句，实现在管理员不知情的情况下的非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
实验表创建： ```sql USE school;

DROP TABLE IF EXISTS user; CREATE TABLE IF NOT EXISTS user ( uid INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID’, uname VARCHAR(255) UNIQUE NOT NULL COMMENT ‘用户名’, upsw VARCHAR(255) NOT NULL COMMENT ‘密码’ );

INSERT INTO user(UNAME, UPSW) VALUES (‘zhangsan’, ‘123456’), (‘lisi’, ‘lisi123’);

SELECT * FROM user;


- 实验程序编写：用户输入用户名和密码，模拟登录。
```python
import pymysql
# 输入用户名与密码
username = input("username = ")
password = input("password = ")
connect = None
try:
    connect = pymysql.Connection(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='school',
        charset='utf8mb4'
    )
    if connect:
        curser = connect.cursor()
        # 编写查询的SQL语句，根据用户名和密码查找数据
        sql = f"SELECT * FROM `user` WHERE uname = '{username}' AND upsw = '{password}';"
        result_line = curser.execute(sql)
        # 若查找的记录不为0，则登录成功。（因为uname设置了UNIQUE约束，所以存在即唯一）
        if result_line:
            print(curser.fetchone())  # 输出用户信息
            print("登录成功！")
        else:
            print("用户信息输入有误，登录失败！")
except Exception as e:
    print(e)
finally:
    if connect is not None:
        connect.close()
"""
运行结果：
username = zhangsan
password = 123456
(1, 'zhangsan', '123456')
登录成功
"""

2.4.2 SQL注入复现

在2.4.1用户登录程序运行时，输入以下内容：

username = ' OR 1=1 -- 
password = 123
(1, 'zhangsan', '123456')
登录成功！

原因分析：
- 程序中的查询语句为：SELECT * FROMuserWHERE uname = '{username}' AND upsw = '{password}';
- 当username = ' OR 1=1 --、password = 123时，这条SQL语句实际上被拼接成了：SELECT * FROMuserWHERE uname = '' OR 1=1 -- ' AND upsw = '123';
- 因为在SQL语句中，--代表着注释，所以这条SQL语句最终为：SELECT * FROMuserWHERE uname = '' OR 1=1。
- 因为OR 1=1恒成立，因此WHERE后面的东西实际上可以直接忽略掉，也就是说最后执行的是SELECT * FROMuser``，用于获取数据表user中所有的信息。
- 因为第27行代码print的是curser.fetchone()，所以表中第一条uid=1的记录就被输出了。
危害：在互联网初期，username = ' OR 1=1 --几乎就相当于万能密码，由此可见SQL注入让网站失去了安全性。

2.4.3 预防SQL注入
形成SQL注入的根本原因：
- 在程序中写SQL语句，会出现预编译之前就把数据设置在SQL语句上的情况。
- 此时就会出现当编译时，才去分析数据的逻辑，此时就容易让攻击者钻空子。
解决方案：
- 先编写SQL语句，数据的位置用占位符填充，然后先把流程逻辑预编译。（此时这条SQL就类似于一个存储过程了）
- 接着再去填充数据，此时就会直接验证数据的正确性，SQL语句的结构就不会改变了。

代码实现：（仅修改数据库中登录部分的逻辑，其他代码与2.4.1中完全一致）

if connect:
  curser = connect.cursor()
  # 先编写SQL语句结构，要填充数据的位置用占位符先占位。
  sql = f"SELECT * FROM `user` WHERE uname = %s AND upsw = %s;"
  # 游标对象的execute()存在args参数，这个参数可以传递”元组、列表、字典“三种类型的数据。
  # 将要插入到SQL语句中的数据进行封装，然后传递给args参数即可。
  result_line = curser.execute(sql, (username, password))
  # 查询数据并实现登录。
  if result_line:
      print(curser.fetchone())  # 输出用户信息
      print("登录成功！")
  else:
      print("用户信息输入有误，登录失败！")

此时再执行所谓的万能密码，也无法登录了。 ```sql “”” 正常数据 “”” username = zhangsan password = 123456 (1, ‘zhangsan’, ‘123456’) 登录成功！

“”” 万能密码 “”” username = ‘ OR 1=1 — password = 123 用户信息输入有误，登录失败！

<a name="wD7qq"></a>
## 03. MySQLClient模块
<a name="mlwhQ"></a>
### 3.1 安装MySQLClient
- MySQLClient是一个第三方模块，需要使用pip命令进行安装。
```python
pip install mysqlclient

3.2 MySQLClient模块的常用操作

MySQLClient的操作与PyMySQL的操作几乎一致，这里演示一下数据库连接即可。 ```sql
导入MySQLClient模块，MySQLdb就是MySQLClient。
import MySQLdb

connect = None try:

# 获取数据库连接对象。
connect = MySQLdb.Connection(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='school',
    charset='utf8mb4'
)
# 操作数据库，这里简单打印一下连接对象即可。
print(connect)  # <_mysql.connection open to '127.0.0.1' at 000001DAA9A304C0>

except Exception as e: print(e) finally:

# 关闭数据库连接。
if connect is not None:
    connect.close()

```

其他操作可阅读文档：https://mysqlclient.readthedocs.io/

Python BI AI

07. Python连接数据库

01. 数据库连接概述

02. PyMySQL模块

2.1 安装PyMySQL

2.2 PyMySQL连接数据库

2.2.1 Connection类介绍与基本连接实现

导入PyMySQL模块

获取数据库连接对象。

操作数据库，这里简单打印一下连接对象即可。

关闭数据库连接。

2.3 Python实现数据库的增删改查

2.3.1 增、删、改操作

2.3.2 查询操作

2.4 SQL注入问题

2.4.1 SQL注入介绍与实验环境准备

2.4.2 SQL注入复现

2.4.3 预防SQL注入

3.2 MySQLClient模块的常用操作

导入MySQLClient模块，MySQLdb就是MySQLClient。