前面已经讲过了如果mysql密码忘记了该怎么办,(传送门)。因此本节让我们开始利用Python实践一下。
首先先介绍一下之后要用到的表:
然后再介绍一下我们此次的任务:
遇到的问题:
ERROR 1241 (21000): Operand should contain 1 column(s)
这个是因为我在第一题多表查询时,在select的结果集上加了括号。
错误示范:select (Student.sno,。。。。) from xx
利用内连接:
select Student.sno,sname,sdept,SC.cno,cname,Grade from Student,SC,Course where (Student.sno='10002' and Student.sno=SC.sno and SC.cno=Course.cno);
利用where:
select Student.sno,sname,sdept,SC.cno,cname,Grade from ((Student inner join SC on Student.sno=SC.sno) inner join Course on SC.cno=Course.cno) where Student.sno='10002';
代码:
import pymysql
def connect():
try:
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
db='csl',
passwd='密码',
charset='utf8'
)
print('ok')
return conn
except Exception as err:
print('失败:{}'.format(err))
def main():
# 创建连接,并且返回连接对象
conn = connect()
# 创建游标对象
cursor = conn.cursor()
# ********* Begin ********* #
a=cursor.execute("select Student.sno,sname,sdept,SC.cno,cname,Grade from Student,SC,Course where (Student.sno='10002' and Student.sno=SC.sno and SC.cno=Course.cno)")
print(cursor.fetchall()) #打印查询结果内容
print(a) #打印查询结果个数
b=cursor.execute("select Student.sno,sname,sdept,SC.cno,cname,Grade from Student,SC,Course where (Grade>85 and Student.sno=SC.sno and SC.cno=Course.cno)")
c=cursor.execute("update Course set credit=5 where cno='00001'")
d=cursor.execute("insert into SC(sno,cno,Grade) values ('10005','00004',73)")
# delete from Student where sno='10005'; 删除操作,不用加*
# ********* End ********* #
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
if __name__ == '__main__':
main()