Python 数据库操作-阿里云开发者社区

一、前言

在开始本文之前，我们假定每位读者都已经掌握了 Python 语言的基本语法，对于 sql 语句也有一定了解。因为该部分内容不会作为本文的主题。本文主题在于理解如何使用 Python 语言来连接数据库，并且进行相应的操作（增删改查）。另外还会使用图形界面实现一个简单的登录注册功能。

二、数据库基本操作

2.1 安装 PyMySQL 模块

我使用的是 Python 3.x 版本，我们可以通过 pip 直接安装：

pip install pymysql

也可以选择先去 https://pypi.org/project/PyMySQL/#files 下载 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 钱并没有减。我们来详细说一下这个过程到底发生了什么：

在我们获取连接时，该数据库默认开启了事务
我们在事务中添加了给 zack 加钱的操作
在执行给 rudy 减钱的操作时发生异常，该操作并未添加到事务当中
在最后我们提交事务中的操作，即给 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()

这里我们就是做了一些收尾工作，将数据库相关东西关闭，然后关闭窗口。

五、总结

本文先对用最简单的数据库连接作为开始，然后分别讲解了增删改查四种操作。在了解基础操作后，我们再针对事务进行相应的讲解。对大致内容进行了解后，我们使用图形界面。将上面内容进行综合的应用。不管使用什么语言，操作数据库的基础在于对 SQL 语句的熟悉，如果本身对 SQL 不了解，那么后续的学习也无法进展。

Python 数据库操作

一、前言

二、数据库基本操作

2.1 安装 PyMySQL 模块

2.2 连接数据库和简单操作

2.3 添加数据

2.4 删除数据和修改数据

2.5 查询数据

三、事务执行

四、实现简单登陆注册

4.1 图形界面

4.2 dbutils 模块

4.3 login 函数

4.4 register 函数

4.5 on_closing 函数

五、总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Python 数据库操作

一、前言

二、数据库基本操作

2.1 安装 PyMySQL 模块

2.2 连接数据库和简单操作

2.3 添加数据

2.4 删除数据和修改数据

2.5 查询数据

三、事务执行

四、实现简单登陆注册

4.1 图形界面

4.2 dbutils 模块

4.3 login 函数

4.4 register 函数

4.5 on_closing 函数

五、总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像