PyMySQL 是一款基于 PEP 249 的纯Python MySQL客户端库。
目录
一、安装依赖
一、安装依赖
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple/
二、初始化数据库
/*
Navicat Premium Data Transfer
Source Server : 本地数据库
Source Server Type : MySQL
Source Server Version : 50733
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 50733
File Encoding : 65001
Date: 07/07/2022 16:39:15
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for fruit
-- ----------------------------
DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`price` int(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of fruit
-- ----------------------------
INSERT INTO `fruit` VALUES (1, '苹果', 50);
INSERT INTO `fruit` VALUES (2, '橘子', 100);
INSERT INTO `fruit` VALUES (3, '葡萄', 50);
INSERT INTO `fruit` VALUES (4, '西瓜', 70);
INSERT INTO `fruit` VALUES (5, '梨', 30);
INSERT INTO `fruit` VALUES (6, '香蕉', 30);
INSERT INTO `fruit` VALUES (7, '柠檬', 50);
INSERT INTO `fruit` VALUES (8, '苹果', 51);
INSERT INTO `fruit` VALUES (9, '苹果', 51);
INSERT INTO `fruit` VALUES (10, '苹果', 51);
INSERT INTO `fruit` VALUES (11, '苹果', 51);
SET FOREIGN_KEY_CHECKS = 1;
三、增删改查操作
(1)查询数据
#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author: Roc-xb
"""
import pymysql
# 获取数据库连接
def query():
# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="123456", database="test")
# 使用cursor()方法创建一个游标对象
cursor = db.cursor()
# 使用execute()方法执行SQL语句
cursor.execute("SELECT * FROM fruit")
# 使用fetall()获取全部数据
data = cursor.fetchall()
# 打印获取到的数据
for item in data:
print(item)
# 关闭游标和数据库的连接
cursor.close()
db.close()
if __name__ == '__main__':
query()
(2)新增数据
#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author: Roc-xb
"""
import pymysql
def insert():
# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="123456", database="test")
# 使用cursor()方法创建一个游标对象
cursor = db.cursor()
# 使用execute()方法执行SQL语句
cursor.execute("INSERT INTO fruit(tname, price) VALUES ('苹果', 51);")
# 获取自增主键ID
id = db.insert_id()
print("数据插入成功,主键ID:", id)
# 提交
db.commit()
# 关闭游标和数据库的连接
cursor.close()
db.close()
if __name__ == '__main__':
insert()
(3)修改数据
#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author: Roc-xb
"""
import pymysql
def update():
# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="123456", database="test")
# 使用cursor()方法创建一个游标对象
cursor = db.cursor()
# 使用execute()方法执行SQL语句
cursor.execute("UPDATE fruit SET tname = '荔枝', price = 53 WHERE id = 12;")
print("数据修改成功")
# 提交
db.commit()
# 关闭游标和数据库的连接
cursor.close()
db.close()
if __name__ == '__main__':
update()
(4)删除数据
#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author: Roc-xb
"""
import pymysql
def delete():
# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="123456", database="test")
# 使用cursor()方法创建一个游标对象
cursor = db.cursor()
# 使用execute()方法执行SQL语句
cursor.execute("DELETE FROM fruit WHERE id = 12;")
print("数据删除成功")
# 提交
db.commit()
# 关闭游标和数据库的连接
cursor.close()
db.close()
if __name__ == '__main__':
delete()