pymysql进行数据库各项基础操作

最新推荐文章于 2024-05-24 17:29:14 发布

Dream_Bri

最新推荐文章于 2024-05-24 17:29:14 发布

阅读量953

点赞数 19

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/ximu__l/article/details/137195752

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1、mysql数据库简介

功能齐全的数据库管理系统：MySQL是最流行的开源关系数据库管理系统之一，具有完整数据库功能和组件。
客户端-服务器架构：MySQL使用常见的客户端-服务器模型，需要一个运行的数据库服务器，并通过网络或本地连接方式供客户端访问。
支持高并发和大数据量：MySQL设计用于处理大型数据库和高并发用户，支持复制、事务、ACID、多用户和多线程等企业级功能。
管理和配置：需要配置和管理数据库服务器（如用户管理、性能调优、安全设置等）。
用途：广泛用于网站、在线应用程序、企业解决方案等需要处理大量数据和用户的场景。

附注如下：

mysql同我们之前文章中提到的SQLite有什么区别和不同呢？ SQLite3进行数据库各项常用操作

SQLite是一个轻量级、嵌入式的数据库，适合简单的单用户应用或本地存储，而MySQL是一个功能更全面的数据库服务器，适合需要处理复杂查询、多用户访问和高并发的应用。

使用时选择哪一个呢？主要取决于应用的规模、用户数量和性能要求：对于轻量级、便携式应用或者快速开发环境，选择SQLite；对于需要强大数据库功能、高可扩展性和远程访问支持的企业级应用，选择MySQL会更加合适。

下面进入pymysql进行各项基础操作的正题：

2、基于mysql数据库的准备工作

值得一说的是，在本文的数据库研究中，我们使用的是一款名为Navicat Premium 的管理工具。
在这里插入图片描述
我们通过Navicat Premium建立数据库（名称为：mhdata）；表的名为books。

3、通过pymysql进行表的创建

我们要创建一个什么样的表呢？

我们在之前的案例中 SQLite3进行数据库各项常用操作使用了id和name两列结构数据进行了研究。现在我们在原来的基础上，进行（id, name, category, price, publish_time ）的结构创建。

代码如下：

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="mhdata")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS books")
# 使用预处理语句创建表
sql = """
CREATE TABLE books (
  id int(8) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  category varchar(50) NOT NULL,
  price decimal(10,2) DEFAULT NULL,
  publish_time date DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
"""
# 执行SQL语句
cursor.execute(sql)
# 关闭数据库连接
db.close()

注意代码中使用的db = pymysql.connect(host=“localhost”, user=“root”, password=“123456”, database=“mhdata”)语句里的四个参数，按照自己的情况进行输入。

本例子是假设你已经下载了类似于Navicat Premium的数据库管理软件，并在里面创建了一个数据库，我们这里的数据库名字为”mhdata“。

下面我们执行程序，运行成功后，可以打开数据库管理软件----Navicat Premium，找到我们的数据库，可以看到在mhdata数据库下有一个表，表中有个名为books的子表，双击books后，就可以看到右边界面有我们程序设置好的五个分类（id, name, category, price, publish_time ），因为每个分类下都没有对应数据输入，所以用N/A进行了表示：
在这里插入图片描述
到这里，该代码就执行成功了。

4、通过pymysql进行数据插入

现在我们想在上面创建好的表中插入数据，怎么进行操作呢？
代码如下

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="mhdata",charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 数据列表
data = [("语文课本",'语文','79.80','2024-1-1'),
        ("数学课本",'数学','79.80','2024-2-2'),
        ("英语课本",'英语','98.00','2024-3-3'),
        ("物理课本",'物理','69.80','2024-4-4'),
        ("化学课本",'化学','69.80','2024-5-5'),
        ]
try:
    # 执行sql语句，插入多条数据
    cursor.executemany("insert into books(name, category, price, publish_time) values (%s,%s,%s,%s)", data)
    # 提交数据
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()

# 关闭数据库连接
db.close()

执行上面代码后，数组中data的信息就插入到我们的books表格中了。
再次打开Navicat Premium，找到books表格，如下所示：
在这里插入图片描述
我们根据输出结果可以看到books中的信息是数组data 循环两次的结果，这是因为我们将代码执行了两次的原因。

5、通过pymysql进行数据修改

比如现在我们想要id为1对应的一行信息中publish_time进行修改，将其改为"2022-10-10"，那么怎么操作呢？

操作代码如下：

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="mhdata",charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 数据列表
data = ("2022-10-10",1)
try:
    # 执行sql语句，修改一条数据
    cursor.execute("UPDATE books SET publish_time = %s WHERE id = %s", data)
    # 提交数据
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()

# 关闭数据库连接
db.close()

运行结果展示如下，通过结果我们可以看到id为1对应的一行信息中publish_time已经成功改为"2022-10-10"。
在这里插入图片描述

关于代码中cursor.execute(“UPDATE books SET publish_time = %s WHERE id = %s”, data)的解释

这行代码是在执行一个SQL更新操作，用于更新数据库中的表。具体到这个例子中，它通过使用cursor.execute()方法，更新books表中的一条记录（也可以多条）。
这里的"UPDATE books SET publish_time = %s WHERE id = %s"是一个参数化的SQL更新语句，用于更新满足特定条件（WHERE id = %s）的记录。在这个语句中，%s是占位符，代表将要被替换的值，使用这种参数化查询的方式可以提高SQL语句的安全性，避免SQL注入攻击。
参数data是一个元组（tuple），包含了要替代上述SQL语句中占位符%s的值。具体来说，假设data是这样一个元组：(“2023-04-01”, 1)。
第一个元素"2023-04-01"将替换第一个%s，表示将publish_time更新为2023-04-01。
第二个元素1将替换第二个%s，指定了这个操作应用于id字段值为1的记录。
综合来看，整条语句的作用是：在books表中，将id值为1的记录的publish_time字段更新为"2023-04-01"。

6、通过pymysql进行数据查询

现在我们将查询表books中category类别下为语文相关数据

代码显示为：

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="mhdata",charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 数据列表
data = ("语文")
# 执行sql语句，修改一条数据
cursor.execute("SELECT * FROM books WHERE category = %s", data)
myresult = cursor.fetchall()
# 将需要查询到的数据打印出来
for x in myresult:
  print(x)

# 关闭数据库连接
db.close()

代码执行后，我们可以看到程序打印出来的结果：

(1, '语文课本', '语文', Decimal('79.80'), datetime.date(2022, 10, 10))
(6, '语文课本', '语文', Decimal('79.80'), datetime.date(2024, 1, 1))

7、通过pymysql进行数据删除

现在我们将删除表books中category类别下为数学相关数据

代码显示为：

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="mhdata",charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 数据列表
data = ("数学")
# 执行sql语句，删除一条数据
cursor.execute("DELETE FROM books WHERE category = %s", data)
# 关闭数据库连接
db.close()

代码执行后，我们可以看到books表中已经将category 为数学的相关行删除了：
在这里插入图片描述
截止到这里，大家就已经对mysql、基于pymysql的表的插入、表的修改、表的查询、表的删除有了基本的了解，多多练习实践就可以对基于pymysql的数据库进行操作了。