1、所需资源:pycharm,python3.6,module:pymysql
2、pycharm配置mysql:
新添加一个mysql数据库
ip:192.168.112.54 端口:3306 账号:root 密码:123456
接下来,创建数据库表信息:(代码改编,来自网络)
/*1、创建表*/ DROP TABLE IF EXISTS mysql.Student; create table Student ( Sno ), Sname ), primary key (Sno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.Course; create table Course ( Cno ), Cname ), Tno ), primary key (Cno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.SC; create table SC ( Sno ), Cno ), score int, primary key (Sno, Cno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; DROP TABLE IF EXISTS mysql.Teacher; create table Teacher ( Tno ), Tname ), primary key (Tno) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; /*2、插入表数据*/ ', '陈一'); ', '郭二'); ', '张三'); ', '李四'); ', '王五'); ', '张老师'); ', '王老师'); ', '钱老师'); ', '刘老师'); ', '胡老师'); '); '); '); '); '); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); ); /*运行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=', db='mysql', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 实例化——创建数据库游标 cursor = connection.cursor() # 使用execute方法执行SQL语句 id = ' 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/