042 navicat和pymysql

一 navicat可视化界面操作数据库

链接：🔗navicat下载安装与激活教程
注意点：下载Navicat之后不要打开，要不然激活的时候会报错，如以打开请卸载后并删除安装时产生的数据再重装。首先要打开注册机

"""
一开始学习python的时候  下载python解释器然后直接在终端书写
pycharm能够更加方便快捷的帮助你书写python代码
我们在终端操作MySQL，也没有自动提示也无法保存等等  不方便开发
navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标点点点即可完成操作，无需书写sql语句
"""

navicat能够充当多个数据的客户端
navicat图形化界面有时候反应速度较慢 你可以选择刷新或者关闭当前窗口再次打开即可

当你有一些需求该软件无法满足的时候 你就可以自己动手写sql

提示：

1 MySQL是不区分大小写的
	忽略大小写
		内部统一转大写或者小写比较即可
2 MySQL建议所有的关键字写大写
3 MySQL中的注释有两种
	-- select * from emp;
	# select * from emp;
4 快捷键 ctrl + ?

 
 

二 数据库查询练习（多表操作）

练习题
1、 查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门（包括两门）的学生姓名和班级

 
 

三 python 如何操作MySQL(pymysql模块)

首先准备一个userinfo表

"""
pymysql链接数据操作
"""
import pymysql


# 链接数据库
conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123",
    charset="utf8",  # 此处不能写utf-8，连接数据库的时候会报错
    database="study"  # 这是你自己创建的数据库名字
)
# 括号内参数让其以字典的形式返回，不写默认返回元组，当数据很多的时候，元组不能很清楚的看清每个项目有所代表的是啥
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 生成游标对象
sql = "select * from userinfo;"
res = cursor.execute(sql)
# print(res)  # 7 返回这条sql语句所能影响的行数
"""
fetchone就类似于文件读写，一次都没有执行时，光标位于文件头，执行一次光标往后挪一位
fetchall就类似于文件读写，一次都没有执行时，光标位于文件头，执行一次光标往后挪至末尾
fetchmany(3)就类似于文件读写，一次都没有执行时，光标位于文件头，执行一次光标往后挪至括号内数字的位置
"""
# scroll 回滚
# cursor.scroll(1, "relative")  # 相对于光标所在的位置继续往后移动1位
# cursor.scroll(1, "absolute")  # 相对于数据的开头往后继续移动1位
print(cursor.fetchone())  # 一次显示一条sql语句结果 返回的类型由conn.cursor内的参数决定
print(cursor.fetchone())
print(cursor.fetchmany(3))  # 返回一个列表
print(cursor.fetchall())  # 显示所有  返回一个列表

 
 

四 sql注入

"""
sql注入
    对数据库进行登录验证操作
"""
import pymysql

# 链接数据库
conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123",
    charset="utf8",
    database="study"
)
# 生成游标对象，操作数据库的作用
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

username = input("username: ").strip()
password = input("password: ")

sql = "select * from userinfo where name='%s' and password='%s';" % (username, password)
# sql = f"select * from userinfo where name={username} and password={password};"  # 不支持？？？
res = cursor.execute(sql)
if res:
    print("登录成功")
    print(cursor.fetchall())
else:
    print("登录失败")

"""
这样就写完了，利用数据库来实现登录认证的功能，
"""
"""
那么这样写安全吗，我接下来的骚操作只需要一个用户名就行，不行你运行上述代码，在输入用户名的时候输入aaa' -- a看看


这就惊艳到你了？
那这个呢 你用户名都不要
xxx' or 1=1 --a
为什么呢？你可以在sql语句那一行下面，print(sql) 看看sql是啥你就知道了

"""

那么怎么才能安全呢？？？？？

import pymysql
conn = pymysql.connect(
	host="127.0.0.1",
	port=3306,
	user="root",
	password="123",
	charset="utf8",
	database="study"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input("username: ").strip()
password = input("password: ")
sql = "select * from userinfo where name=%s and password=%s;"
# 拼接字符串的操作不要放在sql语句中
res = cursor.execute(sql, (username, password))
if res:
	print("登录成功")
	print(cursor.fetchall())
else:
	print("登录失败")

 
 

五 pymysql模块的增删改查

# 1 针对 增 删 改操作 pymysql需要二次确认才能真正的操作数据
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123",
    database="study",
    charset="utf8",
)
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 增
sql1 = "insert into userinfo(name,password) values(%s,%s);"
cursor.execute(sql1, ("lxx2", 123))
# 还可以一次性插入多条数据
# cursor.executemany(sql1,[('xxx', 123),('yyy', 123),('zzz', 123)])

# 改
sql2 = "update userinfo set name='a33' where name='a3';"
cursor.execute(sql2)

# 删
sql3 = "delete from userinfo where id =4;"
cursor.execute(sql3)

conn.commit()  # 手动确认一次，刷新数据才会有数据添加

# 查  无需手动确认
sql4 = "select * from userinfo"
cursor.execute(sql4)
print(cursor.fetchall())

pymysql实现数据库的注册：

import pymysql


# 连接数据库
conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123",
    charset="utf8",
    database="study"
)
# 生成游标对象，实现对数据的操作
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 注册
while 1:
    username = input("请输入注册名： ").strip()
    password = input("请输入注册密码： ")
    re_password = input("请确认注册密码： ")
    if not password == re_password:
        print("密码不一致")
        continue
    sql1 = "select name from userinfo where name=%s;"
    res = cursor.execute(sql1, (username,))
    # 判断数据库是否存在名字了，不存在就注册，存在就返回重新输入注册
    if res:  # 注册时用户名已存在
        print("用户名已存在,重新注册。")
        continue
    else:
        sql2 = "insert into userinfo(name, password) values(%s, %s);"
        res = cursor.execute(sql2, (username, password))
        print("res: ", res)  # res: 1
        print(cursor.fetchall())
        conn.commit()  # 确认
        print("注册OK")
        break