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()：

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.conn

4.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

https://mp.weixin.qq.com/s/9aZoAIWES7XzUUExnbUIzQ