一、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()
