1. 安装PyMySQL
pip install PyMySQL
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_2python.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