一、PyMySQL簡(jiǎn)介
PyMySQL ： 是封裝了MySQL驅(qū)動(dòng)的Python驅(qū)動(dòng)凶赁，一個(gè)能使Python連接到MySQL的庫(kù)。
文檔：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
三、連接數(shù)據(jù)庫(kù)
1. 創(chuàng)建表
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. 添加數(shù)據(jù)
# 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)
# 創(chuàng)建對(duì)象
cursor=conn.cursor()
sql="select * from users where email like %s"
cursor.execute(sql,'%ad%')
# 數(shù)據(jù)提取
result=cursor.fetchone()
# result=cursor.fetchall()
print(result)
conn.close()
說(shuō)明：
fetchone(): 查詢一條
fetchall()：查詢所有
cursor.fetchmany(10)：提取指定10條記錄
3. 修改
# coding=utf-8
import pymysql
conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 創(chuàng)建cursor對(duì)象
cursor=conn.cursor()
# 修改數(shù)據(jù)
sql="update users set email=%s,password=%s where id=%s"
cursor.execute(sql,("system@163.com","000000",1))
conn.commit()
conn.close()
4. 刪除數(shù)據(jù)
# coding=utf-8
import pymysql
conn=pymysql.connect("localhost","root","1234","qt_db", cursorclass=pymysql.cursors.DictCursor)
# 創(chuàng)建cursor對(duì)象
cursor=conn.cursor()
# 刪除
sql="delete from users where id=%s"
cursor.execute(sql,(1))
conn.commit()
conn.close()
