Python 数据库操作
Python 数据库操作
一、前言
数据库是应用程序保存数据的一个重要手段，因此学习python语言的数据库操作也是非常重要的内容。今天我们就来分享pymysql模块的使用。
二、数据库基本操作
2.1 安装 PyMySQL 模块
我使用的是 Python 3.x 版本，我们可以通过 pip 直接安装：
pip install pymysql也可以选择先去 https://pypi.org/project/PyMySQL/#files [1]下载 whl 文件，点击红框部分：
我把下载好的文件放到桌面，然后在 cmd 中进入桌面文件夹，输入如下语句：
pip install PyMySQL-0.9.3-py2.py3-none-any.whl其中 install 为文件的全名，我们可以直接输入 pip install PyMy 然后按 Tab 自动补全。这样我们的模块就安装好了。
2.2 连接数据库和简单操作
连接数据库非常简单，一个最简单的数据库操作步骤如下：
•获取本地 IP•连接数据库•获取数据库游标•数据库操作•关闭游标和数据库连接
在具体操作之前，我们先创建一个数据库，后续所有案例都会在该数据库中进行。我们先进入 MySQL，执行如下语句：
create database pydb;接下来我们就可以开始操作数据库了，代码实现如下：
import pymysql, socket
# 1、获取本地ip，我们使用socket获取'localhost'
ip_address = socket.gethostbyname('localhost')
# 2、连接数据库
"""
host：电脑的ip地址
port：数据库的端口号，该参数为整形
user：用户名
password：密码
database：数据库名称
charset：字符编码
该方法返回一个数据库的连接
"""
conn = pymysql.connect(host=ip_address, port=3306, user='root', password='123456', database=pydb, charset='utf8')
# 3、获取数据库游标
m_cursor = conn.cursor()
# 4、数据库操作
# 4.1、准备sql语句
sql = """
create table user(
name varchar(20),
password varchar(20)
);
"""
# 4.2、使用光标执行sql语句
m_cursor.execute(sql)
# 关闭光标和数据库连接，需要先关闭游标再关闭连接
m_cursor.close()
conn.close()这样我们就在数据库中创建了一个 user 表了，我们可以查询一下：
use pydb;
desc user;结果如下：
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+2.3 添加数据
添加数据和上面的操作大致一样，除了 SQL 语句不同外，我们还需要提交对数据库进行的操作。其步骤如下：
•获取本地 IP•连接数据库•获取数据库游标•数据库操作•提交已经进行的操作•关闭游标和数据库连接
这里加了一个步骤，其余都是相同的。代码如下：
import pymysql, socket
# 1、获取本地ip，我们使用socket获取'localhost'
ip_address = socket.gethostbyname('localhost')
# 2、连接数据库
conn = pymysql.connect(host=ip_address, port=3306, user='root', password='123456', database=pydb, charset='utf8')
# 3、获取数据库游标
m_cursor = conn.cursor()
# 4、添加数据
# 4.1、准备sql语句
sql = "insert into user values('zack', '123456')"
# 4.2、使用光标执行sql语句
m_cursor.execute(sql)
# 5、提交
conn.commit()
# 6、关闭游标和数据库连接
m_cursor.close()
conn.close()大部分代码都一样，其中添加了如下一句代码：
conn.commit()其作用就是提交你开始执行的 SQL 语句，执行该语句后数据库中的数据会发生相应的变化。
2.4 删除数据和修改数据
因为要删除数据，我们先在数据库中添加一些数据：
insert into user values('rudy', '123456');
insert into user values('alice', '1111');
insert into user values('alex', '1111');
insert into user values('atom', '1111');
insert into user values('nyx', '123456');在控制台中进入 MySQL 环境，执行上面语句，然后我们再查询一下数据库中的数据，语句如下：
select * from user;执行后结果如下：
+-------+----------+
| name | password |
+-------+----------+
| zack | 123456 |
| rudy | 123456 |
| alice | 1111 |
| alex | 1111 |
| atom | 1111 |
| nyx | 123456 |
+-------+----------+接下来我们就可以进行删除操作了。代码如下：
import pymysql, socket
# 1、获取本地ip，我们使用socket获取'localhost'
ip_address = socket.gethostbyname('localhost')
# 2、连接数据库
conn = pymysql.connect(host=ip_address, port=3306, user='root', password='123456', database=pydb, charset='utf8')
# 3、获取数据库游标
m_cursor = conn.cursor()
# 4、添加数据
# 4.1、准备sql语句
sql1 = "delete from user where password = '1111';"
sql2 = "update user set name = 'zax' where name = 'zack';"
# 4.2、使用光标执行sql语句
num = m_cursor.execute(sql1)
print('删除了' + str(num) + '条数据')
num = m_cursor.execute(sql2)
print('修改了' + str(num) + '条数据')
# 5、提交
conn.commit()
# 6、关闭游标和数据库连接
m_cursor.close()
conn.close()大致代码都没有改变，我们在执行 SQL 语句时使用 num 接收了 execute() 的返回值。该返回值为被删除/修改数据的数量。执行上面代码后我们再查询数据，发现 password 为 1111 的数据全被删除了。且原本 name 为 zack 的数据被修改为 zax。
2.5 查询数据
查询数据和之前的操作有了些许区别，其中我们主要会使用到 cursor 的两个个方法。各方法解释如下：
方法名称 | 返回值类型 | 返回值解释 |
|---|---|---|
fetchone() | 元组 | 返回下一行数据的结果集 |
fetchall() | 二元组 | 返回所有数据 |
了解上面两个方法后我们就可以开始进行查询操作了。我们先使用查询一行数据的方法 fetchone()：
import pymysql, socket
ip_address = socket.gethostbyname('localhost')
conn = pymysql.connect(host=ip_address, port=3306, user='root', password='123456', database=pydb, charset='utf8')
m_cursor = conn.cursor()
# 准备sql语句，查询user表中的所有数据
sql1 = "select * from user;"
# 执行sql语句
m_cursor.execute(sql)
# 获取下一行数据
set = m_cursor.fetchone()
# 格式化输出数据
data = """用户名：%s....密码：%s"""
print(data%(set[0], set[1]))我们调用 m_cursor.fetchone() 方法，获取一条结果的元组。因为是第一次调用，所以返回的是第一行数据，如果再次调用将返回第二行数据。上面代码输出如下：
用户名：zax....密码：123456我们还可以遍历每个结果。我们将获取下一行数据部分修改成如下：
# 获取第一行数据
set = m_cursor.fetchone()
# 如果第一行数据不为空，则进入循环
while set is not None:
# 格式化输出数据
data = """用户名：%s....密码：%s"""
print(data%(set[0], set[1]))
# 获取下一行数据
set = m_cursor.fetchone()修改后输出结果如下：
用户名：zax....密码：123456
用户名：rudy....密码：123456
用户名：nyx....密码：123456当然，我们还可以直接使用 fetchall() 方法，改方法返回数据为二元组，我们将代码修改为如下：
# 获取查询的所有数据
set = cursor.fetchall()
# 遍历结果集
for i in set:
# 格式化输出
data = "用户名：%s...密码：%s"
print(data%(i[0], i[1]))这样我们就完成了我们的查询操作。
三、事务执行
在开始事务的讲解之前，我们先准备好另外一张表 bank：
create table bank(
name varchar(20) primary key,
password varchar(20) not null,
money varchar(20) not null
);这是一张银行账户表，我们把用户名设置为主键。我们事先添加两条数据：
insert into bank values('zack', '123456', '2000');
insert into bank values('rudy', '123456', '200');下面我们来实现这么一个功能，就是让 rudy 给 zack 转账 100 元。那么我们需要执行如下步骤：
•将 zack 的钱增加 100•将 rudy 的钱减少 100
接下来我们用代码来实现一下以上功能：
import pymysql, socket
# 获取ip
url = socket.gethostbyname('localhost')
# 连接数据库
conn = pymysql.connect(host=url, port=3306, user='root', password='123456', database='pydb', charset='utf8')
# 获取游标
m_cursor = conn.cursor()
"""转账部分代码start"""
# 在我们修改zack的钱之前，我们需要知道zack现在的钱
sql = "select money from bank where name = 'zack';"
m_cursor.execute(sql)
zack_money = cursor.fetchone()[0]
# 将zack的钱添加100
zack_money = 100 + int(zack_money)
# 将修改后的数据更新到数据库
sql = "update bank set money = '" + str(zack_money) + "' where name = 'zack';"
m_cursor.execute(sql)
# 在我们修改zack的钱之前，我们需要知道rudy现在的钱
sql = "select money from bank where name = 'rudy';"
m_cursor.execute(sql)
rudy_money = cursor.fetchone()[0]
# 将rudy的钱减少100
rudy_money = int(rudy_money) - 100
# 将修改后的数据更新到数据库
sql = "update bank set money = '" + str(rudy_money) + "' where name = 'rudy';"
m_cursor.execute(sql)
"""转账部分代码end"""
# 提交操作并关闭连接
conn.commit()
cursor.close()
conn.close()注意：上面部分 SQL 语句我使用了字符串拼接的方式，这里需要注意的是在拼接 varchar 数据类型时，需要添加单引号。
接下来我们看一看，执行上面代码后我们再查询一下数据库中的数据：
+------+----------+-------+
| name | password | money |
+------+----------+-------+
| rudy | 123456 | 100 |
| zack | 123456 | 2100 |
+------+----------+-------+数据已经修改了，但是还有个问题。现在 rudy 只有 100 元，如果再让 rudy 给 zack 转账 200 元。这样的话上述代码还是会正常执行的，但是现实生活中是不允许的，所有我们需要将转账部分的代码放入 try 中，当余额不足时抛出异常。为了方便，我们将转账部分代码抽出函数 transferAccounts()，函数具体代码如下：
def transferAccounts(cursor, user1, user2, money):
"""
该方法对cursor所在数据库进行操作，使用户1向用户2转账money
:param cursor: 游标
:param user1: 用户1
:param user2: 用户2
:param money: 要转的钱数
:return:
"""
# 查询账户余额并存钱
sql = "select money from bank where name = '" + user2 + "';"
cursor.execute(sql)
user2_money = cursor.fetchone()[0]
user2_money = money + int(user2_money)
sql = "update bank set money = '" + str(user2_money) + "' where name = '" + user2 + "';"
cursor.execute(sql)
# 查询账户余额并取钱
sql = "select money from bank where name = '" + user1 + "';"
cursor.execute(sql)
user1_money = cursor.fetchone()[0]
user1_money = int(user1_money) - money
# 余额不足是抛出异常
if(user1_money < 0):
raise Exception('money is not enough')
sql = "update bank set money = '" + str(user1_money) + "' where name = " + user1 + ";"
cursor.execute(sql)我们在取钱的时候对修改后的金额进行判断，如果为 0 我们就抛出异常。然后我们主体部分代码可以修改为如下：
# 省略获取连接和游标部分代码
# 执行try代码块
try:
transferAccounts(m_cursor, 'rudy', 'zack', 200)
# 捕获异常，并将异常信息打印出来
except Exception as e:
print("交易失败，失败信息：%s"%e)
conn.commit()
cursor.close()
conn.close()我们让 rudy 给 zack 转账 200，但是实际上 rudy 只有 100 元，这个时候就会产生异常。执行结果如下：
交易失败，失败信息：money is not enough然后我们查询一下数据库中的数据：
+------+----------+-------+
| name | password | money |
+------+----------+-------+
| rudy | 123456 | 100 |
| zack | 123456 | 2500 |
+------+----------+-------+发现 zack 的钱增加了，但是 rudy 钱并没有减。我们来详细说一下这个过程到底发生了什么：
1.在我们获取连接时，该数据库默认开启了事务2.我们在事务中添加了给 zack 加钱的操作3.在执行给 rudy 减钱的操作时发生异常，该操作并未添加到事务当中4.在最后我们提交事务中的操作，即给 zack 加钱的操作
由上面的操作可知，我们只给 zack 加了钱，而没有给 rudy 减钱。这种亏本的生意银行可不会做，这个时候我们就要介绍一个和 commit() 息息相关的函数 rollback() 了。rollback() 的作用是回滚已经添加的操作。我们将代码修改为如下：
# 省略获取连接和游标部分代码
# 执行try代码块
try:
transferAccounts(m_cursor, 'rudy', 'zack', 200)
# 提交数据
conn.commit()
# 捕获异常，并将异常信息打印出来
except Exception as e:
# 回滚操作
conn.rollback()
print("交易失败，失败信息：%s"%e)
cursor.close()
conn.close()到此，事务就讲完了。
四、实现简单登陆注册
在进行下面的讲解前，我们先删除原先的 user 表：
drop table user;然后我们执行下列语句，创建一个新的 user 表：
create table user(
name varchar(20) primary key,
password varchar(20)
);然后我们就可以继续下面的讲解了。
4.1 图形界面
因为图形界面不是本次 Chat 的重点，所以这里不会对图形界面进行过多的讲解，只会提到一些简单的东西。首先这里选用的是 tkinter 模块。我们实现一个简单的界面，具体代码如下：
from tkinter import *
from dbutils import DbUtils
from tkinter.messagebox import *
# 创建一个数据库工具的对象
db = DbUtils('localhost', 3306, 'root', '123456', 'pydb', 'utf8')
# 获取数据库连接
conn = db.get_conn()
# 获取数据库游标
m_cursor = conn.cursor()
# 创建窗口并设置窗口属性
root = Tk()
root.title("登陆界面") # 设置界面标题
root.geometry("300x200") # 设置界面大小width=300，height=200
root.resizable(0, 0) # 设置界面大小不可改变
# 添加文本标签和输入框
l_name = Label(root, text='用户名') # 在root窗口下创建一个文本标签
l_name.pack() # 将文本标签放到root上
e_name = Entry(root) # 在root窗口下创建一个输入框
e_name.pack() # 将输入框放到root上
l_psd = Label(root, text='密码') # 在root窗口下创建一个文本标签
l_psd.pack() # 将文本标签放到root上
e_psd = Entry(root, textvariable=StringVar(), show='*') # 在root窗口下创建一个文本显示格式为***的密码框
e_psd.pack() # 将密码框放到root上
# 点击登陆按钮的回调函数
def login():
# 内容后续慢慢讲解
pass
# 点击注册按钮的回调函数
def register():
# 内容后续慢慢讲解
pass
# 创建登陆和注册按钮，分别将其事件回调方法设置为login和register
b_login = Button(root, text='登陆', width=10 ,command=login) # 创建登陆按钮，设置回调函数为login
b_login.pack() # 将登陆按钮放到root上
b_register = Button(root, text='注册',width=10, command=register) # 创建注册按钮，设置回调函数为register
b_register.pack() # 将注册按钮放到root上
# 窗口关闭时的回调事件
def on_closing():
# 内容后续慢慢讲解
root.destroy()
# 设置窗口关闭调用on_closing方法
root.protocol("WM_DELETE_WINDOW", on_closing)
# 让root界面进入循环
root.mainloop()上面代码每句都有相应的注释。其中有三个方法：login、register、on_closing。这三个方法的方法体是空的，其内容放到后面讲解。另外我们导入了一个 dbutils 模块。该模块是自己编写的一个简单模块，后续会大家讲解。因为没有做过多的调整，所以界面比较紧凑，运行后展示界面如下：
4.2 dbutils 模块
实际上一个模块就是一个 py 文件，我们在项目的根目录下创建一个** dbutils.py** 文件。然后我们开始代码部分的编写：
import pymysql, socket
# 定义一个数据库工具类
class DbUtils():
# 初始化传入一些必要的参数
def __init__(self, host, port, user, password, database, charset):
# 当第一个参数为字符串'localhost'时，自动获取本地ip
if(host == 'localhost'):
self.host = socket.gethostbyname('localhost')
# 否则直接使用用户传入的ip
else:
self.host = host
self.port = port
self.user = user
self.password = password
self.database = database
self.charset = charset
# 连接数据库
self.conn = pymysql.connect(
host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset
)
# 获取数据库连接
def get_conn(self):
return self.conn4.3 login 函数
接下来我们来看看我们应该如何响应登陆按钮。大致登陆流程如下：
flowchat
st=>start: 用户登陆
op1=>operation: 输入用户名密码并点击登陆按钮
cond1=>condition: 判断用户名密码是否为非空
op2=>operation: 通过用户名在数据库中查找密码
cond2=>condition: 判断查找的密码和输入的密码是否一样
e=>end: 登陆成功
st->op1->cond1
cond1(yes)->cond2
cond1(no)->op1
cond2(no)->op1
cond2(yes)->e我们用代码实现上面的流程图：
def login():
# 获取用户名和密码输入框中的内容
name = e_name.get() # 获取用户输入的用户名
password = e_psd.get() # 获取用户输入的密码
# 判断密码和用户名是否为非空
if (password == '' or name == ''):
# 弹出警告窗口
showerror('警告', '用户名和密码不能为空')
else:
# 查询name用户的密码
sql = "select password from user where name = '%s'" % name
m_cursor.execute(sql)
psd = m_cursor.fetchone()
# 如果查询的结果不为空，且和输入的密码匹配则登陆成功
if (psd is not None and psd[0] == password):
showinfo('登陆', '登陆成功')
# 否者登陆失败
else:
showwarning('警告', '用户名或密码错误')这样我们就实现了登陆的功能。因为没有对数据进行修改，所以不需要进行异常代码捕获。接下来我们看一看注册的流程。
4.4 register 函数
注册的判断和登陆有许多相似的地方，我们先看一下注册的流程图：
flowchat
st=>start: 用户注册
op1=>operation: 输入用户名密码并点击注册按钮
cond1=>condition: 判断用户名密码是否为非空
op2=>operation: 通过用户名在数据库中查找密码
cond2=>condition: 判断输入的用户名是否是新用户
e=>end: 注册成功
st->op1->cond1
cond1(yes)->cond2
cond1(no)->op1
cond2(no)->op1
cond2(yes)->e前面的步骤是一样的，就是第二个判断改成了判断输入的用户名是否为新用户。我们用代码实现上面流程图：
def register():
# 获取用户名和密码输入框中的内容
name = e_name.get() # 获取用户输入的用户名
password = e_psd.get() # 获取用户输入的密码
# 判断密码和用户名是否为非空
if (password == '' or name == ''):
showwarning('警告', '用户名和密码不能为空')
else:
# 在数据库中查询name用户
sql = "select * from user where name = '%s'" % name
m_cursor.execute(sql)
psd = m_cursor.fetchone()
# 如果查询结果为空
if(psd is None):
# 因为可能遇到异常，我们在异常代码块注册用户
try:
# 将新用户信息更新至数据库
sql = "insert into user values ('%s', '%s')"%(name, password)
m_cursor.execute(sql)
conn.commit()
showinfo('注册', '注册成功')
except Exception as e:
# 当发生错误是回滚操作
showerror('错误', '发生错误%s'%e)
conn.rollback()
# 当查询结果不为空时给出警告
else:
showwarning('警告', '用户已存在')4.5 on_closing 函数
最后就是我们的窗口关闭响应函数 on_closing，这里非常简单：
def on_closing():
# 关闭游标
m_cursor.close()
# 关闭数据库连接
conn.close()
# 关闭窗口
root.destroy()这里我们就是做了一些收尾工作，将数据库相关东西关闭，然后关闭窗口。
References
[1] https://pypi.org/project/PyMySQL/#files : https://pypi.org/project/PyMySQL/#files