一、PyMySQL简介

PyMySQL ： 是封装了MySQL驱动的Python驱动，一个能使Python连接到MySQL的库。
文档：https://github.com/chendemo12/knowledgegraph/wiki/pymysql%E5%AE%98%E6%96%B9%E6%96%87%E6%A1%A3
源码地址：https://github.com/PyMySQL/PyMySQL
模块地址：https://pypi.org/project/PyMySQL/

二、模块安装

pip install PyMySQL
或
pip install PyMySQL==0.9.3

三、连接数据库

1. 创建表

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=1 ;

四、增删改查

1. 添加数据

# coding=utf-8
import pymysql

conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 增
cursor=conn.cursor()
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql,('admin@qq.com','123456'))
conn.commit()
conn.close()

2. 查询

# coding=utf-8
import pymysql

conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 创建对象
cursor=conn.cursor()
sql="select * from users where email like %s"
cursor.execute(sql,'%ad%')
# 数据提取
result=cursor.fetchone()
# result=cursor.fetchall()
print(result)
conn.close()

说明：
fetchone(): 查询一条
fetchall()：查询所有
cursor.fetchmany(10)：提取指定10条记录

3. 修改

# coding=utf-8
import pymysql

conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 创建cursor对象
cursor=conn.cursor()
# 修改数据
sql="update users set email=%s,password=%s where id=%s"
cursor.execute(sql,("system@163.com","000000",1))
conn.commit()
conn.close()

4. 删除数据

# coding=utf-8
import pymysql

conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 创建cursor对象
cursor=conn.cursor()
# 删除
sql="delete from users where id=%s"
cursor.execute(sql,(1))
conn.commit()
conn.close()