Python MySQL数据库交互

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 本文介绍在 `Python` 中如何与 `MySQL` 数据库交互利用 `PyMySQL` 数据库驱动,实现 `MySQL` 数据库的增删改查及事务处理

引言

本文介绍在 Python 中如何与 MySQL 数据库交互

利用 PyMySQL 数据库驱动,实现 MySQL 数据库的增删改查及事务处理

<br/>

MySQL 简介

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,后来被Sun公司收购,Sun公司后来又被Oracle公司收购,目前属于Oracle旗下产品。

特点

开源 免费 不要钱 使用范围广,跨平台支持性好,提供了多种语言调用的 API。

是学习数据库开发的首选。

<br/>

环境

环境名称 版本
Python 3.7.9
PyMySQL 1.0.2
MySql-Server 5.7.32

首先我们要安装 PyMySQL 数据库驱动

pip install PyMySQL
AI 代码解读

如要指定版本

pip install PyMySQL==1.0.2
AI 代码解读

可能默认的源安装第三库会有点慢,可以配置一下其他的镜像源。Pip安装第三方库网速慢(解决方案)

如果只想临时安装第三库快一点,可以临时使用其他镜像源。

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple PyMySQL==1.0.2
AI 代码解读

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2 中则使用 mysqldb。

<br/>

准备数据

创建数据库准备数据

# 创建 testdb 数据库
create databases testdb charset=utf8;

use testdb;

# 创建 employee 表
create table employee (
    id int unsigned primary key auto_increment not null,
    emp_num varchar(10) not null,
    name varchar(10) not null,
    age int not null,
    sex varchar(6) not null,
    salary float not null
);

# 插入员工数据
insert into employee (emp_num, name, age, sex, salary) values 
    ('M001', '张三', 56, '男', 10000),
    ('F002', '李四', 50, '女', 9000),
    ('M003', '王五', 47, '男', 8000), 
    ('M004', '赵六', 46, '男', 7000),
    ('F005', '孙七', 36, '女', 6000),
    ('M006', '周八', 28, '男', 5000),
    ('M007', '吴九', 26, '男', 4000),
    ('M008', '郑十', 22, '男', 3000);
AI 代码解读

<br/>

Python 访问数据库流程

Python DB API访问数据库流程

<br/>

引入模块

  • py文件 中引入 pymysql 模块
from pymysql import *
AI 代码解读

<br/>

Connection 对象

  • 用于建立与数据库的连接
  • 创建对象:调用connect()方法
conn = connect(参数列表)
AI 代码解读
  • 参数 host:连接的 mysql 主机,如果本机就是 localhost
  • 参数 port:连接的 mysql 主机的端口,默认是 3306
  • 参数 database:数据库的名称
  • 参数 user:连接的用户名
  • 参数 password:连接的密码
  • 参数 charset:通信采用的编码方式,推荐使用 utf8

<br/>

对象的方法

  • close() 关闭连接
  • commit() 提交
  • cursor() 返回 Cursor 对象,用于执行 sql 语句并获得结果

<br/>

Cursor对象

  • 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
  • 获取Cursor对象:调用Connection对象的cursor()方法
cursor = conn.cursor()
AI 代码解读

<br/>

对象的方法

  • close() 关闭
  • execute(operation [, parameters ]) 执行sql语句,返回受影响的行数,主要用于执行 insert、update、delete 语句,也可以执行 create、alter、drop 等语句
  • fetchone() 执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
  • fetchall() 执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回

<br/>

对象的属性

  • rowcount 只读属性,表示最近一次 execute() 执行后受影响的行数
  • connection 获得当前连接对象

<br/>

Python操作MySQL数据库

查询 MySQL 服务版本

"""
Python与MySQL数据库交互练习
"""
import pymysql


def mysql_version():
    """
    查询MySQL版本信息
    """

    # 获取数据库连接
    conn = pymysql.connect(
        host='localhost', # mysql服务主机,localhost代表本地
        user='root', 
        password='123456', 
        database='testdb',
        port=3306
    )

    # 创建游标对象 cursor
    cursor = conn.cursor()

    # 查询mysql版本的sql语句
    sql = 'select version();'

    # 执行sql语句
    cursor.execute(sql)

    data = cursor.fetchone()
 
    print ("Database version : %s " % data)
 
    # 关闭数据库连接
    conn.close()


def main():

    mysql_version()


if __name__ == '__main__':
    main()
AI 代码解读

<br/>

employee 数据表的增删改

为了方便操作数据库,我把获取数据库连接和游标对象提取到一个函数里了

import pymysql


def get_conn(dbname):
    """
    获取本地 dbname 的数据库连接及游标对象
    """
    conn = pymysql.connect(
        host = 'localhost', 
        user = 'root',
        password = '123456',
        database = dbname,
        port = 3306
    )
    return conn, conn.cursor()
AI 代码解读

<br/>

新增员工信息到 employee 数据表

def emp_insert():
    """
    新增员工信息
    """
    conn, cursor = get_conn(dbname='testdb')

    # 插入数据
    insert_sql = """insert into employee values (NULL, 'M009', 'hui', 21, '男', 6000);"""

    # 执行sql语句,返回受响应的行数
    count = cursor.execute(insert_sql)
    print(count)

    # 使用占位符
    emp_info = ('M010', 'wang', 22, '男', 7000)
    insert_sql = """insert into employee values (NULL, %s, %s, %s, %s, %s);"""
    count = cursor.execute(sql, emp_info)
    print(count)

    # 更新数据(给每一位员工涨10%工资)
    update_sql = """update employee set salary=salary * 1.1;"""
    cursor.execute(update_sql)

    # 删除数据(删除名字为wang的员工)
    delete_sql = """delete from employee where name='wang';"""
    cursor.execute(delete_sql)

    # 记得提交到数据库执行
    conn.commit()

    cursor.close()
    conn.close()
AI 代码解读

<br/>

更新 employee 数据表信息

def emp_update():
    """
    更新employee数据库表数据
    """
    conn, cursor = get_conn(dbname='testdb')

    # 更新数据(给每一位员工涨10%工资)
    update_sql = """update employee set salary=salary * 1.1;"""
    cursor.execute(update_sql)

    # 记得提交到数据库执行
    conn.commit()

    cursor.close()
    conn.close()
AI 代码解读

<br/>

删除 employee 数据表信息

def emp_delete():
    """
    删除employee数据库表数据
    """
    conn, cursor = get_conn(dbname='testdb')

    # 删除数据(删除名字为wang的员工)
    delete_sql = """delete from employee where name='wang';"""
    cursor.execute(delete_sql)

    # 记得提交到数据库执行
    conn.commit()

    cursor.close()
    conn.close()
AI 代码解读

<br/>

原employee数据表

mysql> select * from employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1 | M001    | 张三 |  56 | 男  |  10000 |
|  2 | F002    | 李四 |  50 | 女  |   9000 |
|  3 | M003    | 王五 |  47 | 男  |   8000 |
|  4 | M004    | 赵六 |  46 | 男  |   7000 |
|  5 | F005    | 孙七 |  36 | 女  |   6000 |
|  6 | M006    | 周八 |  28 | 男  |   5000 |
|  7 | M007    | 吴九 |  26 | 男  |   4000 |
|  8 | M008    | 郑十 |  22 | 男  |   3000 |
+----+---------+------+-----+-----+--------+
8 rows in set (0.00 sec)
AI 代码解读

操作后的

新增后的
mysql> select * from employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1 | M001    | 张三 |  56 | 男  |  10000 |
|  2 | F002    | 李四 |  50 | 女  |   9000 |
|  3 | M003    | 王五 |  47 | 男  |   8000 |
|  4 | M004    | 赵六 |  46 | 男  |   7000 |
|  5 | F005    | 孙七 |  36 | 女  |   6000 |
|  6 | M006    | 周八 |  28 | 男  |   5000 |
|  7 | M007    | 吴九 |  26 | 男  |   4000 |
|  8 | M008    | 郑十 |  22 | 男  |   3000 |
| 16 | M009    | hui  |  21 | 男  |   6000 |
| 17 | M010    | wang |  22 | 男  |   7000 |
+----+---------+------+-----+-----+--------+
10 rows in set (0.00 sec)

更新、删除后
mysql> select * from employee;
+----+---------+------+-----+-----+--------+
| id | emp_num | name | age | sex | salary |
+----+---------+------+-----+-----+--------+
|  1 | M001    | 张三 |  56 | 男  |  11000 |
|  2 | F002    | 李四 |  50 | 女  |   9900 |
|  3 | M003    | 王五 |  47 | 男  |   8800 |
|  4 | M004    | 赵六 |  46 | 男  |   7700 |
|  5 | F005    | 孙七 |  36 | 女  |   6600 |
|  6 | M006    | 周八 |  28 | 男  |   5500 |
|  7 | M007    | 吴九 |  26 | 男  |   4400 |
|  8 | M008    | 郑十 |  22 | 男  |   3300 |
| 16 | M009    | hui  |  21 | 男  |   6600 |
+----+---------+------+-----+-----+--------+
9 rows in set (0.00 sec)
AI 代码解读

<br/>

employee 数据表的查询

  • fetchone() 获取查询结果集的第一个行数据,返回一个元组
  • fetchall() 获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回

<br/>

单行查询获取数据

def emp_fetchone():
    """
    单行查询employee数据表信息
    """

    # 单行查询
    conn, cursor = get_conn('testdb')
    sql = """select * from employee where id > 3;"""
    count = cursor.execute(sql)
    print("查询到%d条数据:" % count)

    for i in range(count):
        # 一行一行获取查询结果
        result = cursor.fetchone()
        print(result)

    cursor.close()
    conn.close()
AI 代码解读

<br/>

多行查询获取数据

def emp_fetchall():
    """
    多行查询employee数据表信息
    """

    # 多行查询
    conn, cursor = get_conn('testdb')
    sql = """select * from employee;"""
    count = cursor.execute(sql)
        print("查询到%d条数据:" % count)
    
    results = cursor.fetchall()
    for ret in results:
        print(ret)

    cursor.close()
    conn.close()
AI 代码解读

<br/>

事务处理

为什么要有事务

事务广泛的运用于订单系统、银行系统等多种场景

例如:

A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:

  1. 检查A的账户余额>500元;
  2. A 账户中扣除500元;
  3. B 账户中增加500元;

正常的流程走下来,A账户扣了500,B账户加了500,皆大欢喜。

那如果A账户扣了钱之后,系统出故障了呢?A白白损失了500,而B也没有收到本该属于他的500。

以上的案例中,隐藏着一个前提条件:A扣钱和B加钱,要么同时成功,要么同时失败。事务的需求就在于此

所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。

例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。所以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性

事务机制可以确保数据一致性

<br/>

事务应该具有4个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。

  • 原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
  • 一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
  • 隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
  • 持久性(durability)。持续性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

Python DB API 2.0 的事务提供了两个方法 commit()rollback()

<br/>

小实例

def transaction_test():
    """
    事务演示
    """
    conn, cursor = get_conn('testdb')
    sql = "delete from employee where age > %s" % (20)
    try:
       # 执行SQL语句
       cursor.execute(sql)

       # 这里以除0异常,来演示现实场景
       n = 1 / 0

       # 向数据库提交
       conn.commit()

    except:
       # 发生错误时回滚
       print('事务回滚')
       conn.rollback()

    cursor.close()
    conn.close()
AI 代码解读

delete from employee where age > 20 这条sql语句并没有把员工年龄20岁以上的给删掉,说明事务回滚。

<br/>

源代码

源代码已上传到 Gitee PythonKnowledge: Python知识宝库,欢迎大家来访。

✍ 码字不易,还望各位大侠多多支持❤️。

<br/>

公众号

新建文件夹X

大自然用数百亿年创造出我们现实世界,而程序员用几百年创造出一个完全不同的虚拟世界。我们用键盘敲出一砖一瓦,用大脑构建一切。人们把1000视为权威,我们反其道行之,捍卫1024的地位。我们不是键盘侠,我们只是平凡世界中不凡的缔造者 。
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
1
分享
相关文章
Python中使用Flask-SQLAlchemy对数据库的增删改查简明示例
这样我们就对Flask-SQLAlchemy进行了一次简明扼要的旅程,阐述了如何定义模型,如何创建表,以及如何进行基本的数据库操作。希望你在阅读后能对Flask-SQLAlchemy有更深入的理解,这将为你在Python世界中从事数据库相关工作提供极大的便利。
244 77
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
课时15:Python的交互模式
今天给大家带来的分享是 Python 的交互模式以及计算机对 Python 的开发,分为以下三个部分。 1.Python的介绍 2.Python的结构 3.保存代码
Python中使用MySQL模糊查询的方法
本文介绍了两种使用Python进行MySQL模糊查询的方法:一是使用`pymysql`库,二是使用`mysql-connector-python`库。通过这两种方法,可以连接MySQL数据库并执行模糊查询。具体步骤包括安装库、配置数据库连接参数、编写SQL查询语句以及处理查询结果。文中详细展示了代码示例,并提供了注意事项,如替换数据库连接信息、正确使用通配符和关闭数据库连接等。确保在实际应用中注意SQL注入风险,使用参数化查询以保障安全性。
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
843 15
探索后端开发:构建高效API与数据库交互
【10月更文挑战第36天】在数字化时代,后端开发是连接用户界面和数据存储的桥梁。本文深入探讨如何设计高效的API以及如何实现API与数据库之间的无缝交互,确保数据的一致性和高性能。我们将从基础概念出发,逐步深入到实战技巧,为读者提供一个清晰的后端开发路线图。
Python Web开发者必看!AJAX、Fetch API实战技巧,让前后端交互如丝般顺滑!
在Web开发中,前后端的高效交互是提升用户体验的关键。本文通过一个基于Flask框架的博客系统实战案例,详细介绍了如何使用AJAX和Fetch API实现不刷新页面查看评论的功能。从后端路由设置到前端请求处理,全面展示了这两种技术的应用技巧,帮助Python Web开发者提升项目质量和开发效率。
169 1
PHP与MySQL的高效交互:从基础到实践####
本文深入探讨了PHP与MySQL数据库之间的高效交互技术,涵盖了从基础连接到高级查询优化的全过程。不同于传统的摘要概述,这里我们直接以一段精简代码示例作为引子,展示如何在PHP中实现与MySQL的快速连接与简单查询,随后文章将围绕这一核心,逐步展开详细讲解,旨在为读者提供一个从入门到精通的实战指南。 ```php <?php // 数据库配置信息 servername="localhost";username = "root"; password="password";dbname = "test_db"; // 创建连接 conn=newmysqli(se
189 0
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
161 1

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问