【python3 自动化之mysql操作】python3下的mysql入门基础

1、创建表*/ DROP TABLE IF EXISTS mysql.Student; create table Student ( Sno varchar(20), Sname varchar(50), primary key (Sno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.Course; create table Course ( Cno varchar(20), Cname varchar(50), Tno varchar(20), primary key (Cno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.SC; create table SC ( Sno varchar(20), Cno varchar(20), score int, primary key (Sno, Cno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.Teacher; create table Teacher ( Tno varchar(20), Tname varchar(50), primary key (Tno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; /*2、插入表数据*/ INSERT INTO `Student` (Sno, Sname) VALUES (‘001‘, ‘陈一‘); INSERT INTO `Student` (Sno, Sname) VALUES (‘002‘, ‘郭二‘); INSERT INTO `Student` (Sno, Sname) VALUES (‘003‘, ‘张三‘); INSERT INTO `Student` (Sno, Sname) VALUES (‘004‘, ‘李四‘); INSERT INTO `Student` (Sno, Sname) VALUES (‘005‘, ‘王五‘); INSERT INTO `Teacher` (Tno, Tname) VALUES (‘001‘, ‘张老师‘); INSERT INTO `Teacher` (Tno, Tname) VALUES (‘002‘, ‘王老师‘); INSERT INTO `Teacher` (Tno, Tname) VALUES (‘003‘, ‘钱老师‘); INSERT INTO `Teacher` (Tno, Tname) VALUES (‘004‘, ‘刘老师‘); INSERT INTO `Teacher` (Tno, Tname) VALUES (‘005‘, ‘胡老师‘); INSERT INTO `Course` (Cno, Cname, Tno) VALUES (‘001‘, ‘语文‘, ‘001‘); INSERT INTO `Course` (Cno, Cname, Tno) VALUES (‘002‘, ‘数学‘, ‘002‘); INSERT INTO `Course` (Cno, Cname, Tno) VALUES (‘003‘, ‘英语‘, ‘003‘); INSERT INTO `Course` (Cno, Cname, Tno) VALUES (‘004‘, ‘物理‘, ‘004‘); INSERT INTO `Course` (Cno, Cname, Tno) VALUES (‘005‘, ‘政治‘, ‘005‘); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘001‘, ‘001‘, 50); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘001‘, ‘002‘, 60); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘001‘, ‘003‘, 70); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘001‘, ‘004‘, 80); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘001‘, ‘005‘, 90); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘002‘, ‘001‘, 90); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘002‘, ‘002‘, 80); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘002‘, ‘003‘, 70); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘002‘, ‘004‘, 60); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘002‘, ‘005‘, 50); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘003‘, ‘001‘, 81); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘003‘, ‘002‘, 82); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘003‘, ‘003‘, 83); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘003‘, ‘004‘, 84); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘003‘, ‘005‘, 85); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘004‘, ‘001‘, 71); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘004‘, ‘002‘, 72); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘004‘, ‘003‘, 73); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘004‘, ‘004‘, 74); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘004‘, ‘005‘, 75); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘005‘, ‘001‘, 75); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘005‘, ‘002‘, 74); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘005‘, ‘003‘, 73); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘005‘, ‘004‘, 72); INSERT INTO `SC` (Sno, Cno, score) VALUES (‘005‘, ‘005‘, 71); /*运行sql文件出错，解决办法：使用Notepad++打开文件，选择 格式->无BOM的UTF8格式编码*/ /*2、单表查询_表数据*/ select * from Teacher; select * from Student; select * from Course; select * from SC; /*3、多表查询_表数据*/ select s.Sname as "学生", t.Tname as "教师", c.Cname as "课程", sc.score as "分数" from Student s, SC sc, Course c, Teacher t where sc.Cno = c.Cno and sc.Sno = s.Sno and c.Tno = t.Tno order by s.Sname, t.Tname, c.Cname, sc.score;

输出查询结果【部分截图】，能够正常显示，说明环境配置正常

 

紧接着：python代码去模拟手工操作数据库

# coding:utf-8
import pymysql
‘‘‘
新添加一个mysql数据库
ip:192.168.112.54    端口：3306    账号：root   密码：123456
‘‘‘
# 连接mysq数据库
connection = pymysql.connect(host=‘192.168.112.54‘,
                             port=3306,
                             user=‘root‘,
                             password=‘123456‘,
                             db=‘mysql‘,
                             charset=‘utf8‘,
                             cursorclass=pymysql.cursors.DictCursor)
# 实例化——创建数据库游标
cursor = connection.cursor()
# 使用execute方法执行SQL语句
id = ‘001‘
i = cursor.execute("select * from Student s where s.Sno = %s" % id)
print("记录数：%d;" % i, "类型：", type(i))

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print("data:", data, "type:", type(data))
# fetchone()返回字典类型，通过字典访问值
print("Student Number : %s " % data["Sno"])
# 使用 fetchall() 方法获取多条数据
j = cursor.execute("select * from Teacher ")
print("记录数：%d;" % j, "类型：", type(j))
datas = cursor.fetchall()
# 循环遍历查找字典值
for k in datas:
    print(k["Tname"])
print("datas:", datas, "type:", type(datas))
# fetchall()返回list类型，通过list访问值
print(datas[0]["Tname"])
# 关闭数据库连接
cursor.close()
connection.close()

 

输出查询结果【部分截图】

数据库修改等基本操作，请查阅mysql相关知识

-----------------------------------------------------------------------------

特别鸣谢以下大佬：

 

Anges黎梦      博客地址：https://www.cnblogs.com/AngesZhu/

快捷通道：
想学自动化测试（接口自动化、selenium自动化、appium自动化等）请联系，上海悠悠
悠悠大佬博客：
https://www.cnblogs.com/yoyoketang/
雷总博客：
https://www.cnblogs.com/leiziv5/

 

【python3 自动化之mysql操作】python3下的mysql入门基础

标签：www   信息   配置   ODB   格式   mod   engine   分享   l数据库