Python技术之操作数据库

举报
tea_year 发表于 2025/11/29 06:53:48 2025/11/29
【摘要】 1. 操作简介我们在使用到python进行一些业务操作的时候，经常性的要与数据库进行交互。可能要读取到数据库的表中的数据，也可能需要将一些数据写入到表中，完成数据库的数据更新的操作。此时就需要使用python与数据库进行交互了。python有着非常强大的库，为python丰富了各种各样的功能，其中就包括了对数据库的操作。在python中如果需要操作mysql数据库，我们需要首先安装对应的库 ...

1. 操作简介

我们在使用到python进行一些业务操作的时候，经常性的要与数据库进行交互。可能要读取到数据库的表中的数据，也可能需要将一些数据写入到表中，完成数据库的数据更新的操作。此时就需要使用python与数据库进行交互了。
python有着非常强大的库，为python丰富了各种各样的功能，其中就包括了对数据库的操作。在python中如果需要操作mysql数据库，我们需要首先安装对应的库 pip install pymysql

2. 数据库的基本操作

2.1. 连接到数据库

import pymysql
#建立与数据库的连接对象，需要指定与数据库的连接相关的属性
db_connection = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="mydb1"
)
#这个数据库连接对象，在使用结束后，需要调用close来释放资源
db_connection.close()

2.2. 创建操作对象

#获取数据库操作对象
#获取到的是一个光标对象，数据库所有的操作都需要使用这个对象来完成
#例如：DDL、DML、DQL语句
db_cursor = db_connection.cursor()
#数据库操作对象在使用结束后，也是需要调用close来释放资源
#释放需要在数据库连接对象释放之前
db_cursor.close()

2.3. 执行DDL、DML操作

CREATE TABLE stu
 (
  stu_id    varchar(255) NOT NULL,
  stu_name  varchar(255) DEFAULT NULL,
  age       int(11) DEFAULT NULL,
  gender    varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#准备执行的SQL语句
sql = "insert into stu values ('S_1012', 'xiaoMing', 34, 'male')"
#使用数据库操作对象，执行SQL语句
#执行的返回值是一个数字，表示多少行数据受影响 (affected rows)
db_cursor.execute(sql)
#在执行DDL、DML操作的时候，最后需要使用commit来提交，才可以影响到数据库中的数据
#需要使用数据库连接对象来提交
db_connection.commit()

2.4. 执行DQL操作

#准备执行的SQL语句
sql = "select * from stu"
#使用数据库操作对象，执行SQL语句
#执行的返回值是一个数字，表示查询到了多少行的数据
db_cursor.execute(sql)
#获取查询到的一行数据，将一行数据存入一个元组返回
#类似于迭代器，重复调用fetchone的时候，会逐行获取到后续的每一行内容
db_cursor.fetchone()
#获取查询到的N行数据，默认是1行
#将查询到的每一行的数据存入一个元组，再将这些元组存入一个大的元组返回
#即返回的结果是一个二维元组
db_cursor.fetchmany(N)
#获取查询到的所有的数据
3将查询到的每一行的数据存入一个元组，再将这些元组存入一个大的元组返回
#即返回的结果是一个二维元组
db_cursor.fetchall()

代码演示：

import pymysql
#建立数据库连接
db_connection = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="pydb"
)
#增删改查的过程
#它是执行 SQL语句的对象 获取光标对象
db_cursor = db_connection.cursor()
sql = "insert into stu values ('S_1012', 'xiaoMing', 34, 'male')"
sql = "select * from stu"
#执行sql语句
db_cursor.execute(sql)
num = db_cursor.execute(sql)
#获取到一行数据，是一个元组，元组可以通过下标获取，也可以解析获取
#a = db_cursor.fetchone()
#print(a)
#查询前两条数据
#前面获取到了数据，后面再获取就没有了
a = db_cursor.fetchmany(2)
print(a)# (('S_1012', 'xiaoMing', 34, 'male'),)
print(a[0][1]) # 获取到的是第一个元组中的名字
b = db_cursor.fetchall()
print(b)
#提交 查询是不需要提交的
db_connection.commit()
#关闭所有的连接
db_cursor.close()
db_connection.close()
print(db_connection)

3. SQL注入问题

3.1. 什么是SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

3.2. SQL注入演示

准备数据
drop table if exists bank_account;
create table bank_account (
        id int primary key auto_increment comment '主键',
        account_id varchar(18) not null comment '用户账号',
        account_balance double(10,2) comment '账户余额',
        user_name varchar(20) not null comment '用户名称',
        user_pwd varchar(128) unique comment '用户密码',
        user_idcard varchar(18) unique comment '身份证',
        oper_time timestamp comment '操作日期',
        gender enum('f','m') comment 'f 表示女性，m表示男性'
);
insert into bank_account values (null,'6225113088436225',200000,'zhugeliang','zgl123456','100000100010101000','2019-01-01 13:10:10','m');
insert into bank_account values (null,'6225113088436226',1000,'zhouyu','zy123456','100000100010101001','2019-03-01 14:10:10','m');
insert into bank_account values (null,'6225113088436227',210000,'caocao','cc123456','100000100010101002','2019-04-01 14:10:10','m');
insert into bank_account values (null,'6225113088436228',500,'niumo','nm123456','100000100010101003','2019-03-01 10:10:10','m');
commit;
登录案例演示
import pymysql.cursors
#使用登录验证的案例，演示SQL注入
class BankServer:

    @staticmethod
    def login(username, password):
        db = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='123456',
                             database='pydb')
        with db.cursor() as cursor:  # type:'pymysql.cursors.Cursor'
            # 1. 拼接查询的SQL语句
            # sql = select * from bank_account where user_name = '113231' and user_pwd = '' or 1= '1'
            # sql 此时的账号已经不重要了，密码是  ' or 1= '1
            # sql = f"select * from bank_account where user_name = '{username}' and user_pwd = '{password}'"
            # cursor.execute(sql)
            # 以下方式也不行  本质上还是字符串拼接
            """
                        sql = cursor.execute(
                                "select * from bank_account where user_name = '%s' and user_pwd = '%s'" % (username, password))
                        cursor.execute(sql) """
            # 1. 拼接查询的SQL语句，但是解决SQL注入
            sql = "select * from bank_account where user_name = %s and user_pwd = %s"
            # 2. 执行查询语句
            #    为了解决SQL注入，我们将需要用户输入的部分，使用%s来占位
            cursor.execute(sql, (username, password))
            # 3. 获取查询到的一条数据
            return cursor.fetchone()
            
#模拟客户端操作
username = input("输入用户名: ")
password = input("输入密码: ")
#登录验证
login_user = BankServer.login(username, password)
if login_user:
    print(login_user)
else:
    print("登录失败")
请输入用户名: root
请输入密码: ' or 1= '1
可以看到，在我输入密码的时候，就可以使用特定的输入方式，绕过了登录的检查！

3.3. 解决SQL注入

SQL注入本质的问题就是没有对用户输入的内容进行严格的校验，导致用户可以通过自己输入的内容，修改SQL语句的查询结构，达到了自己的破解的目的。那么解决方案其实也就简单了，只需要限制用户输入的内容的格式校验即可。在pymysql模块中，使用execute语句执行指定的SQL语句，在这个函数中，第一个参数就是需要执行的SQL，而我们可以在这样的SQL语句中，使用占位符完成对某些部分的占位，而具体对应的数据，可以将其封装入一个元组，传入第二个参数即可。
#未解决SQL注入的时候
cursor.execute("select * from bank_account where username = %s and password = %s" % (username, password))
#修改如下
cursor.execute("select * from bank_account where username = %s and password = %s", (username, password))

4. 事务支持

4.1. 事务的介绍

当一个业务需要处理多个DML操作的时候，这个业务需要当作一个整体来处理。在处理的过程中，如果有失败或者异常，我们需要回到业务开始的时候；如果处理成功，我们再将数据持久化到磁盘中。这样一个过程，我们就称为一个“事物”。
事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
事物具有一下特性：
  • 原子性（Atomicity）：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
  • 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
只要是跟数据打交道的工具或者框架，必然有事务！！

4.2. MySQL的事务

默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。
事务开始于：
  • 连接到数据库上，并执行一条DML语句insert、update或delete
  • 前一个事务结束后，又输入了另一条DML语句
事务结束于：
  • 执行commit或rollback语句。
  • 执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
  • 执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。
  • 断开与数据库的连接。
  • 执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句。
示例:sql语句实现事务支持
1.回滚情况
START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
ROLLBACK;
2.提交情况
START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
COMMIT;

4.3. python事务案例

案例分析：
一个账号fromAccount向另外一个账号toAccount转账money元钱！
分析：
  • 检查两个账号是否存在，不存在的话，结束转账行为
  • 检查转出账号里的金额是否充足，不充足的话结束转账行为；充足的话，进行扣款money元
  • 转入账号进行增加money元
import pymysql
class PayError(Exception):
    pass
    
def pay(from_account, to_account, money):
    # 建立数据库连接
    db_connection = pymysql.connect(
        host="localhost",
        port=3306,
        user="root",
        password="123456",
        database="pydb"
    )
    # 增删改查的过程
    # 它是执行 SQL语句的对象 获取光标对象
    db_cursor = db_connection.cursor()
    try:
        # 检查付款账号是否存在
        res1 = db_cursor.execute("select * from bank_account where user_name = %s", from_account)
        if res1 == 0:
            raise PayError("付款账号不存在")
        # 检查余额是否足够
        rest_money = db_cursor.fetchone()[2]
        if rest_money < money:
            raise PayError("账号余额不足")
        # 检查转入账号是否存在
        res2 = db_cursor.execute("select * from bank_account where user_name = %s", to_account)
        if res2 == 0:
            raise PayError("转入账号不存在")
        sql1 = "update bank_account set account_balance=account_balance-%s where user_name=%s"
        db_cursor.execute(sql1, (money, from_account))
        a = 10 / 0
        sql2 = "update bank_account set account_balance=account_balance+%s where user_name=%s"
        db_cursor.execute(sql2, (money, to_account))
        # 成功就提交
        db_connection.commit()
    except Exception as e:
        print(e)
        print("转账失败")
        # 失败就回滚
        db_connection.rollback()
    finally:
        db_cursor.close()
        db_connection.close()
        
from_account = input("请输入需要付款的账号名: ")
to_account = input("请输入需要收款的账号名: ")
money = float(input("请输入需要转账的金额: "))
pay(from_account, to_account, money)

5. 数据库操作封装

我们已经可以实现用python来操作数据库中的数据，但是操作的方式比较繁琐，而且重复的操作比较多。因此我们是否可以将一些常见的、重复性功能封装起来，哪里需要用，就在哪里调用呢？
import pymysql
class DB:

    # 构造对象，创建数据库连接对象，并创建数据库操作对象
    def __init__(self, *, host=None, port=None, user=None, password=None, database=None):
        self.db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        self.cur = self.db.cursor()
        
    # 执行DML、DDL操作
    def execute(self, sql, params=None):
        try:
            self.cur.execute(sql, params)
            self.db.commit()
            print("execute: ", sql, params)
        except Exception as e:
            print(e)
            self.db.rollback()

    # 执行DQL操作
    def query_all(self, sql):
        self.cur.execute(sql)
        return self.cur.fetchall()
  
    def __del__(self):
        self.cur.close()
        self.db.close()

#将数据库连接属性的配置写到类的外部
db_connection_prop = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "mydb1"
}

#使用这个封装好的类
db = DB(**db_connection_prop)
#执行查询操作
res1 = db.query_all("select * from bank_account")
for l in res1:
print(l)
#执行DML操作
db.execute("delete from bank_account where id = %s", 4)
res2 = db.query_all("select * from bank_account")
for l in res1:
print(l)
db.execute("update bank_account set account_balance = account_balance - %s where user_name = %s", (100, "zhouyu"))
res3 = db.query_all("select * from bank_account")
for i in res3:
    print(i)

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Python 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入