Python3使用PyMySQL操作数据库

1. 安装PyMySQL

pip install PyMySQL

关于PyMySQL的详细内容可以查看官方文档  Github

2. 创建表

在某个数据库内，使用以下指令建表

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

3. 使用PyMySQL执行增删改查

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2019-1-7 15:43
# @Author  : Z.C.Wang
# @Email   : iwangzhengchao@gmail.com
# @File    : PyConnectionMySQL.py
# @Software: PyCharm Community Edition
"""
Description :
pymysql.Connect()参数说明
host(str):      MySQL服务器地址
port(int):      MySQL服务器端口号
user(str):      用户名
passwd(str):    密码
db(str):        数据库名称
charset(str):   连接编码

connection对象支持的方法
cursor()        使用该连接创建并返回游标
commit()        提交当前事务
rollback()      回滚当前事务
close()         关闭连接

cursor对象支持的方法
execute(sql, args)     执行一个数据库的查询命令
fetchone()      取得结果集的下一行
fetchmany(size) 获取结果集的下几行
fetchall()      获取结果集中的所有行
close()         关闭游标对象
"""
import pymysql

# 连接数据库
connection = pymysql.connect(host='localhost', user='root', password='root',
                             db='test', charset='utf8')

if connection.open:
    print('the connection is open...')

# 清空users表
cursor = connection.cursor()
cursor.execute("truncate table users")
connection.commit()

# (1)批量插入
record = []
for i in range(10):
    email = "mail_" + str(i) + "@qq.com"
    password = "xxx_" + str(i)
    record.append((email, password))

try:
    sql = "insert into users (email, password) values (%s, %s)"
    rows = cursor.executemany(sql, record)
    connection.commit()
    print("insert success. affected rows : %d" % rows)
except:
    print("insert ERROR.")
    connection.rollback()

# (2)删除记录
try:
    sql = "delete from users where id=1"
    rows = cursor.execute(sql)
    connection.commit()
    print("delete success. affected rows : %d" % rows)
except:
    print("delete ERROR.")
    connection.rollback()

# (3)修改记录
try:
    sql = "update users set password='yyy' where id=5"
    rows = cursor.execute(sql)
    connection.commit()
    print("update success. affected rows : %d" % rows)
except:
    print("update ERROR.")
    connection.rollback()

# (4)查询记录
try:
    sql = 'select * from users'
    count = cursor.execute(sql)
    print("number of record in users: %d" % count)
    result = cursor.fetchall()
    for row in result:
        print(row)
    connection.commit()
except:
    print("query ERROR.")
    connection.rollback()

# 关闭连接
cursor.close()
connection.close()
print("connection close.")

运行结果：

D:\Python3.6_2\python.exe E:/PycharmProjects/PyConnectionMySQL/PyConnectionMySQL.py
the connection is open...
insert success. affected rows : 10
delete success. affected rows : 1
update success. affected rows : 1
number of record in users: 9
(2, 'mail_1@qq.com', 'xxx_1')
(3, 'mail_2@qq.com', 'xxx_2')
(4, 'mail_3@qq.com', 'xxx_3')
(5, 'mail_4@qq.com', 'yyy')
(6, 'mail_5@qq.com', 'xxx_5')
(7, 'mail_6@qq.com', 'xxx_6')
(8, 'mail_7@qq.com', 'xxx_7')
(9, 'mail_8@qq.com', 'xxx_8')
(10, 'mail_9@qq.com', 'xxx_9')
connection close.

Process finished with exit code 0