python基础学习总结(十二)

Python 数据库

一. 数据库简介

Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。

Python 数据库接口支持非常多的数据库，你可以选择适合你项目的数据库：

  1. GadFly
  2. mSQL
  3. MySQL
  4. PostgreSQL
  5. Microsoft SQL Server 2000
  6. Informix
  7. Interbase
  8. Oracle
  9. Sybase

不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和MySQL数据库模块。

DB-API 是一个规范. 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。

而Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python DB-API使用流程：

  1. 引入 API 模块
  2. 获取与数据库的连接
  3. 执行SQL语句和存储过程
  4. 关闭数据库连接

这里我们使用MySQL数据库

二. MySQLdb

MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。
使用pymysql也可

三. 安装MySQLdb

在python中使用

1
pip install mysqlclient -i https://pypi.douban.com/simple

安装mysqlclient包即可

在python中引入MySQLdb不报错即安装成功

1
import MySQLdb

四. 数据库连接

连接数据库前，请先确认以下事项：

  1. 您已经创建了数据库 testdb
  2. 在testdb数据库中您已经创建了表 employee
  3. employee表字段为 old_name, new_name, age, sex 和 address
  4. 连接数据库testdb使用的用户名为 “root” ，密码为 “123456”,你可以可以自己设定或者直接使用root用户名及其密码， Mysql数据库用户授权请使用Grant命令。
  5. 在电脑上已经安装了 Python MySQLdb 模块。
  6. 了解基础SQL语法

创建数据库:

1
2
3
4
5
6
7
8
9
10
11
12
create database if not exists testdb ;
use testdb;
create table employee (
    old_name varchar(100),
    new_name varchar(100),
    age int,
    sex varchar(10),
    address varchar(100)
) engine = innodb default charset = utf8;
# 指定表字符为utf8，否则插入中文会报错

insert into employee values ('Tom', 'Tommy',20,'男','北京');

五. 数据库操作

1. 数据库连接操作

MySQLdb.connect(本地ip,数据库用户,数据库密码,数据库库名，数据编码格式)
例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 连接数据库
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print("Database version : %s " % data)

# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()

2. 创建数据库表操作

如果数据库连接存在我们可以使用execute()方法来为数据库创建表，如下所示创建表user：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 创建数据库表
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS user")

# 创建数据表SQL语句
sql = 'create table {} (id varchar(20) not null, name varchar(20) not null, primary key(id))'.format("user")

cursor.execute(sql)

# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()

3. 数据库插入操作

与创建不同的是在使用execute注入SQL语句的时候要添加一个异常处理,提交与回滚

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
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

try:
    # SQL 插入语句
    sql = """INSERT INTO EMPLOYEE(old_name, new_name, age, sex, address)  
                     VALUES (%s, %s, %s, %s, %s)"""

    # 单个插入
    # val1 = ('PT', 'PPT', 22, '女', '天津')
    # val2 = ('BA', 'BAO', 18, '男', '上海')
    # # 执行sql语句
    # cursor.execute(sql, val1)
    # cursor.execute(sql, val2)

    # 多个插入
    values = [
        ('PT', 'PPT', 22, '女', '天津'),
        ('BA', 'BAO', 18, '男', '上海')
    ]
    # 使用executemany()执行多个插入
    cursor.executemany(sql, values)

    # 提交到数据库执行
    db.commit()
    print("数据插入成功！")
except Exception as e:
    # 如果发生错误则回滚
    db.rollback()
    print("数据插入失败！", e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    db.close()

4. 数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

方法 作用
fetchone() 该方法获取下一个查询结果集。结果集是一个对象
fetchall() 接收全部的返回结果行
rowcount 这是一个只读属性，并返回执行execute()方法后影响的行数

例如: 查询表中age（年龄）字段大于等于20的所有数据：

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
# 创建数据库
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

try:
    # SQL 查询语句
    sql = "select * from employee where age >= %s" % (20)

    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        old_name = row[0]
        new_name = row[1]
        age = row[2]
        sex = row[3]
        address = row[4]
        # 打印结果
        print("old_name={},new_name={},age={},sex={},address={}".format(old_name, new_name, age, sex, address))
except Exception as e:
    print("数据查询失败！", e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    db.close()

例如: 查询empoyee表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

try:
    # SQL 查询语句
    sql = "desc {}".format("employee")

    # 执行SQL语句
    cursor.execute(sql)
    print("显示表结构：", cursor.fetchall())
except Exception as e:
    print("数据查询失败！", e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    db.close()

5. 数据库更新操作

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
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

try:
    # SQL 更新语句
    sql = "UPDATE employee SET age = age + 1 WHERE SEX = '%c'" % ('女')

    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
    print("数据更新成功！")
except Exception as e:
    # 发生错误时回滚
    db.rollback()
    print("数据查询失败！", e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    db.close()

6. 数据库删除操作

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
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

try:
    # SQL 删除语句
    sql = "DELETE FROM employee WHERE AGE < %s" % (20)

    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
    print("数据删除成功！")
except Exception as e:
    # 发生错误时回滚
    db.rollback()
    print("数据查询失败！", e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭数据库连接
    db.close()

六. 事务执行

事务机制可以确保数据一致性。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

属性 性质
原子性（atomicity） 一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency） 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation） 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability） 持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 创建数据库
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "root", "123456", "testdb", charset='utf8')

try:
    with db:
        # 使用with语句确保事务在结束时正确提交或回滚
        cursor = db.cursor()
        # 使用参数化查询来防止SQL注入
        sql = "DELETE FROM employee WHERE age > %s" % 20
        cursor.execute(sql)
        # 在with语句块结束时，如果没有异常发生，则自动调用conn.commit()
        # 如果在with语句块内发生异常，则会自动调用conn.rollback()
except Exception as e:
    print("数据查询失败！", e)
# with语句已经为我们关闭了游标和数据库连接，这里不需要再调用cursor.close()和conn.close()方法

七. 错误处理

DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常:

异常类 描述
Exception 所有异常的基类
  Warning 警告的基类（通常不用于控制流，只是通知）
    UserWarning 用户生成的警告（默认警告类别）
  Error 异常的基类，用于所有“严重”的情况
    DatabaseError 数据库相关的错误
      DataError 数据处理错误，如数据类型不匹配、数据范围超出等
      OperationalError 数据库操作错误，如连接失败、事务处理失败等
      IntegrityError 完整性约束违反，如外键约束失败、唯一约束违反等
      InternalError 数据库的内部错误，如游标失效、事务同步失败等
      ProgrammingError 编程错误，如SQL语法错误、表不存在等
      NotSupportedError 不支持的操作或功能，如数据库不支持的API调用
    InterfaceError 数据库接口错误，如游标未关闭、连接已关闭但尝试执行操作等

注意：

  1. 上面的异常类并不都是PEP 249直接定义的，而是基于Python 3中常见的异常处理模式和数据库编程中可能遇到的错误类型构建的。
  2. 实际的数据库适配器（如psycopg2、sqlite3、pymysql等）可能会定义自己的特定异常类，这些类将继承自上述异常类之一。
  3. 在实际编程中，你应该查阅你所使用的数据库适配器的文档，以了解它定义了哪些异常类，并了解如何适当地捕获和处理这些异常。
  4. Warning类及其子类通常用于发出警告而不是控制程序流程。在Python中，警告可以通过 warnings模块进行过滤、忽略或转换为异常。然而，在数据库编程中，Warning通常用于非严重的问题，如数据截断。
  5. Exception是所有异常的基类，在Python 3中，所有的内置异常和自定义异常都应该是 Exception的子类（直接或间接）。