数据库软件介绍-社工库已整理22g数据

数据库连接

在连接数据库之前，请确认以下内容：

1、查看数据库TESTDB（测试数据库）是否已经创建，打开MySQL客户端界面输入密码，输入命令：

1 show databases;

2.看到没有这个数据库，那么我们开始创建一个TESTDB数据库数据库软件介绍，输入命令：

1 create database TESTDB;

3、再次输入第一步的命令，查看是否已经创建成功

4、查看TESTDB数据库中是否存在EMPLOYEE（用于测试的测试表），输入命令：

1 use testdb;
2 show tables;

5、发现TESTDB数据库中没有EMPLOYEE表，所以如果没有EMPLOYEE表数据库软件介绍，我们可以创建一个EMPLOYEE表。

创建实例

运行结果：（从结果中可以看出已经成功创建了一个Connection和Cursor对象。）

下面将以具体代码的形式介绍如何在python中对MySQL数据库进行增删改查。示例： 1.连接数据库

运行结果：

参考代码：

 1 # coding=utf-8
 2 # 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
 3 
 4 # 2.注释：包括记录创建时间，创建人，项目名称。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-宏哥
 8 Project:学习和使用python操作MySQL数据库
 9 '''
10 # 3.导入模块
11 import pymysql
12 
13 import pymysql
14 
15 # 打开数据库连接
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法创建一个游标对象
19 cursor = conn.cursor()
20 
21 # 使用execute()方法执行SQL查询
22 cursor.execute('SELECT VERSION()')
23 
24 # 使用fetchone()方法获取单条数据
25 data = cursor.fetchone()
26 
27 # 打印
28 print('database version: %s' % data)
29 
30 # 关闭数据库连接
31 conn.close()

2.创建数据库表

如果数据库连接存在我们可以使用execute()方法为数据库创建一个表，如下创建表EMPLOYEE：

运行代码后查看是否有创建表，输入命令：

1 use TESTDB；
2 show tables;

运行结果：

参考代码：

 1 # coding=utf-8
 2 # 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
 3 
 4 # 2.注释：包括记录创建时间，创建人，项目名称。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-宏哥
 8 Project:学习和使用python操作MySQL数据库
 9 '''
10 # 3.导入模块
11 import pymysql
12 
13 import pymysql
14 
15 # 打开数据库连接
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法创建一个游标对象cursor
19 cursor = conn.cursor()  # 游标对象用于执行查询和获取结果
20 
21 # 使用execute()方法执行SQL，如果表存在则将其删除
22 cursor.execute('DROP TABLE IF EXISTS EMPLOYEE')
23 
24 # 使用预处理语句创建表
25 sql = """CREATE TABLE `employee` (
26   `first_name` varchar(255) DEFAULT NULL COMMENT '姓',
27   `last_name` varchar(255) DEFAULT NULL COMMENT '名',
28   `age` int(11) DEFAULT NULL COMMENT '年龄',
29   `sex` varchar(255) DEFAULT NULL COMMENT '性别',
30   `income` varchar(255) DEFAULT NULL COMMENT '收入'
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
32 """
33 
34 # 执行SQL语句
35 cursor.execute(sql)
36 
37 # 关闭数据库连接
38 conn.close()

3.数据库插入操作

以下示例使用 SQL INSERT 语句将记录插入表 EMPLOYEE：

1、首先查看是否有记录，输入查询语句：

1 select * from employee;

2.代码实现

运行结果：

再次查看是否有记录，输入第一步的sql语句：（可以看到插入了一条记录）

参考代码：

# coding=utf-8
# 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
# 2.注释：包括记录创建时间，创建人，项目名称。
'''
Created on 2019-5-22
@author: 北京-宏哥
Project:学习和使用python操作MySQL数据库
'''
# 3.导入模块
import pymysql
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句：向数据表中插入数据
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交事务到数据库执行
    conn.commit()  # 事务是访问和更新数据库的一个程序执行单元
except:
    # 如果发生错误则执行回滚操作
    conn.rollback()
# 关闭数据库连接
conn.close()

另一种写法：

# coding=utf-8
# 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
# 2.注释：包括记录创建时间，创建人，项目名称。
'''
Created on 2019-5-22
@author: 北京-宏哥
Project:学习和使用python操作MySQL数据库
'''
# 3.导入模块
import pymysql
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句：向数据表中插入数据
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交事务到数据库执行
    conn.commit()  # 事务是访问和更新数据库的一个程序执行单元
except:
    # 如果发生错误则执行回滚操作
    conn.rollback()
# 关闭数据库连接
conn.close()

例子：

以下代码使用变量向 SQL 语句传递参数：

1 ..................................
2 user_id = "root"
3 password = "root"
4 
5 con.execute('insert into Login values( %s,  %s)' % \
6              (user_id, password))
7 ..................................

3.数据库查询操作

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

例子：

查询EMPLOYEE表中薪水（salary）字段大于1000的所有数据：

运行结果：

参考代码：

 1 # coding=utf-8
 2 # 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
 3 
 4 # 2.注释：包括记录创建时间，创建人，项目名称。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-宏哥
 8 Project:学习和使用python操作MySQL数据库
 9 '''
10 # 3.导入模块
11 import pymysql
12 
13 import pymysql
14 
15 # 打开数据库连接
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法获取操作游标
19 cursor = conn.cursor()
20 
21 # SQL语句：查询
22 sql = "SELECT * FROM employee WHERE income > 1000 "
23 
24 # 异常处理
25 try:
26     # 执行SQL语句
27     cursor.execute(sql)
28     # 获取所有的记录列表
29     results = cursor.fetchall()
30     # 遍历列表
31     for row in results:
32         # 打印列表元素
33         print(row)
34         # 姓
35         first_name = row[0]
36         # 名
37         last_name = row[1]
38         # 年龄
39         age = row[2]
40         # 性别
41         sex = row[3]
42         # 收入
43         income = row[4]
44         # 打印列表元素
45         print(first_name, last_name, age, sex, income)
46 except:
47     print('Uable to fetch data!')
48 
49 # 关闭数据库连接
50 conn.close()

4.数据库更新操作

更新操作用于更新数据表中的数据。 以下示例将 EMPLOYEE 表中 SEX 字段为“M”的 AGE 字段递增 5：

1. 之前的年龄是20岁，

2.代码实现：

3、运行结果：

4、检查年龄是否已经变成25岁

5.参考代码：

# coding=utf-8
# 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
# 2.注释：包括记录创建时间，创建人，项目名称。
'''
Created on 2019-5-22
@author: 北京-宏哥
Project:学习和使用python操作MySQL数据库
'''
# 3.导入模块
import pymysql
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句，执行更新操作
sql = 'UPDATE employee SET age = age + 5 WHERE sex = "M"'
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    conn.commit()
except:
    # 发生错误时回滚
    conn.rollback()
# 关闭数据库连接
conn.close()

5.删除操作

删除操作用于删除数据表中的数据。 下面的例子演示删除数据表EMPLOYEE中AGE大于20的所有数据：

1.只有一项大于20，删除了就没有记录了。 进入

1  select * from employee;

2.代码实现

三、运行结果

4.查看数据库表，重复第一步

5.参考代码

 1 # coding=utf-8
 2 # 1.先设置编码，utf-8可支持中英文，如上，一般放在第一行
 3 
 4 # 2.注释：包括记录创建时间，创建人，项目名称。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-宏哥
 8 Project:学习和使用python操作MySQL数据库
 9 '''
10 # 3.导入模块
11 import pymysql
12 
13 import pymysql
14 
15 # 打开数据库连接
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法获取操作游标
19 cursor = conn.cursor()
20 
21 # SQL语句，执行删除操作
22 sql = 'DELETE FROM employee WHERE age >20'
23 
24 # 异常处理
25 try:
26     # 执行SQL语句
27     cursor.execute(sql)
28     # 提交到数据库执行
29     conn.commit()
30 except:
31     # 发生错误时回滚
32     conn.rollback()
33 
34 # 关闭数据库连接
35 conn.close()

执行业务

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

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

Python DB API 2.0 的事务提供了commit 和rollback 两种方法。

例子：

 1 # SQL删除记录语句
 2 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
 3 try:
 4    # 执行SQL语句
 5    cursor.execute(sql)
 6    # 向数据库提交
 7    db.commit()
 8 except:
 9    # 发生错误时回滚
10    db.rollback()

对于支持事务的数据库，在Python数据库编程中，当创建游标时，会自动启动一个不可见的数据库事务。

commit()方法执行游标的所有更新操作，rollback()方法回滚当前游标的所有操作。 每个方法都会启动一个新事务。

错误处理

DB API 中定义了一些数据库操作错误和异常。 下表列出了这些错误和异常：

异常

描述

警告

当出现严重警告时触发，比如插入的数据被截断等。必须是StandardError的子类。

错误

除警告之外的所有其他错误类别。 必须是 StandardError 的子类。

接口错误

当数据库接口模块本身出现错误（而不是数据库出现错误）时触发。 必须是错误的子类。

数据库错误

当发生与数据库相关的错误时触发。 必须是错误的子类。

数据错误

当数据处理过程中出现错误时触发，例如除零错误、数据超出范围等。必须是DatabaseError的子类。

操作错误

指不受用户控制，但在操作数据库时发生的错误。 比如：连接意外断开，找不到数据库名，事务处理失败，内存分配错误等都是操作数据库时出现的错误。 必须是 DatabaseError 的子类。

完整性错误

与完整性相关的错误，例如外键检查失败等。必须是 DatabaseError 子类。

内部错误

数据库内部错误，如游标失败、事务同步失败等。必须是DatabaseError的子类。

编程错误

程序错误，如数据表（表）未找到或已存在、SQL语句语法错误、参数个数错误等。必须是DatabaseError的子类。

不支持错误

不支持的错误是指使用了数据库不支持的函数或API。 比如在connection对象上使用.rollback()函数，但是数据库不支持事务或者事务被关闭。 必须是 DatabaseError 的子类。