{ "cells": [ { "cell_type": "markdown", "id": "84513081", "metadata": {}, "source": [ "## 查看数据" ] }, { "cell_type": "code", "execution_count": 1, "id": "23241985", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:10:53.493078Z", "start_time": "2023-10-10T12:10:53.417598Z" }, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "(5, '孙铭阳', '男', 20, '广东', '23446523', '23446523@qq.com', 'zhangsan', '123')\n", "(17, '李四', '女', 26, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(21, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(22, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(25, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(27, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(28, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(29, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(30, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(31, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(32, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(33, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(34, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(35, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(36, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(37, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(38, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(39, '李永波', '女', 43, '广东', '236543', '236543@qq.com', None, None)\n", "(40, '00', '男', 78, '广东', '99', '123', None, None)\n" ] } ], "source": [ "import pymysql\n", "\n", "## 连接数据库\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset='utf8') # 编码 UTF-8\n", "\n", "print(connection)\n", "\n", "# 创建 cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写 sql\n", "sql = \"select * from user\"\n", "\n", "# 执行 SQL\n", "cursor.execute(sql)\n", "\n", "# 获取结果集\n", "users = cursor.fetchall()\n", "\n", "# 查看结果\n", "for u in users:\n", " print(u)" ] }, { "cell_type": "markdown", "id": "ebb6eacc", "metadata": {}, "source": [ "## 插入数据" ] }, { "cell_type": "code", "execution_count": 2, "id": "eb638782", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:15:52.983647Z", "start_time": "2023-10-10T12:15:52.960757Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 1\n" ] } ], "source": [ "import pymysql\n", "\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\",\n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", "\n", "# 获取cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写sql\n", "sql = \"insert into user(id, name) values(41, 'Tom')\"\n", "\n", "# 执行sql，返回 影响的行数\n", "count = cursor.execute(sql)\n", "\n", "print(\"count: \", count)\n", "\n", "# 执行DML时候，会开启一个事务，需要我们主动提交 -- 防止对数据进行误操作\n", "connection.commit()" ] }, { "cell_type": "markdown", "id": "4e53b475", "metadata": {}, "source": [ "## 修改数据" ] }, { "cell_type": "code", "execution_count": 4, "id": "af994da0", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:18:28.072611Z", "start_time": "2023-10-10T12:18:28.062564Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 1\n" ] } ], "source": [ "import pymysql\n", "\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", "\n", "# 获取cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写sql\n", "sql = \"update user set gender='男' where id=41\"\n", "\n", "# 执行sql，返回受影响的行数\n", "count = cursor.execute(sql)\n", "print(\"count:\", count)\n", "\n", "# 执行DML时候，会开启一个事务，需要我们主动提交 -- 防止对数据进行误操作\n", "connection.commit()" ] }, { "cell_type": "markdown", "id": "7155a879", "metadata": {}, "source": [ "## 删除数据" ] }, { "cell_type": "code", "execution_count": 5, "id": "985d4f0b", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:20:45.873650Z", "start_time": "2023-10-10T12:20:45.864234Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 1\n" ] } ], "source": [ "import pymysql\n", "\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", "\n", "# 获取cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写sql\n", "sql = \"delete from user where id=41\"\n", "\n", "# 执行sql，返回受影响的行数\n", "count = cursor.execute(sql)\n", "print(\"count:\", count)\n", "\n", "# 执行DML时候，会开启一个事务，需要我们主动提交 -- 防止对数据进行误操作\n", "connection.commit()" ] }, { "cell_type": "markdown", "id": "56bce4d3", "metadata": {}, "source": [ "## 查询漏洞" ] }, { "cell_type": "code", "execution_count": 7, "id": "29422cfe", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:24:40.726658Z", "start_time": "2023-10-10T12:24:40.717935Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 19\n" ] } ], "source": [ "import pymysql\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", "\n", "# 获取cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写sql\n", "sql = \"select * from user where name=%s\" % (\"'scott' or 1=1\")\n", "\n", "# 执行sql\n", "count = cursor.execute(sql)\n", "print(\"count:\", count)\n", "\n", "# 执行DML时候，会开启一个事务，需要我们主动提交 -- 防止对数据进行误操作\n", "connection.commit()" ] }, { "cell_type": "markdown", "id": "706a683b", "metadata": {}, "source": [ "## 传递参数" ] }, { "cell_type": "code", "execution_count": 8, "id": "a468b121", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:26:10.322260Z", "start_time": "2023-10-10T12:26:10.310338Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 1\n" ] } ], "source": [ "import pymysql\n", "# 获取连接\n", "connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", "\n", "# 获取cursor对象\n", "cursor = connection.cursor()\n", "\n", "# 编写sql\n", "sql = \"insert into user(id, name) values(%s, %s)\"\n", "\n", "# 执行sql\n", "args = (41, 'marry')\n", "count = cursor.execute(sql, args)\n", "print(\"count:\", count)\n", "\n", "# 执行DML时候，会开启一个事务，需要我们主动提交 -- 防止对数据进行误操作\n", "connection.commit()" ] }, { "cell_type": "markdown", "id": "84a00649", "metadata": {}, "source": [ "## 添加异常处理" ] }, { "cell_type": "code", "execution_count": 9, "id": "d24cf6a9", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:32:36.642368Z", "start_time": "2023-10-10T12:32:36.630347Z" }, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count: 1\n" ] } ], "source": [ "import pymysql\n", "connection = None\n", "cursor = None\n", "\n", "try:\n", " # 获取连接\n", " connection = pymysql.connect(\n", " host=\"localhost\", user=\"root\", password=\"zxydsg123\", \n", " db=\"User_information_case\", # 数据库名称\n", " charset=\"utf8\") # 编码 UTF-8\n", " \n", " # 获取 cursor对象\n", " cursor = connection.cursor()\n", " \n", " # 编写sql\n", " sql = \"insert into user(id, name) values(%s, %s)\"\n", " \n", " # 执行sql\n", " args = (1234, 'Tom')\n", " count = cursor.execute(sql, args)\n", " print(\"count:\", count)\n", " \n", " # 执行DML时候，会开启一个事务，需要我们主动提交\n", " connection.commit()\n", " \n", "except Exception as e:\n", " # 如果捕获到异常\n", " print(e)\n", " \n", " if connection:\n", " # 回滚事务\n", " connection.rollback()\n", " \n", "finally:\n", " # 无论是否捕获到异常，我们都要关闭cursor，释放资源\n", " if cursor:\n", " cursor.close()\n", " \n", " if connection:\n", " cursor.close()" ] }, { "cell_type": "markdown", "id": "874055be", "metadata": {}, "source": [ "## DBUtil功能封装" ] }, { "cell_type": "code", "execution_count": 11, "id": "8873d37e", "metadata": { "ExecuteTime": { "end_time": "2023-10-10T12:44:33.199935Z", "start_time": "2023-10-10T12:44:33.175199Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1062, \"Duplicate entry '9999' for key 'user.PRIMARY'\")\n", "(9999, 'lili', '女', None, None, None, None, None, None)\n", "(5, '孙铭阳', '男', 20, '广东', '23446523', '23446523@qq.com', 'zhangsan', '123')\n", "(17, '李四', '女', 26, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(21, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(22, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(25, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(27, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(28, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(29, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(30, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(31, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(32, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(33, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(34, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(35, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(36, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(37, '李四', '男', 24, '广东', '12345123', 'lisi@qq.com', None, None)\n", "(38, '王五', '男', 25, '湖南', '12345123', 'lisi@qq.com', None, None)\n", "(39, '李永波', '女', 43, '广东', '236543', '236543@qq.com', None, None)\n", "(40, '00', '男', 78, '广东', '99', '123', None, None)\n", "(41, 'marry', None, None, None, None, None, None, None)\n", "(1234, 'Tom', None, None, None, None, None, None, None)\n", "(9999, 'lili', '女', None, None, None, None, None, None)\n" ] } ], "source": [ "import pymysql\n", "\n", "\n", "class DBUtil:\n", " \"\"\"数据库工具类\"\"\"\n", "\n", " config = {\n", " \"host\": \"localhost\",\n", " \"user\": \"root\",\n", " \"password\": \"zxydsg123\",\n", " \"db\": \"User_information_case\", # 数据库名称\n", " \"charset\": \"utf8\" # 编码 UTF-8\n", " }\n", "\n", " def __init__(self):\n", "\n", " # 建立连接，传入参数\n", " self.connection = pymysql.connect(**DBUtil.config)\n", "\n", " # 创建 cursor对象\n", " self.cursor = self.connection.cursor()\n", "\n", " def close(self):\n", " \"\"\"释放资源\"\"\"\n", " if self.cursor:\n", " self.cursor.close()\n", " if self.connection:\n", " self.connection.close()\n", "\n", " def exeDML(self, sql, *args):\n", " \"\"\"DML（Data Manipulation Language）语句: 数据操纵语言 -- 插入 修改 删除调用\"\"\"\n", " try:\n", " # 执行sql，返回影响到的行数\n", " count = self.cursor.execute(sql, args)\n", " \n", " # 提交事务\n", " self.connection.commit()\n", " \n", " return count\n", " \n", " # 如果捕获到异常\n", " except Exception as e:\n", " print(e)\n", " \n", " # 回滚食物\n", " if self.connection:\n", " self.connection.rollback()\n", "\n", " def query_one(self, sql, *args):\n", " \"\"\"查询单行数据\"\"\"\n", " try:\n", " # 执行sql\n", " self.cursor.execute(sql, args)\n", " \n", " # 获取结果集\n", " return self.cursor.fetchone()\n", " \n", " except Exception as e:\n", " print(e)\n", " \n", " def query_all(self, sql, *args):\n", " \"\"\"查询所有数据\"\"\"\n", " try:\n", " # 执行sql\n", " self.cursor.execute(sql, args)\n", " # 获取结果集\n", " return self.cursor.fetchall()\n", " \n", " except Exception as e:\n", " print(e)\n", "\n", "\n", "if __name__ == \"__main__\":\n", " \n", " # 创建 DBUtil\n", " dbutil = DBUtil()\n", " \n", " # 插入数据\n", " sql = \"insert into user(id, name, gender) values(%s, %s, %s)\"\n", " \n", " count = dbutil.exeDML(sql, 9999,'lili', '女')\n", "\n", " \n", " # 查询单行数据\n", " sql = \"select * from user where id=%s\"\n", "\n", " # 打印查询结果\n", " emp = dbutil.query_one(sql, 9999)\n", " print(emp)\n", "\n", " \n", " # 查询所有数据\n", " sql = \"select * from user\"\n", " users = dbutil.query_all(sql)\n", " for u in users:\n", " print(u)\n", " \n", " # 记得释放资源\n", " dbutil.close()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.12" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": { "height": "calc(100% - 180px)", "left": "10px", "top": "150px", "width": "288px" }, "toc_section_display": true, "toc_window_display": true } }, "nbformat": 4, "nbformat_minor": 5 }