疯狂Python讲义学习笔记（含习题）之 数据库编程

Python为操作不同的数据库提供了不同的模块。

一、Python数据库API简介

Python数据库模块都遵循Python制订的DB API协议，目前该协议的最新版本是2.0，因此不同Python数据库模块之间有很多操作时相同的，尽管Python提供了许多不同的数据库模块，但是只要掌握一个模块之后，再学习其他模块就会非常容易。

下面来看一下Python的DB API协议中规定的不同数据库模块之间的通用部分。

（一）全局变量

变量	描述
apilevel	显示数据库模块的API版本号。对于支持DB API2.0版本的数据库模块来说，这个变量值通常是2.0.如果这个变量不存在，则可能因为该数据库模块暂时还不支持DB API 2.0，此次应该谨慎使用该模块，或选择使用支持该数据库的其他数据库模块。
threadsafety	指定数据库模块的线程安全等级，该等级值为0~3，其中3代表该模块完全是线程安全的；1表示该模块具有部分线程安全性，线程可以共享该模块，但不能共享连接；0则表示线程完全不能共享该模块。
paramstyle	指定当SQL语句需要参数时，可以使用哪种风格的参数。该变量可能返回如下变量值： ● format：表示在SQL语句中使用Python标准的格式化字符串代表参数。如：在程序中需要参数的地方使用%s，接下来程序即可为这些参数指定参数值。 ● pyformat：表示在SQL语句中使用扩展的格式代码代表参数。如使用%(name)，这样即可以使用包含key为name的字典为该参数指定参数值。 ● qmark：表示在SQL语句中使用问号（？）代表参数。在SQL语句中有几个参数，全部用问号代替。 ● numeric：表示在SQL语句中使用数字占位符（：N）代表参数。如，:1代表第一个参数，:2代表另一个参数，这些数字相当于参数名，因此他们不一定需要连续。 ● named：表示在SQL语句中使用命名占位符（:name）代表参数。如:name代表一个参数，:age也代表一个参数。

（二）数据库API的核心类

遵循DB API 2.0协议的数据库模块通常会提供一个connect()函数，用于连接数据库，并返回数据库连接对象。

数据库连接对象通用属性和方法如下：

属性/方法	描述
cursor(factory=Cursor)	打开游标
commit()	提交事务
rollback()	回滚事务
close()	关闭数据库连接
isolation_level	返回或设置数据库连接中事务的隔离级别
in_transaction	判断当前是否处于事务中

游标对象通用属性和方法如下：

属性/方法	描述
execute(sql[,parameters])	执行SQL语句。parameters参数用于为SQL语句中的参数指定值。
executemany(sql, seq_of_parameters)	重复执行SQL语句。可以通过seq_of_parameters序列为SQL语句中的参数指定值，该序列有多少个元素，SQL语句就被执行多少次。
executescript(sql_script)	不是DB API 2.0的标准方法。该方法可以直接执行包含多条SQL语句的SQL脚本
fetchone()	获取查询结果集的下一行。如果没有下一行，返回None
fetchmany(size=cursor.arraysize)	返回查询结果集的下N行组成的列表。如果没有更多的数据行，则返回空列表
fetchall()	返回查询结果集的全部行组成的列表
close()	关闭游标
rowcount	该只读属性返回受SQL语句影响的行数。对于executemany()方法，该方法所修改的记录条数也可通过该属性获取。
lastrowid	该只读属性可获取最后修改行的rowid
arraysize	用于设置或获取fetchmany()默认获取的记录条数，该属性默认值为1.部分数据库模块没有该属性
description	该只读属性可获取最后一次查询返回的所有列的信息
connection	该只读属性返回创建游标的数据库连接对象。部分数据库模块没有该属性

（三）操作数据库的基本流程

 

① 调用connect()方法打开数据库连接，返回数据库连接对象

② 通过数据库连接对象打开游标

③ 使用游标执行SQL语句（包括DDL、DML、select查询语句等）。如果执行的是查询语句则处理查询数据。

④ 关闭游标

⑤ 关闭数据库连接

 

二、操作SQLite数据库

Python默认自带了SQLite数据库和SQLite数据库的API模块。

SQLite只是一个嵌入式的数据库引擎，专门适用于在资源有限的设备上（如手机、PDA等）进行适量数据的存取。

SQLite数据库只是一个文件，不需要服务器进程。SQLite不需要安装、启动服务器进程。

>>> import sqlite3
>>> sqlite3.apilevel
'2.0'
>>> sqlite3.paramstyle
'qmark'

可以看出，SQLite数据库模块遵守DB API 2.0，并且使用问号作为参数。

（一）创建数据表

程序只要通过数据库连接对象打开游标，就可以执行DDL语句，DDL语句负责创建表、修改表或删除表。

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
# 也可以使用特殊名称:memory:,代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# 第二步：获取游标
c = conn.cursor()
# 第三步：执行DDL语句创建数据库表
c.execute('''create table user_tb(
_id integer primary key autoincrement,
name text,
pass text,
gender text)
''')
# 执行DDL语句创建数据表
c.execute('''
create table order_tb(
_id integer primary key autoincrement,
item_name text,
item_price real,
item_number real,
user_id inteter,
foreign key(user_id) references user_tb(_id))
''')
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

SQLite内部只支持NULL 、INTEGER 、REAL（浮点数）、TEXT（文本）和BLOB（大二进制对象〉这5种数据类型，但实际上SQ Lite 完全可以接受varchar(n)、char(n)、decimal(p,s)等数据类型，只不过SQLite 会在运算或保存时将它们转换为上面5种数据类型中相应的类型。

SQLite它允许把各种类型的数据保存到任何类型的字段中，开发者可以不用关心声明该字段所使用的数据类型。有一种情况例外:被定义为“ INTEGER PRIMARY KEY ”的字段只能存储64位整数，当使用这种宇段保存除整数以外的其他类型的数据时，SQLite会产生错误。

SQLite允许在存入数据时忽略底层数据列实际的数据类型，因此在编写建表语句时可以省略各数据列后面的类型声明。如：

create table my_test
(
    _id integer primary key autoincrement,
    name ,
    pass ,
    gender
);

（二）使用SQLite Expert工具

安装 SQLite Expert 工具的步骤如下：

1. 登录 http://www.sqliteexpert.com/download.html 站点来下载 SQLite Expert，该工具提供了两个版本：免费的个人版和收费的商业版。此处选择免费的个人版。将页面滚动到下方，找到“SQLite Expert Personal 5.x”，然后单击下方的链接（64 位操作系统选择 64bit 版，32 位操作系统选择 32bit 版），如图 1 所示。

   
   
    

2. 以 64 位操作系统为例，所以下载 SQLiteExpertPersSetup64.exe 文件，下载完成后单击该文件开始安装，其安装过程和安装普通的 Windows 软件完全相同。
3. 安装完成后，启动 SQLite Expert 工具，启动后可以看到如图所示的程序界而。

   
   

   
   程序界面的左上角工具栏中看到 4 个工具按钮，它们的作用依次是创建数据库、创建内存中的数据库、打开数据库和关闭数据库：
   • 如果要使用 SQLite Expert 新建数据库，则单击工具栏中的“New Database”按钮，即可创建一个新的数据库。
   • 如果要使用 SQLite Expert 打开已有的数据库文件，则单击工具栏中的“Open Database”按钮。
4. 单击工具栏中的“Open Database”按钮，打开如图 3 所示的浏览数据库文件窗口。

   
   

   找到前面程序所创建的 first.db 文件，然后单击“打开”按钮，SQLite Expert 将会打开 first.db 文件所代表的数据库。
5. 打开 first.db 数据库之后，可以在 SQLite Expert 工具中看到该数据库包含两个数据表。随便选中一个数据表，就可以在右边看到该数据表的详细信息，包括数据列（Columns）、主键（Primary Key）、索引（Indexs）、外键（Foreign Keys）、唯一约束（Unique Constraints）等

   
   

如图所示的界面，就是一个非常方便的数据库管理界面，可以通过该界面执行创建数据表、删除数据表、添加数据、删除数据等操作。
 

（三）使用序列重复执行DML语句

使用游标的execute()方法可以执行DML的insert、update、delete等语句。

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
conn = sqlite3.connect('first.db')
# 第二步：获取游标
c = conn.cursor()
# 第三步：执行insert语句插入数据
c.execute('insert into user_tb values(null, ?, ?, ?)', ('张三', '123456', 'male'))
c.execute('insert into order_tb values(null, ?, ?, ?, ?)',
          ('鼠标', '34.2', '3', 1))
# 提交执行DML语句
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

以上程序时通过execute一次执行一条SQL语句，可以使用executemany()方法，多次执行同一条SQL语句：

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
# 也可以使用特殊名称:memory:，代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# 第二步：获取游标
c = conn.cursor()
# 第三步：调用executemany()方法多次执行同一条SQL语句
c.executemany('insert into user_tb values(null, ?, ?, ?)',
              (('sun', '123456', 'male'), ('bai', '123456', 'female'),
               ('zhu', '123456', 'male'), ('niu', '123456', 'male'),
               ('tang', '123456', 'male')))
# 提交执行
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

运行以上代码会向user_tb表中插入5条数据。除了可以重复执行insert语句外，executemany还可以执行update, delete等语句，只要其第二个参数是一个序列，序列的每个元素都可以对被执行的SQL语句的参数赋值即可。

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
# 也可以使用特殊名称:memory:，代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# 第二步：打开游标
c = conn.cursor()
# 第三步：调用executemany()方法多次执行同一条SQL语句
c.executemany('update user_tb set name=? where _id=?',
              (('小孙', 2), ('小白', 3), ('小猪', 4), ('小牛', 5), ('小唐', 6)))
# 通过rowcount获取被修改的记录条数
print('修改的记录条数：', c.rowcount)
# 提交查询
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()

（四）执行查询

游标的fetchone()、fetchmany(n)、fetchall()可以用来获取查询结果，分别用于获取1条、n条和全部记录。

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
conn = sqlite3.connect('first.db')
# 第二步：获取游标
c = conn.cursor()
# 第三步：调用execute执行select语句
c.execute('select * from user_tb where _id > ?', (2,))
print('查询返回的记录数：', c.rowcount)
# 通过游标的description属性获取列信息
for col in (c.description):
    print(col[0], end='\t')
print('\n-------------------------')
while True:
    # 获取一条记录，每行数据都是一个元组
    row = c.fetchone()
    # 如果获取的row为None，也就是没有查询到数据，则退出循环
    if not row:
        break
    print(row)
    print(row[1] + '-->' + row[2])
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

如果使用executemany()执行select语句，程序会抛出ProgrammingError: executemany() can only execute DML statements。

（五）事务控制

事务是由一步或几步数据库操作序列组成的逻辑执行单元，这一系列操作要么全部执行，要么全部放弃执行。

事务特性：

事务	描述
原子性（Atomicity)	事务是应用中最小的执行单位，如原子是自然界中的最小颗粒一样，具有不可再分的特征，事务是应用中不可再分的最小逻辑单元
一致性（Consistency）	事务执行的结果，必须使数据库从一种一致性状态变为另一种一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致性状态。如果系统运行发生中断，某个事务尚未完成而被迫中断，而该未完成的事务对数据库所做的修改已被写入数据库中，此时数据库就处于一种不正确的状态。一致性是通过原子性来保证的。
隔离性（Isolation）	各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说，并发执行的事务之间不能看到对方的中间状态，它们不能相互影响。
持续性（Durability）	持续性也成为持久性（Persistence），指事务一旦提交，对数据所做的任何改变都要记录到永久存储器中，也就是保存到物理数据库中。

一旦事务中的任何一个数据库操作执行失败后，将回滚（rollback）事务，使事务中所做的修改全部失效。事务回滚有两种方式：

回滚方式	描述
显式回滚	调用数据库连接对象的rollback
自动回滚	系统错误，或者强行退出。

假设程序执行了DML语句后没有执行commit()方法，则不会提交事务，程序所做的修改不会被提交到底层数据库。

（六）执行SQL脚本

可以使用executescript()方法执行SQL脚本，但这并不是一个标准的DB API 2.0方法，因此有些数据库模块中可能不包含这个方法。

# 导入访问SQLite的模块
import sqlite3
​
# 第一步：打开或创建数据库
# 也可以使用特殊名称：memory:，代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# 第二步：获取游标
c = conn.cursor()
# 第三步：调用executescript()方法，执行一段SQL脚本
c.executescript('''
    insert into user_tb values(null, '李四', '3444', 'male');
    insert into user_tb values(null, '王五', '44444', 'male');
    create table item_tb(_id integer primary key autoincrement,
    name,
    price);
    ''')
# 提交事务
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()

（七）创建自定义函数

数据库连接对象还提供了一个create_function(name, num_params, func)方法，用于注册一个自定义函数，以便于程序在接下来的SQL语句中使用该自定义函数。其中参数name表示指定注册的自定义函数的名字，num_params指定自定义函数所需参数个数，func指定自定义函数对应的函数。

# 导入访问SQLite的模块
import sqlite3
​
​
# 定义函数，准备注册为SQL语句中的自定义函数
def reverse_ext(st):
    # 对字符串翻转，前后添加方括号
    return '[' + st[::-1] + ']'
​
​
# 第一步：打开或创建数据库
conn = sqlite3.connect('first.db')
# 调用create_function注册自定义函数：enc
conn.create_function('enc', 1, reverse_ext)
# 第二步：获取游标
c = conn.cursor()
# 第三步：在SQL语句中使用自定义函数
c.execute('insert into user_tb values(null, ?, enc(?), ?)',
          ('郭富城', '123456', 'male'))
# 提交事务
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭连接
conn.close()
​

（八）创建聚集函数

标准的SQL语句提供了如下5个标准的聚集函数

聚集函数	描述
sum()	统计总和
avg()	统计平均值
count()	统计记录条数
max()	统计最大值
min()	统计最小值

可以使用数据库连接对象的create_aggregate(name, num_params, aggregate_class)方法注册一个自定义的聚集函数。其中参数name表示自定义聚集函数的名称，num_params指定聚集函数所需的参数，aggregate_calss指定聚集函数的实现类。该类必须实现step(self, params...)和finalize(self)方法，其中step()方法对于查询所返回的每条记录各执行一次；finalize(self)方法只在最后执行一次，该方法的返回值将作为聚集函数最后的返回值。

# 导入访问SQLite的模块
import sqlite3
​
​
# 定义类，准备注册为SQL中的自定义聚集函数
class MinLen:
​
    def __init__(self):
        self.min_len = None
​
    def step(self, value):
        # 如果self.min_len还未赋值，则直接将当前value赋给self.min_len
        if self.min_len is None:
            self.min_len = value
            return
        # 找到一个长度更短的value，用value代替self.min_len
        if len(self.min_len) > len(value):
            self.min_len = value
​
    def finalize(self):
        return self.min_len
​
​
# 第一步：打开或创建数据库
conn = sqlite3.connect('first.db')
# 调用create_aggregate注册自定义聚集函数：min_len
conn.create_aggregate('min_len', 1, MinLen)
# 第二步：获取游标
c = conn.cursor()
# 第三步：使用自定义聚集函数
c.execute('select min_len(pass) from user_tb')
print(c.fetchone()[0])
# 提交事务
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

（九）创建比较函数

标准SQL语句中的order by 子句，可以对查询结果进行排序，这种排序使按照默认的排序规则进行的，如果要按业务相关规则进行排序，则需要创建自定义的比较函数。

使用数据库连接对象的create_collation(name, callable)方法可以注册一个自定义比较函数，其中参数name为比较函数的名字，callable指定自定义比较函数对应的函数。该函数包含两个参数，并对这两个参数进行大小比较，第一个参数更大函数返回正整数，否则返回负整数，如果相等则返回0.

需要注意的是，callable函数的参数以Python（bytes）字节串的形式传入，因此系统默认会以UTF-8字符集将字符串编码成字节串后传入callable函数。

假设，我们需要对user_tb中的pass字段进行排序，由于pass字段加密时将第一个字符和最后一个字符添加方括号，因此排序的时候需要将这两个字符去除，此时，我们可以自定义一个比较函数用来排序。

# 导入访问SQLite的模块
import sqlite3
​
​
# 定义函数，去掉字符串的第一个字符和最后一个字符后比较大小
def my_collate(st1, st2):
    if st1[1:-1] == st2[1:-1]:
        return 0
    elif st1[1:-1] > st2[1:-1]:
        return 1
    else:
        return -1
​
​
# 第一步：打开或创建数据库
conn = sqlite3.connect('first.db')
# 调用conn.create_collation()注册自定义比较函数：sub_cmp
conn.create_collation('sub_cmp', my_collate)
# 第二步：获取游标
c = conn.cursor()
# 第三步：在SQL语句中使用sub_cmp自定义比较函数
c.execute('select * from user_tb order by pass collate sub_cmp')
# 采用for循环遍历游标
for row in c:
    print(row)
# 提交事务
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

以上代码中，使用了游标的可迭代特性，由于游标本身是可迭代的，因此不需要使用fetchone()来逐行获取查询结果，而是使用for循环遍历查询结果集。

 

三、操作MySQL数据库

（一）下载和安装MySQL数据库

安装 MySQL 数据库与安装普通程序并没有太大的区别，关键是在配置 MySQL数据库时需要注意选择支持中文的编码集。下面简要介绍在 Windows 平台上下载和安装 MySQL 数据库的步骤：

1. 登录 http://dev.mysql.com/downloads/mysql/ 站点，下载 MySQL 数据库社区版（Community）的最新版本。建议下载该版本的 MySQL 安装文件。读者可根据自己所用的 Windows 平台，选择下载相应的 MSI Installer 安装文件。

   

    

2. 下载完成后，得到一个 mysql-installer-web-community-8.0.18.0.msi 文件。双击该文件，开始安装 MySQL 数据库。
3. 在出现的“License Agreement”对话框界面，该界面要求用户必须接受该协议才能安装 MySQL 数据库。勾选该界面下方的“I accept the license terms”复选框，然后单击“Next”按钮，显示如图所示的安装选项对话框。

   
   
   

4. 勾选“Custom”单选钮，然后单击“Next”按钮。在接下来的界面中可以选择安装 MySQL 所需的组件，并选择 MySQL 数据库及数据文件的安装路径，本教程选择将 MySQL 数据库和数据文件都安装在 D 盘下。选择安装 MySQL 服务器、文档和 Connector/Python（这就是 Python 连接 MySQL 的模块）。

   
   
   

5. 单击“Next”按钮，MySQL Installer 会检查系统环境是否满足安装 MySQL 数据库的要求。如果满足要求，则可以直接单击“Next”按钮开始安装；如果不符合条件，请根据 MySQL 提示先安装相应的系统组件，然后再重新安装 MySQL 数据库。
6. 开始安装 MySQL 数据库，安装完成后，会看到如图所示的成功安装对话框。

   
   

MySQL Server（数据库服务器）安装成功，Connector/Python 模块也安装成功，通过下方的 Details 信息可以看到 Python 的 MySQL 模块的安装位置。

注意，MySQL8.0 需要 Visual C++ 2015 Redistributable，而且不管操作系统是 32 位还是 64 位的，它始终需要 32 位的 Visual C++ 2015 Redistributable，否则会安装失败。

MySQL 数据库程序安装成功后，系统还要求配置 MySQL 数据库。单击对话框下方的“Next”按钮，开始配置 MySQL 数据库。选中“Standalone MySQL Server/Classic MySQL Replication”单选钮，即可进行更详细的配置。

两次单击“Next”按钮，将出现如图所示的对话框，允许用户设置 MySQL 的 root 账户密码，也允许添加更多的用户。

如果需要为 MySQL 数据库添加更多的用户，则可单击“Add User”按钮。设置完成后，单击“Next”按钮，将依次出现一系列对话框，但这些对话框对配置影响不大，直接单击“Next”按钮，直至 MySQL 数据库配置成功。

MySQL 可通过命令行客户端来管理 MySQL 数据库及数据库中的数据。经过上面几个步骤之后，应该在 Windows 的“开始”菜单中看到“MySQL”→“MySQL Server 8.0”→“MySQL 8.0 Command Line Client - Unicode”菜单项，单击该菜单项将启动 MySQL 的命令行客户端窗口，进入该窗口将会提示输入 root 账户密码。

由于 MySQL 默认使用 UTF-8 字符串，因此应该通过“MySQL 8.0 Command Line Client - Unicode”菜单项启动命令行工具，该工具将会使用 UTF-8 字符集。市面上有一个名为SQLyog 的工具程序提供了较好的图形用户界面来管理MySQL数据库中的数据。除此之外，MySQL也提供了MySQLAdministrator 工具来管理MySQL数据库。读者可以自行下载这两个工具，并使用这两个工具来管理MySQL数据库。但本教程依然推荐读者使用命令行工具，因为这种“恶劣”的工具会强制读者记住SQL命令。

在命令行客户端中输入为 root 账户设置的密码，进入 MySQL 数据库系统中，通过执行 SQL 命令就可以管理 MySQL 数据库了。

（二）使用pip工具管理模块

如果在安装MySQL数据库的时候忘记了安装Connector/Python模块，不要着急，我们还可以使用Python自带的pip工具来安装。

1、查看已安装的模块

pip list   查看已安装的所有模块

pip show packagename    查看指定模块的安装信息

pip show mysql-connector-python

2、卸载已安装的模块

pip unistall packagename

3、安装模块

pip install packagename

如：

pip install mysql-connector-python

将安装最新版本的mysql-connector-python

如果希望安装不同版本的模块，则可指定版本号。如：

pip install packagename == 8.0.12    # 安装指定版本

除了使用MySQL官方提供的Python模块来连接MySQL数据库之外，还有一个使用广泛的链接MySQL数据库的模块：MySQL-python，官方站点：https://pypi.org/project/MySQL-python/

（三）执行DDL语句

检查模块的全局属性：

import mysql.connector
mysql.connector.apilevel
'2.0'
mysql.connector.paramstyle
'pyformat'

可以看到mysql-connector-python遵循DB API 2.0规范，并且使用扩展的格式代码（pyformat）来代表参数。

# 导入访问MySQL的模块
import mysql.connector
​
# 第一步：连接数据库
conn = mysql.connector.connect(
    user='root',
    password='**********',
    host='localhost',
    port='3306',
    database='python',
    use_unicode=True)
# 第二步： 获取游标
c = conn.cursor()
# 第三步：执行DDL语句创建数据表
c.execute('''
    create table user_tb(user_id int primary key auto_increment,
    name varchar(255),
    pass varchar(255),
    gender varchar(255))
    ''')
# 执行DDL语句创建数据表
c.execute('''
    create table order_tb(order_id integer primary key auto_increment,
    item_name varchar(255),
    item_price double,
    item_number double,
    user_id int,
    foreign key(user_id) references user_tb(user_id))
    ''')
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

（四）执行DML语句

MySQL执行DML语句的方法与SQLite类似，使用游标的execute()方法即可：

# 导入访问MySQL的模块
import mysql.connector
​
# 第一步：连接数据库
conn = mysql.connector.connect(
    user='root',
    password='********',
    host='localhost',
    port='3306',
    database='python',
    use_unicode=True)
# 第二步：获取游标
c = conn.cursor()
# 第三步：调用execute()执行insert语句插入数据
c.execute('insert into user_tb values(null, %s, %s, %s)',
          ('张三', '123456', 'male'))
c.execute('insert into order_tb values(null, %s, %s, %s, %s)',
          ('鼠标', '34.2', '3', 1))
# 提交事务，将数据写入数据库
conn.commit()
# 第四步：关闭游标
c.close()
# 第五步：关闭连接
conn.close()
​

与SQLite不同的是，MySQL模块的参数使用%s作为占位符。

MySQL数据库模块的连接对象有一个autocommit属性，如果将这个属性的值设为True，将关闭该连接的事务支持，程序每次执行DML语句之后都会自动提交，所以就不需要再调用commit()方法来提交事务了。

# 导入MySQL模块
import mysql.connector
​
# 第一步：连接数据库
conn = mysql.connector.connect(
    user='root',
    password='********',
    host='localhost',
    port='3306',
    database='python',
    use_unicode=True)
# 将autocommit设置为True，关闭事务
conn.autocommit = True
# 第二步：获取游标
c = conn.cursor()
# 第三步：使用DML语句更新数据，修改将立刻写入数据库，不需要再调用commit
c.execute('update user_tb set pass=%s where user_id=%s', ('654321', 1))
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

（五）执行查询语句

# 导入MySQL模块
import mysql.connector
​
# 第一步：连接数据库
conn = mysql.connector.connect(
    user='root',
    password='********',
    host='localhost',
    port='3306',
    database='python',
    use_unicode=True)
# 第二步：获取游标
c = conn.cursor()
# 第三步：调用execute()方法执行select语句查询数据
c.execute('select * from user_tb where user_id > %s', (0,))
# 通过游标的description属性获取列信息
for col in (c.description):
    print(col[0], end='\t')
print('\n--------------------------')
# 使用for循环遍历游标中的结果集
for row in c:
    print(row)
    print(row[1] + '-->' + row[2])
# 第四步：关闭游标
c.close()
# 第五步：关闭连接
conn.close()
​

（六）调用存储过程

MySQL数据库模块为游标提供了一个非标准的callproc(self, procname, args=())方法，用于调用数据库存储过程。其中procname代表存储过程的名字，args参数用于为存储过程传入参数。

 

delimiter //
create procedure add_pro(a int, b int, out sum int)
begin
set sum = a + b;
end;
//

# 导入MySQL模块
import mysql.connector
​
# 第一步：连接数据库
conn = mysql.connector.connect(
    user='root',
    password='********',
    host='localhost',
    port='3306',
    database='python',
    use_unicode=True)
# 第二步： 获取游标
c = conn.cursor()
# 第三步：调用callproc()方法执行存储过程
# 虽然add_pro存储过程需要3个参数，但最后一个参数是传出参数
# 程序不会使用它的值，因此程序用一个0来占位置
result_args = c.callproc('add_pro', (5, 6, 0))
# 返回的result_args即包含输入参数的值，也包含传出参数的值
print(result_args)
# 如果指向访问传出参数的值，则可以直接访问result_args的第三个元素
print(result_args[2])
# 第四步：关闭游标
c.close()
# 第五步：关闭数据库连接
conn.close()
​

习题：

1.实现个人记账程序， 用户可以录入个人每天的消费记录（至少包括消费时间、消费地点、消费用途、消费金额等），并且可以根据各种条件查看消费记录，也可以修改消费记录，但不允许删除消费记录。

主要由两个文件实现，简单说明一下：

1.db.py处理sql逻辑

2.account_book.py主体窗口

# -*- coding:utf-8 -*-
# ==========================================================
# @Time      : 2019/10/19 18:42
# @Author    : ice_blue
# @Email     : zhouyong_0@hotmail.com
# @Project   : chapter13
# @file      : db.py
# @Software  : PyCharm
# @Version   : 1.0
# @website   : http://www.ib-top.com http://www.ib-top.cn
# @Desc      : 数据库处理代码，第一次执行时，把 “# create_table()”中的#去掉，执行完后，在加上
# =========================================================
# 导入SQLite模块
import sqlite3
​
​
def create_table():
    # 创建或打开数据库
    conn = sqlite3.connect('account_book.db')
    # 获取游标
    cur = conn.cursor()
    # 执行DDL语句创建数据表
    try:
        create_tb_sql = '''
        create table if not exists account_book
        (id integer primary key autoincrement,
        product_name text,
        product_date text,
        expense_place text,
        product_des text,
        product_price real
        )
        '''
        # 创建数据表
        cur.execute(create_tb_sql)
        print('创建数据表成功')
    except Exception as e:
        print("创建数据表失败！")
        print(e)
        # 关闭数据库连接
        conn.close()
        return False
    # 提交事务
    conn.commit()
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()
​
​
# 删除数据表
def drop_table():
    conn = sqlite3.connect('account_book.db')
    # 获取游标
    cur = conn.cursor()
    try:
        drop_sql = 'drop table account_book'
        cur.execute(drop_sql)
    except Exception as e:
        print("删除数据表失败")
        conn.close()
        return False
    conn.commit()
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()
​
​
# 查询数据，查询条件由调用程序传入
def select_sql(wherestr):
    conn = sqlite3.connect('account_book.db')
    # 获取游标
    cur = conn.cursor()
    # sqlstr
    if wherestr.lower().strip() == 'all':
        sqlstr = "select * from account_book"
    else:
        sqlstr = "select * from account_book where " + wherestr.strip()
    cur.execute(sqlstr)
    result = cur.fetchall()
    # 提交查询
    conn.commit()
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()
    return result
​
​
# 编辑记录,修改的参数通过调用者传递
def edit_data(id, product_name, product_date, expense_place, product_des,
              product_price):
    conn = sqlite3.connect('account_book.db')
    # 获取游标
    cur = conn.cursor()
    # 执行DML语句
    cur.execute(
        'update account_book set product_name=?, product_date=?, expense_place=?, product_des=?, product_price=? where id=?',
        (product_name, product_date, expense_place, product_des, product_price,
         id))
    # 获取受影响的记录数
    result = cur.rowcount
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()
    return result
​
​
# 插入数据
def insert_data(product_name, product_date, expense_place, product_des,
                product_price):
    conn = sqlite3.connect('account_book.db')
    # 获取游标
    cur = conn.cursor()
    # 执行SQL
    cur.execute(
        'insert into account_book (id, product_name, product_date, expense_place, product_des, product_price) values (null, ?, ?, ?, ?, ?)',
        (product_name, product_date, expense_place, product_des, product_price))
    conn.commit()
    result = cur.rowcount
    # 关闭游标
    cur.close()
    # 关闭数据库连接
    conn.close()
    return result
​
#create_table()

# -*- coding:utf-8 -*-
# ==========================================================
# @Time      : 2019/10/19 19:36
# @Author    : ice_blue
# @Email     : zhouyong_0@hotmail.com
# @Project   : chapter13
# @file      : account_book.py
# @Software  : PyCharm
# @Version   : 1.0
# @website   : http://www.ib-top.com http://www.ib-top.cn
# @Desc      : 生成用户界面，提供查询，插入，修改等功能
# =========================================================
# 导入tkinter包
from tkinter import *
from tkinter import Tk, Scrollbar, Frame
import tkinter
from tkinter import messagebox
from tkinter.ttk import Treeview
​
import db as sql
​
MENU_ITEMS = ['功能', '帮助']
MENU_GN_ITEMS = ['查询记录', '录入数据', '编辑数据', '退出程序']
MENU_HELP_ITEMS = ['关于', '帮助']
​
def select_action():
    frame = Frame(root)
    frame.place(x=0, y=10)
    # 添加滚动条
    scrollBar = Scrollbar(frame)
    scrollBar.pack(side=tkinter.RIGHT, fill=tkinter.Y)
    scrollBar_sp = Scrollbar(frame, orient='horizontal')
    # 数据展示组件,显示表头，带垂直滚动条
    tree = Treeview(frame, height=500,
                    columns=('c1', 'c2', 'c3', 'c4', 'c5', 'c6'),
                    show="headings",
                    yscrollcommand=scrollBar.set)
    # 设置每列宽度和对齐方式
    tree.column('c1', width=20, anchor='center')
    tree.column('c2', width=100, anchor='center')
    tree.column('c3', width=200, anchor='center')
    tree.column('c4', width=200, anchor='center')
    tree.column('c5', width=300, anchor='center')
    tree.column('c6', width=100, anchor='center')
    # 设置每列标头标题文本
    tree.heading('c1', text='序号')
    tree.heading('c2', text='名称')
    tree.heading('c3', text='消费时间')
    tree.heading('c4', text='消费地点')
    tree.heading('c5', text='用途')
    tree.heading('c6', text='价格')
    # 设置数据显示
    tree.pack(side=tkinter.LEFT, fill=tkinter.Y)
    def treeviewClick(event):
        # 获取行的索引值
        iid = tree.identify_row(event.y)
        xy = tree.identify_element(event.x, event.y)
        s = tree.selection()
        print(s)
        pass
    # 绑定鼠标单击行事件处理程序
    tree.bind('<Button-1>', treeviewClick)
    # 显示数据
    res = sql.select_sql('all')
    std = []
    for id, product_name, product_date, expense_place, product_des, product_price in res:
        std.append((id, product_name, product_date, expense_place, product_des, product_price))
    for i in range(len(res)):
        tree.insert("", i, values=(std[i][0],std[i][1],std[i][2],std[i][3],std[i][4],std[i][5]))
def insert_action():
    def save_account():
        product_name = ent_name.get()
        product_date = ent_date.get()
        expense_place = ent_place.get()
        product_des = ent_des.get()
        product_price = ent_price.get()
        try:
            sql.insert_data(product_name, product_date, expense_place, product_des, product_price)
            messagebox.showinfo("info", "保存成功")
        except:
            messagebox.showerror("info", "写入数据失败")
    def cancel_account():
        pass
    ins_dat = Tk()
    ins_dat.title('数据录入')
    # 创建标签
    lab_name = Label(ins_dat, text='名称：')
    # tkinter没有现成的日历控件，先用文本替代，后期再进行完善
    lab_date = Label(ins_dat, text='消费日期（yyyy-MM-dd格式）：')
    lab_place = Label(ins_dat, text='消费地点：')
    lab_des = Label(ins_dat, text='用途：')
    lab_price = Label(ins_dat, text='价格：')
    # 输入框
    ent_name = Entry(ins_dat)
    ent_date = Entry(ins_dat)
    ent_place = Entry(ins_dat)
    ent_des = Entry(ins_dat)
    ent_price = Entry(ins_dat)
    # 按钮
    btn = Button(ins_dat, text='保存', command=save_account)
    btn2 = Button(ins_dat, text='取消', command=cancel_account)
    # 使用grid布局，创建插入数据界面
    lab_name.grid(row=0, column=0, sticky=W)
    lab_date.grid(row=1, column=0, sticky=W)
    lab_place.grid(row=2, column=0, sticky=W)
    lab_des.grid(row=3, column=0, sticky=W)
    lab_price.grid(row=4, column=0, sticky=W)
​
    ent_name.grid(row=0, column=1, stick=W)
    ent_date.grid(row=1, column=1, sticky=W)
    ent_place.grid(row=2, column=1, sticky=W)
    ent_des.grid(row=3, column=1, sticky=W)
    ent_price.grid(row=4, column=1, sticky=W)
​
    btn.grid(row=5, column=1, sticky=W)
    btn2.grid(row=5, column=2, sticky=W)
    # 开启事件循环
    ins_dat.mainloop()
def edit_action():
    def edit_fd_info():
        id = ent_id.get()
        res = sql.select_sql('id='+id)
        for id, product_name, product_date, expense_place, product_des, product_price in res:
            ent_name_ed.insert(0, product_name)
            ent_date_ed.insert(0, product_date)
            ent_place_ed.insert(0, expense_place)
            ent_des_ed.insert(0, product_des)
            ent_price_ed.insert(0, product_price)
    def save_info():
        id = ent_id.get()
        product_name = ent_name_ed.get()
        product_date = ent_date_ed.get()
        expense_place = ent_place_ed.get()
        product_des = ent_des_ed.get()
        product_price = ent_price_ed.get()
        try:
            sql.edit_data(id, product_name, product_date, expense_place, product_des, product_price)
            messagebox.showinfo('info', '修改成功！')
        except Exception as e:
            messagebox.showerror('info', e)
    # 初始化界面
    edit_dat = Tk()
    edit_dat.title('修改数据')
​
    lab_id = Label(edit_dat, text='请输入要修改数据的ID：')
    ent_id = Entry(edit_dat)
    btn_fd = Button(edit_dat, text='查询', command=edit_fd_info)
    lab_id.grid(row=0, column=0, sticky=W)
    ent_id.grid(row=0, column=1, sticky=W)
    btn_fd.grid(row=0, column=2, sticky=W)
​
    lab_name = Label(edit_dat, text='名称：')
    ent_name_ed = Entry(edit_dat, textvariable=edit_fd_info)
    lab_name.grid(row=2, column=0, sticky=W)
    ent_name_ed.grid(row=2, column=1, sticky=W)
​
    lab_date = Label(edit_dat, text='消费日期：')
    ent_date_ed = Entry(edit_dat, textvariable=edit_fd_info)
    lab_date.grid(row=3, column=0, sticky=W)
    ent_date_ed.grid(row=3, column=1, sticky=W)
​
    lab_place = Label(edit_dat, text='消费地点：')
    ent_place_ed = Entry(edit_dat, textvariable=edit_fd_info)
    lab_place.grid(row=4, column=0, sticky=W)
    ent_place_ed.grid(row=4, column=1, sticky=W)
​
    lab_des = Label(edit_dat, text='用途：')
    ent_des_ed = Entry(edit_dat, textvariable=edit_fd_info)
    lab_des.grid(row=5, column=0, sticky=W)
    ent_des_ed.grid(row=5, column=1, sticky=W)
​
    lab_price = Label(edit_dat, text='价格：')
    ent_price_ed = Entry(edit_dat, textvariable=edit_fd_info)
    lab_price.grid(row=6, column=0, sticky=W)
    ent_price_ed.grid(row=6, column=1, sticky=W)
    btn_sv = Button(edit_dat, text='保存', command=save_info)
    btn_sv.grid(row=7, column=0)
​
    # 开启事件循环
    edit_dat.mainloop()
def about_me():
    messagebox.showinfo('info', '私人账本 V1.0')
def help_me():
    messagebox.showinfo('info', '进入功能菜单使用相应功能')
def get_tk():
    '''获取一个TK对象'''
    return tkinter.Tk()
def set_tk_title(tk, title):
    '''指定窗口的标题'''
    if title is not None and title !='':
        tk.title(title)
    else:
        tk.title('私人账本 V1.0')
def set_tk_geometry(tk, size):
    '''设置窗口大小，格式为widthxheight'''
    if size is not None and size != '':
        tk.geometry(size)
    else:
        tk.geometry('800x600')
def get_menu(tk):
    '''获取菜单条'''
    return Menu(tk)
def menu_gn(menubar):
    '''定义功能菜单'''
    gnmenu = Menu(menubar, tearoff=1)
    gnmenu.add_command(label=MENU_GN_ITEMS[0], command=select_action)
    gnmenu.add_command(label=MENU_GN_ITEMS[1], command=insert_action)
    gnmenu.add_command(label=MENU_GN_ITEMS[2], command=edit_action)
    gnmenu.add_separator()
    gnmenu.add_command(label=MENU_GN_ITEMS[3], command=root.destroy)
    menubar.add_cascade(label=MENU_ITEMS[0], menu=gnmenu)
def menu_help(menubar):
    '''定义帮助菜单'''
    help_menu = Menu(menubar, tearoff=1)
    help_menu.add_command(label=MENU_HELP_ITEMS[0], command=about_me)
    help_menu.add_command(label=MENU_HELP_ITEMS[1], command=help_me)
    menubar.add_cascade(label=MENU_ITEMS[1], menu=help_menu)
def init_menu_bar(menubar):
    '''初始化菜单条'''
    menu_gn(menubar)
    menu_help(menubar)
    # 进入界面显示已有数据
    select_action()
​
# 获得窗口对象
root = get_tk()
# 设置窗口大小
set_tk_geometry(root, '')
# 设置窗口标题
set_tk_title(root, '')
# 获取菜单对象
menubar = get_menu(root)
# 初始化菜单
init_menu_bar(menubar)
# 加载菜单配置
root.config(menu=menubar)
# 启动界面
mainloop()
​
​
​

2.设计一个数据表用于保存图书信息，需要保存图书的书名、价格、作者、出版社、封面（图片）等信息。开发一个带界面的程序，用户可以向该数据表中添加记录、删除记录，也可以修改已有的图书记录， 并可以根据书名、价格、作者等条件查询图书。

用wxpython实现，后台数据库为mysql数据库，采用的pymysql连接数据库。

 

# -*- coding:utf-8 -*-
# ==========================================================
# @Time      : 2019/10/19 21:36
# @Email     : zhouyong_0@hotmail.com
# @Project   : BMS
# @file      : book.py
# @Software  : PyCharm
# @Version   : 1.0
# @website   : http://www.ib-top.com http://www.ib-top.cn
# @Desc      : 图书类
# =========================================================
from datetime import *
from pathlib import Path
__metaclass__ = type
​
​
class Book:
    '''图书信息类，包括图书的书名、价格、作者、出版社、封面（图片）等信息'''
​
    # 初始化实例
    def __init__(self,
                 bookName="",
                 author="",
                 price=0.0,
                 publish="",
                 cover="",
                 des=""):
        self.bookName = bookName  # 书名
        self.author = author  # 作者
        self.price = price  # 价格
        self.publish = publish  # 出版社
        self.cover = cover  # 封面图片路径
        self.des = des  # 简介
        self.add_date = date.today()  # 图书添加日期
​
    def setBookName(self, name):
        self.bookName = name
​
    def getBookName(self):
        return self.bookName
​
    def setAuthor(self, author):
        self.author = author
​
    def getAuthor(self):
        return self.author
​
    def setPrice(self, price):
        if isinstance(price, float):
            self.price = float(price)
        else:
            raise ValueError('价格必须是浮点数')
​
    def getPrice(self):
        return self.price
​
    def setPublish(self, publish):
        self.publish = publish
​
    def getPublish(self):
        return self.publish
​
    def setCover(self, cover):
        if Path(cover).exists():
            self.cover = cover
        else:
            raise ValueError('文件不存在')
​
    def getCover(self):
        return self.cover
​
    def setDes(self, des):
        self.des = des
​
    def getDes(self):
        return self.des
​
    def getAddDate(self):
        return self.add_date
​
​
if __name__ == '__main__':
    mybook = Book()
    print(mybook.add_date)
​

# -*- coding:utf-8 -*-
# ==========================================================
# @Time      : 2019/10/19 22:02
# @Email     : zhouyong_0@hotmail.com
# @Project   : BMS
# @file      : db.py
# @Software  : PyCharm
# @Version   : 1.0
# @website   : http://www.ib-top.com http://www.ib-top.cn
# @Desc      : 数据库操作类
# =========================================================
import pymysql
import datetime
​
__metaclass__ = type
​
​
class DBHelper:
​
    def getConn(self):
        '''获取操作数据库的cursor游标，首先建立连接，需要服务器地址，
        端口号，用户名，密码等信息
        为了能使用中文，得加上编码方式
        '''
        conn = pymysql.connect(
            host="localhost",
            port=3306,
            user="root",
            password="********",
            db="books",
            charset="utf8")
        return conn
​
    # 插入数据
    def insertBook(self, book):
        '''向数据库中的book_info表插入图书信息，book为Book类对象，包含图书信息'''
        sql = "insert into book_info(name, price, author, publish, description, cover, add_date) values(%s, %s, %s, %s, %s, %s, %s)"
        # 获得数据库连接对象
        conn = self.getConn()
        if conn == None:
            return
        # 获取游标对象
        cur = conn.cursor()
        print(book.getPrice())
        # 执行SQL语句
        cur.execute(sql, (book.getBookName(), book.getPrice(), book.getAuthor(),
                          book.getPublish(), book.getDes(), book.getCover(),
                          book.getAddDate()))
        # 提交事务
        conn.commit()
        # 关闭游标
        cur.close()
        # 关闭数据库连接
        conn.close()
        new_id = cur.lastrowid
        print("新插入键值id为：", new_id)
        return new_id
​
    # 获取全部图书信息
    def getAllBook(self):
        '''返回数据库中book_info表的全部数据'''
        sql = "select * from book_info"
        # 获取数据库连接对象
        conn = self.getConn()
        if conn == None:
            return
        # 获取游标对象
        cur = conn.cursor()
        rownum = cur.execute(sql)  # 执行并返回找到的行数
        # 获取查询结果
        rows = cur.fetchall()
        book_list = []
        for item in rows:
            bitem = (item[0], item[1], item[2], item[3], item[4], item[5],
                     item[6], item[7])
            book_list.append(bitem)
        conn.commit()
        cur.close()
        conn.close()
        return book_list
​
    def getBookById(self, bookid):
        '''根据图书id查询图书信息'''
        sql = "select name, price, author, publish, description, cover, add_date from book_info where id=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (bookid,))  # 参数以元组的形式给出
        row = cur.fetchone()
        conn.commit()
        cur.close()
        conn.close()
        return row
​
    def getBookByName(self, bookName):
        '''根据图书书名查询图书信息'''
        sql = "select * from book_info where name=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (bookName,))  # 参数以元组的形式给出
        rows = cur.fetchall()
        book_list = []
        for item in rows:
            bitem = (item[0], item[1], item[2], item[3], item[4], item[5],
                     item[6], item[7])
            book_list.append(bitem)
        conn.commit()
        cur.close()
        conn.close()
        return book_list
​
    def getBookByAuthor(self, bookAuthor):
        '''根据图书作者查询图书信息'''
        sql = "select * from book_info where author=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (bookAuthor,))  # 参数以元组的形式给出
        rows = cur.fetchall()
        book_list = []
        for item in rows:
            bitem = (item[0], item[1], item[2], item[3], item[4], item[5],
                     item[6], item[7])
            book_list.append(bitem)
        conn.commit()
        cur.close()
        conn.close()
        return book_list
​
    def getBookByPrice(self, bookPrice):
        '''根据图书作者查询图书信息'''
        sql = "select * from book_info where price=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (bookPrice,))  # 参数以元组的形式给出
        rows = cur.fetchall()
        book_list = []
        for item in rows:
            bitem = (item[0], item[1], item[2], item[3], item[4], item[5],
                     item[6], item[7])
            book_list.append(bitem)
        conn.commit()
        cur.close()
        conn.close()
        return book_list
​
    def saveUpdate(self, bookid, book):
        '''用book对象来修改id为bookid的图书信息'''
        sql = "update book_info set name=%s, price=%s, author=%s, publish=%s, description=%s, cover=%s, add_date=%s where id=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (book.getBookName(), book.getPrice(), book.getAuthor(),
                          book.getPublish(), book.getDes(), book.getCover(),
                          book.getAddDate(), bookid))
        conn.commit()
        cur.close()
        conn.close()
​
    def deleteBook(self, bookid):
        '''根据图书ID来删除图书信息'''
        sql = "delete from book_info where id=%s"
        conn = self.getConn()
        if conn == None:
            return
        cur = conn.cursor()
        cur.execute(sql, (bookid,))
        conn.commit()
        cur.close()
        conn.close()
​
​
if __name__ == '__main__':
    db = DBHelper()
    book_list = db.getAllBook()
    for item in book_list:
        print(item)
​

# -*- coding:utf-8 -*-
# ==========================================================
# @Time      : 2019/10/19 22:39
# @Email     : zhouyong_0@hotmail.com
# @Project   : BMS
# @file      : bms_main.py
# @Software  : PyCharm
# @Version   : 1.0
# @website   : http://www.ib-top.com http://www.ib-top.cn
# @Desc      : 一个图书管理系统，能够实现图书的增、删、改、查，基于mysql数据库和wxPython
# =========================================================
import os
import shutil
import wx
​
from book import *
from db import *
​
​
class AddFrame(wx.Frame):
    '''添加图书弹出的小窗口'''
    def __init__(self, parent, title):
        '''初始化该小窗口的布局'''
        self.mainframe = parent
        # 生成一个800*300的框
        wx.Frame.__init__(self, parent, title=title, size=(800, 600))
        # 创建容器
        self.panel = wx.Panel(self, pos=(0, 0), size=(750, 550))
        self.panel.SetBackgroundColour("#FFFFFF")    # 设置背景色为白色
​
        # 添加六个编辑框，分别用来编辑书名、价格、作者、出版社、简介、封面图片
        bookName_tip = wx.StaticText(self.panel, label="书名：", pos=(5, 8), size=(100, 25))
        bookName_tip.SetBackgroundColour("#FFFFFF")
        bookName_text = wx.TextCtrl(self.panel, pos=(105, 5), size=(340, 25))
        self.name = bookName_text
​
        bookPrice_tip = wx.StaticText(self.panel, label="价格：", pos=(5, 38), size=(100, 25))
        bookPrice_tip.SetBackgroundColour("#FFFFFF")
        bookPrice_text = wx.TextCtrl(self.panel, pos=(105, 35), size=(340, 25))
        self.price = bookPrice_text
​
        author_tip = wx.StaticText(self.panel, label="作者：", pos=(5, 68), size=(100, 25))
        author_tip.SetBackgroundColour("#FFFFFF")
        author_text = wx.TextCtrl(self.panel, pos=(105, 65), size=(340, 25))
        self.author = author_text
​
        publish_tip = wx.StaticText(self.panel, label="出版社：", pos=(5, 98), size=(100, 25))
        publish_tip.SetBackgroundColour("#FFFFFF")
        publish_text = wx.TextCtrl(self.panel, pos=(105, 95), size=(340, 25))
        self.publish = publish_text
​
        cover_tip = wx.StaticText(self.panel, label="封面图片：", pos=(5, 128), size=(100, 25))
        cover_tip.SetBackgroundColour("#FFFFFF")
        cover_text = wx.TextCtrl(self.panel, pos=(105, 125), size=(340, 25))
        cover_text.SetEditable(False)
        cover_btn = wx.Button(self.panel, label="选择文件", pos=(450, 125))
        self.Bind(wx.EVT_BUTTON, self.OnButton1, cover_btn)
        if cover_text.GetValue() == '':
            pass
        else:
            cover_img = wx.Image(cover_text.GetValue(), wx.BITMAP_TYPE_ANY)
            cover_img.Scale(100, 100)
            wx.StaticBitmap(self.panel, -1, wx.BitmapFromImage(cover_img), pos=(530, 125))
        self.cover = cover_text
​
        des_tip = wx.StaticText(self.panel, label="简介：", pos=(5, 198), size=(100, 25))
        des_tip.SetBackgroundColour("#FFFFFF")
        des_text = wx.TextCtrl(self.panel, pos=(105, 225), size=(340, 100), style=wx.TE_MULTILINE)
        self.des = des_text
​
        # 添加按钮
        save_button = wx.Button(self.panel, label="保存信息", pos=(160, 500))
        self.Bind(wx.EVT_BUTTON, self.saveBook, save_button)
​
        # 需要用到的数据库接口
        self.dbhelper = DBHelper()
​
    def OnButton1(self, evt):
        cover_imgfile = wx.FileDialog(self.panel, "选择文件", os.getcwd(), "", "All Files(*.*)|*.*", wx.FD_OPEN)
        if cover_imgfile.ShowModal() == wx.ID_OK:
            file = Path(cover_imgfile.GetPath())
            copyfile = Path('./cover_img')
            shutil.copy(file, copyfile)
            file1 = str(copyfile) + os.sep + file.name
            cover_img = wx.Image(file1, wx.BITMAP_TYPE_ANY)
            bitmap = cover_img.Scale(cover_img.GetWidth()/20, cover_img.GetHeight()/20).ConvertToBitmap()
            wx.StaticBitmap(self.panel, -1, bitmap, pos=(550, 125))
            self.cover.SetValue(file1)
​
    def saveBook(self, evt):
        '''第一步：获取text中文本，第二步，连接到数据库，第三步插入并获得主键，第四步添加到ListCtrl中'''
        bookName = self.name.GetValue()
        price = self.price.GetValue()
        author = self.author.GetValue()
        publish = self.publish.GetValue()
        cover = self.cover.GetValue()
        des = self.des.GetValue()
​
        print("书名：" + bookName)
        if bookName == "" or price == "" or author == "" or publish == "" or cover == "":
            print("提示错误")
            warn = wx.MessageDialog(self, message="所有信息不能为空！！！", caption="错误警告", sytle=wx.YES_DEFAULT|wx.ICON_ERROR)
            warn.ShowModal()    # 提示错误
            warn.Destroy()
            return
        else:
            print("开始插入数据")
            book = Book(bookName, author, price, publish, cover, des)
            print(book)
            book_id = self.dbhelper.insertBook(book)
            self.mainframe.addToList(book_id, book)
        self.Destroy()
class UpdateFrame(wx.Frame):
    def __init__(self, parent, title, select_id):
        '''初始化更新图书信息界面总布局'''
        wx.Frame(parent, title=title, size=(800, 600))
​
        # 用来调用父frame，便于更新
        self.mainframe = parent
        # 生成一个800*550的框
        wx.Frame.__init__(self, parent, title=title, size=(800, 550))
​
        self.panel = wx.Panel(self, pos=(0, 0), size=(800, 550))
        self.panel.SetBackgroundColour("#FFFFFF")
​
        # 添加六个编辑框，分别用来编辑书名、价格、作者、出版社、简介、封面图片
        bookName_tip = wx.StaticText(self.panel, label="书名：", pos=(5, 8), size=(100, 25))
        bookName_tip.SetBackgroundColour("#FFFFFF")
        bookName_text = wx.TextCtrl(self.panel, pos=(105, 5), size=(340, 25))
        self.name = bookName_text
​
        bookPrice_tip = wx.StaticText(self.panel, label="价格：", pos=(5, 38), size=(100, 25))
        bookPrice_tip.SetBackgroundColour("#FFFFFF")
        bookPrice_text = wx.TextCtrl(self.panel, pos=(105, 35), size=(340, 25))
        self.price = bookPrice_text
​
        author_tip = wx.StaticText(self.panel, label="作者：", pos=(5, 68), size=(100, 25))
        author_tip.SetBackgroundColour("#FFFFFF")
        author_text = wx.TextCtrl(self.panel, pos=(105, 65), size=(340, 25))
        self.author = author_text
​
        publish_tip = wx.StaticText(self.panel, label="出版社：", pos=(5, 98), size=(100, 25))
        publish_tip.SetBackgroundColour("#FFFFFF")
        publish_text = wx.TextCtrl(self.panel, pos=(105, 95), size=(340, 25))
        self.publish = publish_text
​
        cover_tip = wx.StaticText(self.panel, label="封面图片：", pos=(5, 128), size=(100, 25))
        cover_tip.SetBackgroundColour("#FFFFFF")
        cover_text = wx.TextCtrl(self.panel, pos=(105, 125), size=(340, 25))
        cover_text.SetEditable(False)
        cover_btn = wx.Button(self.panel, label="选择文件", pos=(450, 125))
        self.Bind(wx.EVT_BUTTON, self.OnButton1, cover_btn)
        if cover_text.GetValue() == '':
            pass
        else:
            cover_img = wx.Image(cover_text.GetValue(), wx.BITMAP_TYPE_ANY)
            cover_img.Scale(100, 100)
            wx.StaticBitmap(self.panel, -1, wx.BitmapFromImage(cover_img), pos=(530, 125))
        self.cover = cover_text
​
        des_tip = wx.StaticText(self.panel, label="简介：", pos=(5, 198), size=(100, 25))
        des_tip.SetBackgroundColour("#FFFFFF")
        des_text = wx.TextCtrl(self.panel, pos=(105, 225), size=(340, 100), style=wx.TE_MULTILINE)
        self.des = des_text
​
        # 添加按钮
        save_button = wx.Button(self.panel, label="保存信息", pos=(160, 400))
        self.Bind(wx.EVT_BUTTON, self.saveUpdate, save_button)
​
        # 选中的id和bookid
        self.select_id = select_id
        self.bookid = self.mainframe.book_list.GetItem(select_id, 0).Text    # 获取第select_id行的第0列的值
        print(select_id, self.bookid)
        # 需要用到的数据库接口
        self.dbhelper = DBHelper()
        self.showAllText()    # 展现所有的text原来取值
​
    def OnButton1(self, evt):
        cover_imgfile = wx.FileDialog(self.panel, "选择文件", os.getcwd(), "", "All Files(*.*)|*.*", wx.FD_OPEN)
        if cover_imgfile.ShowModal() == wx.ID_OK:
            file = Path(cover_imgfile.GetPath())
            copyfile = Path('./cover_img')
            shutil.copy(file, copyfile)
            file1 = str(copyfile) + os.sep + file.name
            cover_img = wx.Image(file1, wx.BITMAP_TYPE_ANY)
            bitmap = cover_img.Scale(cover_img.GetWidth() / 20, cover_img.GetHeight() / 20).ConvertToBitmap()
            wx.StaticBitmap(self.panel, -1, bitmap, pos=(550, 125))
            self.cover.SetValue(file1)
​
    def showAllText(self):
        '''显示图书原始信息'''
        data = self.dbhelper.getBookById(self.bookid)    # 通过id获取图书信息
        self.name.SetValue(data[0])
        self.price.SetValue(str(data[1]))
        self.author.SetValue(data[2])
        self.publish.SetValue(data[3])
        self.cover.SetValue(data[5])
        cover_img = wx.Image(data[5], wx.BITMAP_TYPE_ANY)
        bitmap = cover_img.Scale(cover_img.GetWidth() / 20, cover_img.GetHeight() / 20).ConvertToBitmap()
        wx.StaticBitmap(self.panel, -1, bitmap, pos=(550, 125))
        self.des.SetValue(data[4])
    def saveUpdate(self, evt):
        '''保存修改后的值'''
        bookName = self.name.GetValue()
        price = self.price.GetValue()
        author = self.author.GetValue()
        publish = self.publish.GetValue()
        cover = self.cover.GetValue()
        des = self.des.GetValue()
​
        if bookName == "" or price == "" or author == "" or publish == "" or cover == "":
            print("提示错误")
            warn = wx.MessageDialog(self, message="所有信息不能为空！！！", caption="错误警告", sytle=wx.YES_DEFAULT|wx.ICON_ERROR)
            warn.ShowModal()    # 提示错误
            warn.Destroy()
            return
        else:
            print("开始将修改后的数据保存到数据库中")
            book = Book(bookName, author, price, publish, cover, des)
            self.dbhelper.saveUpdate(self.bookid, book)
            self.mainframe.book_list.SetItem(self.select_id, 1, bookName)
            self.mainframe.book_list.SetItem(self.select_id, 2, str(price))
            self.mainframe.book_list.SetItem(self.select_id, 3, author)
            self.mainframe.book_list.SetItem(self.select_id, 4, publish)
​
        self.Destroy()
class ShowFrame(wx.Frame):
    '''用来显示图书信息'''
    def __init__(self, parent, title, select_id):
        '''初始化该小窗口布局'''
        self.mainframe = parent
        # 生成一个800*600的框
        wx.Frame.__init__(self, parent, title=title, size=(800, 600))
        self.panel = wx.Panel(self, pos=(0, 0), size=(800, 550))
        self.panel.SetBackgroundColour("#FFFFFF")
​
        # 添加六个编辑框，分别用来编辑书名、价格、作者、出版社、简介、封面图片
        bookName_tip = wx.StaticText(self.panel, label="书名：", pos=(5, 8), size=(100, 25))
        bookName_tip.SetBackgroundColour("#FFFFFF")
        bookName_text = wx.TextCtrl(self.panel, pos=(105, 5), size=(340, 25))
        bookName_text.SetEditable(False)
        self.name = bookName_text
​
        bookPrice_tip = wx.StaticText(self.panel, label="价格：", pos=(5, 38), size=(100, 25))
        bookPrice_tip.SetBackgroundColour("#FFFFFF")
        bookPrice_text = wx.TextCtrl(self.panel, pos=(105, 35), size=(340, 25))
        bookPrice_text.SetEditable(False)
        self.price = bookPrice_text
​
        author_tip = wx.StaticText(self.panel, label="作者：", pos=(5, 68), size=(100, 25))
        author_tip.SetBackgroundColour("#FFFFFF")
        author_text = wx.TextCtrl(self.panel, pos=(105, 65), size=(340, 25))
        author_text.SetEditable(False)
        self.author = author_text
​
        publish_tip = wx.StaticText(self.panel, label="出版社：", pos=(5, 98), size=(100, 25))
        publish_tip.SetBackgroundColour("#FFFFFF")
        publish_text = wx.TextCtrl(self.panel, pos=(105, 95), size=(340, 25))
        publish_text.SetEditable(False)
        self.publish = publish_text
​
        cover_tip = wx.StaticText(self.panel, label="封面图片：", pos=(5, 128), size=(100, 25))
        cover_tip.SetBackgroundColour("#FFFFFF")
        cover_text = wx.TextCtrl(self.panel, pos=(105, 125), size=(340, 25))
        cover_text.SetEditable(False)
        self.cover = cover_text
​
        des_tip = wx.StaticText(self.panel, label="简介：", pos=(5, 198), size=(100, 25))
        des_tip.SetBackgroundColour("#FFFFFF")
        des_text = wx.TextCtrl(self.panel, pos=(105, 225), size=(340, 100), style=wx.TE_MULTILINE)
        des_text.SetEditable(False)
        self.des = des_text
​
        # 选中的id和bookid
        self.select_id = select_id
        self.bookid = self.mainframe.book_list.GetItem(select_id, 0).Text
        # 需要用到的数据库接口
        self.dbhelper = DBHelper()
        self.showAllText()
​
    def showAllText(self):
        '''显示图书原始信息'''
        data = self.dbhelper.getBookById(self.bookid)    # 通过id获取图书信息
        self.name.SetValue(data[0])
        self.price.SetValue(str(data[1]))
        self.author.SetValue(data[2])
        self.publish.SetValue(data[3])
        self.cover.SetValue(data[5])
        if data[5] == '':
            pass
        else:
            cover_img = wx.Image(data[5], wx.BITMAP_TYPE_ANY)
            bitmap = cover_img.Scale(cover_img.GetWidth() / 20, cover_img.GetHeight() / 20).ConvertToBitmap()
            wx.StaticBitmap(self.panel, -1, bitmap, pos=(550, 125))
        self.des.SetValue(data[4])
class LibraryFrame(wx.Frame):
    def __init__(self, parent, title):
        '''初始化系统总体布局，包括各种控件'''
        # 生成一个800*700的frame框
        wx.Frame.__init__(self, parent, title=title, size=(800, 700))
        # 定义一个网格布局，两行一列
        self.main_layout = wx.BoxSizer(wx.VERTICAL)
​
        # 生成一个列表
        self.book_list = wx.ListCtrl(self, -1, size=(800, 600), style=wx.LC_REPORT|wx.LC_HRULES|wx.LC_VRULES)
        # 列表有散列，分别是ID，书名，价格，作者，出版社，添加日期
        self.book_list.InsertColumn(0, "ID")
        self.book_list.InsertColumn(1, "书名")
        self.book_list.InsertColumn(2, "价格")
        self.book_list.InsertColumn(3, "作者")
        self.book_list.InsertColumn(4, "出版社")
        self.book_list.InsertColumn(5, "添加日期")
        # 设置各列宽度
        self.book_list.SetColumnWidth(0, 60)
        self.book_list.SetColumnWidth(0, 100)
        self.book_list.SetColumnWidth(0, 50)
        self.book_list.SetColumnWidth(0, 100)
        self.book_list.SetColumnWidth(0, 90)
        self.book_list.SetColumnWidth(0, 200)
        # 添加一组按钮，实现增删改查，用一个panel来管理该组按钮的布局
        self.panel = wx.Panel(self, pos=(0, 600), size=(800, 100))
        # 定义一组按钮
        add_button = wx.Button(self.panel, label="添加", pos=(300, 15), size=(60, 30))
        del_button = wx.Button(self.panel, label="删除", pos=(400, 15), size=(60, 30))
        update_button = wx.Button(self.panel, label="修改", pos=(500, 15), size=(60, 30))
        query_button = wx.Button(self.panel, label="查看", pos=(600, 15), size=(60, 30))
        # 为按钮绑定时间函数，第一个参数为默认参数，指明为按钮事件，第二个为事件函数名，第三个为按钮名
        self.Bind(wx.EVT_BUTTON, self.addBook, add_button)
        self.Bind(wx.EVT_BUTTON, self.delBook, del_button)
        self.Bind(wx.EVT_BUTTON, self.updateBook, update_button)
        self.Bind(wx.EVT_BUTTON, self.queryBook, query_button)
        #将列表和panel添加到主窗口
        self.main_layout.Add(self.book_list, 3)
        self.main_layout.Add(self.panel, 1)
        self.SetSizer(self.main_layout)
        # 添加数据库操作对象
        self.dbhelper = DBHelper()
        datas = self.dbhelper.getAllBook()
​
        for data in datas:
            index = self.book_list.InsertItem(self.book_list.GetItemCount(), str(data[0]))
            self.book_list.SetItem(index, 1, data[1])
            self.book_list.SetItem(index, 2, str(data[2]))
            self.book_list.SetItem(index, 3, data[3])
            self.book_list.SetItem(index, 4, data[4])
            self.book_list.SetItem(index, 5, str(data[7]))
​
    def addBook(self, evt):
        '''添加书籍按钮，弹出添加书籍框'''
        add_f = AddFrame(self, "添加图书")
        add_f.Show(True)
    def delBook(self, evt):
        '''删除图书按钮，先选中，然后删除'''
        selectId = self.book_list.GetFirstSelected()
        if selectId == -1:
            warn = wx.MessageDialog(self, message="未选中任何条目！！！", caption="错误警告", sytle=wx.YES_DEFAULT|wx.ICON_ERROR)
            warn.ShowModal()
            warn.Destroy()
            return
        else:
            bookid = self.book_list.GetItem(selectId, 0).Text
            self.book_list.DeleteItem(selectId)
            self.dbhelper.deleteBook(bookid)
    def updateBook(self, evt):
        '''修改按钮响应时间，点击修改按钮，弹出修改框'''
        selectId = self.book_list.GetFirstSelected()
        if selectId == -1:
            warn = wx.MessageDialog(self, message="未选中任何条目！！！", caption="错误警告", sytle=wx.YES_DEFAULT|wx.ICON_ERROR)
            warn.ShowModal()
            warn.Destroy()
            return
        else:
            update_f = UpdateFrame(self, "修改图书信息", selectId)
            update_f.Show(True)
    def queryBook(self, evt):
        '''查看按钮事件'''
        selectId = self.book_list.GetFirstSelected()
        if selectId == -1:
            warn = wx.MessageDialog(self, message="未选中任何条目！！！", caption="错误警告", sytle=wx.YES_DEFAULT|wx.ICON_ERROR)
            warn.ShowModal()
            warn.Destroy()
            return
        else:
            show_f = ShowFrame(self, "查看图书", selectId)
            show_f.Show(True)
    def addToList(self, id, book):
        index = self.book_list.InsertItem(self.book_list.GetItemCount(), str(id))
        self.book_list.SetItem(index, 1, book.getBookName())
        self.book_list.SetItem(index, 2, str(book.getPrice()))
        self.book_list.SetItem(index, 3, book.getAuthor())
        self.book_list.SetItem(index, 4, book.getPublish())
        self.book_list.SetItem(index, 5, str(book.getAddDate()))
AppBaseClass = wx.App
class LibraryApp(AppBaseClass):
    def OnInit(self):
        frame = LibraryFrame(None, "图书管理系统")
        frame.Show()
        return True
if __name__ == '__main__':
    app = LibraryApp()
    app.MainLoop()
​

3.开发C/ S 结构的图书销售管理系统，要求实现两个模块： ①后台管理，包括管理种类、图书库存（可以上传图书封面图片）、出版社；②前台销售，包括查询图书资料（根据种类、书名、出版社）、销售图书（ 会影响库存），并记录每一条销售信息，统计每天、每月的销售情况。

 

4. 编写“学生管理系统”，要求： 必须使用自定义函数，完成对程序的模块化；学生信息至少包含姓名、年龄、学号。除此之外，可以适当添加必需的功能，如添加、删除、修改、查询、退出。