数据库 - Navicat与pymysql模块

一、Navicat
    在生产环境中操作MySQL数据库还是推荐使用命令行工具mysql，但在我们自己开发测试时，
    可以使用可视化工具Navicat，以图形界面的形式操作MySQL数据库
    
    官网下载：https://www.navicat.com/en/products/navicat-for-mysql
    网盘下载：https://pan.baidu.com/s/1bpo5mqj
    链接：https://pan.baidu.com/s/1Hu-x0mPuSW3g9CxNFlnAng 密码：pqe5
 
      # 打开 双击：
      # D:\navicatformysql\Navicat for MySQL\navicat

    需要掌握的基本操作
    掌握：
        #1. 测试+链接数据库
        #2. 新建库
        #3. 新建表，新增字段+类型+约束
        #4. 设计表：外键
        #5. 新建查询
        #6. 备份库/表
    
    注意：
        批量加注释：ctrl+？键
        批量去注释：ctrl+shift+？键

二、pymysql 
    介绍：
        在python程序中操作数据库呢？这就用到了pymysql模块，
        该模块本质就是一个套接字客户端软件，使用前需要事先安装
        pip3 install pymysql 
    前提：
        授权加创建
        grant all on *.* to 'root'@'%' identified by '123';
        flush privileges;

        端口:3306
        ip: 192.168.1.102
        mysql -uroot -p123 -h 192.168.1.102

    1.pymysql基本使用
    

           

# -*- coding:utf-8 -*-
"""
端口:3306
ip: 192.168.1.102
mysql -uroot -p123 -h 192.168.1.102

"""
import pymysql

name = input('user>>>:').strip()           # egon1
password = input('password>>>:').strip()  # 123

# 建连接
conn = pymysql.connect(
    host = '192.168.1.102',
    port = 3306,
    user = 'root',
    password = '123',
    db = 'egon',
    charset = 'utf8'
)

# 拿游标
cursor = conn.cursor()

# 执行sql语句
sql = 'select * from userinfo where name= "%s" and password = "%s"'%(name,password)
rows = cursor.execute(sql)
print(rows)

# 关闭
cursor.close()
conn.close()

# 进行判断
if rows:
    print('登录成功')
else:
    print('登录失败')

    2.sql注入
        注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符
        1、sql注入之：用户存在，绕过密码
        egon' -- 任意字符

        2、sql注入之：用户不存在，绕过用户与密码
        xxx' or 1=1 -- 任意字符
       

                 

                 

                   

        
        解决办法：
            # 原来是我们对sql进行字符串拼接
            # sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
            # print(sql)
            # res=cursor.execute(sql)
            
            #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
            sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
            res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

# -*- coding:utf-8 -*-
import pymysql

name = input('name>>>:').strip()
password = input('password>>>:').strip()
conn = pymysql.connect(
    host = '192.168.1.102',
    port = 3306,
    user = 'root',
    password = '123',
    db = 'egon',
    charset = 'utf8'
)
cursor = conn.cursor()
# sql = 'select * from userinfo where name = "%s" and password = "%s"'%(name,password)
# rows = cursor.execute(sql)
sql = 'select * from userinfo where name=%s and password = %s'
rows = cursor.execute(sql,(name,password))   #执行sql语句，返回sql影响成功的行数
print(sql)
print(rows)
cursor.close()
conn.close()
if rows:
    print('登录成功')
else:
    print('登录失败')

"""
name>>>:egon1" -- x          #需要帐号,sql注入 -- 表示 注释掉 只需要判断user 不需要判断password
password>>>:
select * from userinfo where name = "egon1" -- x" and password = ""
1
登录成功
"""
"""
name>>>:xxx" or 1=1 -- xxx   #不需要帐号密码,sql注入 太恐怖!!
password>>>:
select * from userinfo where name = "xxx" or 1=1 -- xxx" and password = ""
3
登录成功
"""
"""
解决办法:
    sql = 'select * from userinfo where name=%s and password = %s'
    rows = cursor.execute(sql,(name,password))
"""

    3.pymysql模块之增删改查
        增：
            sql = 'insert into userinfo(name,password) values(%s,%s)'
            rows = cursor.execute(sql,('lily','123'))
            conn.commit()   # 注意只有执行了commit() 才会更新到数据库中
        
            批量:
            rows = cursor.executemany(sql,[('alice4','123'),('alice5','123'),('alice6','123')])
            print(cursor.lastrowid)  # 显示插入数据前的id 走到哪
        
        删：
            sql = 'delete from userinfo where name = %s'
            rows = cursor.execute(sql,('alice5'))
            conn.commit()
        改：
            sql = 'update userinfo set name = %s where id = %s '
            rows = cursor.execute(sql,('abcd',2))
            conn.commit()
        
        查：
            # 元祖形式
            cursor = conn.cursor()
        
            rows = cursor.execute(sql)
            print(cursor.fetchone())
            print(cursor.fetchmany(3))
            print(cursor.fetchall())
            print(cursor.fetchone())  # None 没有数据了!
        
            ((1, 'aaabbb', '123'), (2, 'abcd', '456'), (3, 'egon3', '789'))
        
            # 字典形式
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            cursor.fetchone()  cursor.fetchmany(2)  cursor.fetchall()
        
            [{'id': 3, 'name': 'egon3', 'password': '789'}, {'id': 6, 'name': 'alice', 'password': '123'}]
        
            # 相对 绝对 移动游标
            print(cursor.fetchone())
            cursor.scroll(5,'absolute')
            # cursor.scroll(5,'relative')
            print(cursor.fetchmany(2))

"""增删改"""
import pymysql
conn = pymysql.connect(
    host = '192.168.1.102',
    port = 3306,
    user = 'root',
    password = '123',
    db = 'egon',
    charset = 'utf8'
)
cursor = conn.cursor()
# sql = 'insert into userinfo(name,password) values(%s,%s)'
# rows =cursor.execute(sql,('lily','123'))
# rows = cursor.executemany(sql,[('alice4','123'),('alice5','123'),('alice6','123')])
# print(cursor.lastrowid)

# sql = 'delete from userinfo where name = %s'
# rows = cursor.execute(sql,('alice6'))

sql = 'update userinfo set name = %s where id = %s '
rows = cursor.execute(sql,('abcd',2))
print(rows)

conn.commit()

cursor.close()
conn.close()

if rows:
    print('操作成功')
else:
    print('失败')


"""查"""
import pymysql
conn = pymysql.connect(
    host = '192.168.1.102',
    port = 3306,
    user = "root",
    password = '123',
    db = 'egon',
    charset = 'utf8'
)
cursor = conn.cursor()
# cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from userinfo'
rows = cursor.execute(sql)  #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询
print(rows)
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchmany(2))
# print(cursor.fetchall())
# print(cursor.fetchone())  # None

print(cursor.fetchone())
cursor.scroll(5,'absolute')
# cursor.scroll(5,'relative')
print(cursor.fetchmany(2))

cursor.close()
conn.close()

if rows:
    print('操作成功')
else:
    print('失败')