前言

本节内容是关于python环境中使用pymysql实现mysql数据库的操作，这里我们以app这个表为本节内容的操作表，完成python环境下，对mysql数据库的表的增删查改操作。在开始本节内容之前，我们首先要安装好我们的python环境以及数据库环境。

正文

①在数据库中创建app表，其结构如下，包含id和name字段

CREATE TABLE `app` (`id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

②使用以下命令安装pymysql环境依赖，操作mysql数据库

pip install PyMySQL

③创建一个my_mysql_pymysql.py类，初始化mysql数据库的连接,以及创建关闭连接mysql的方法

# 使用pymysql操作数据库
import pymysqlclass MyPymysql(object):"""初始化数据库连接"""def __init__(self):self.conn_pymysql = pymysql.connect(host='192.168.110.88',port=3306,user='root',password='root',database='ht-atp',charset='utf8')self.cursor = self.conn_pymysql.cursor()"""关闭数据库连接"""def close(self):self.cursor.close()self.conn_pymysql.close()

④使用mysql连接对象，查询app表中的全部数据

if __name__ == '__main__':#创建mysql连接对象conn = MyPymysql()pymysql = conn.conn_pymysqlcursor = conn.cursor# 查询app表全部数据sql = 'select * from app'cursor.execute(sql)rows = cursor.fetchall()for row in rows:print(row)print('-----------------查询结束------------------')# 关闭数据库连接conn.close()

⑤使用mysql连接对象，新增多条数据到app表

if __name__ == '__main__':conn = MyPymysql()pymysql = conn.conn_pymysqlcursor = conn.cursor# 保存app表数据sql = 'INSERT INTO app(id,name) VALUES(%s, %s)'datas = [('7', 'jack'), ('8', 'zhangsan'), ('9', 'wangwu')]result = cursor.executemany(sql, datas)print("result:", result)pymysql.commit()print('-----------------保存数据结束------------------')# 关闭数据库连接conn.close()

⑥使用mysql连接对象，更新app表数据id为7的数据

if __name__ == '__main__':conn = MyPymysql()pymysql = conn.conn_pymysqlcursor = conn.cursor# 更新app表数据sql = "UPDATE app SET name='%s' WHERE id='%s'"sql = sql % ("xiaoming", "7")print("sql:", sql)result = cursor.execute(sql)print("result:", result)pymysql.commit()print('-----------------更新数据结束------------------')# 关闭数据库连接conn.close()

⑦使用mysql连接对象，删除app表数据id为7的数据

if __name__ == '__main__':conn = MyPymysql()pymysql = conn.conn_pymysqlcursor = conn.cursor# 删除app表数据sql = "DELETE FROM app WHERE id='%s'"sql = sql % "4"print("sql:", sql)result = cursor.execute(sql)print("result:", result)pymysql.commit()print('-----------------删除数据结束------------------')# 关闭数据库连接conn.close()

⑧使用mysql连接对象，根据id批量删除app表中数据

if __name__ == '__main__':conn = MyPymysql()pymysql = conn.conn_pymysqlcursor = conn.cursor# 批量删除app表数据sql = "DELETE FROM app WHERE id in {}"data = ('7', '8', '9')sql = sql.format(data)print("sql:", sql)result = cursor.execute(sql)print("result:", result)pymysql.commit()print('-----------------批量删除数据结束------------------')# 关闭数据库连接conn.close()