Python操作MySQL之查看、增删改、自增ID

在python中用pymysql模块来对mysql进行操作，该模块本质就是一个套接字客户端软件，使用前需要事先安装，在cmd中输入：

pip3 install pymysql

1、查看

import pymysql

user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="localhost",user='root',password='2180736',database="db666")  # 创建连接
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 创建游标,设置打印结果以列表套字典的形式显示出来
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)
effect_row=cursor.execute(sql) # 执行SQL，并返回收影响行数
result = cursor.fetchone() #只拿第一条数据 
#result=cursor.fetchmany(n) #一次取n条数据 
#result=cursor.fetchall() #取sql语句全部查到的数据（分页显示数据的时候）   
cursor.close() # 关闭游标
conn.close()   # 关闭连接

if result: 
　　print('登录成功') print(result) 
else: 
　　print('登录失败') 


#打印结果： 
#username:alex 
#password:123 
#登录成功 
#(1, 'alex', '123')

在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

2、execute()防注入问题

问题：在navicat for MySQL中，--符号属于注释符，会注释后后面的sql语句，这样会对登陆造成影响：绕过密码，直接登陆

解决办法：

#把
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)
cursor.execute(sql)

#替换为
sql = "select * from userinfo where username=%s and password=%s" %(user,pwd,)
cursor.execute(sql，user,pwd) #execute会帮我们做字符串拼接，推荐这种写法

3、增、删、改

# 增加单条数据
#方式一
import pymysql
conn=pymysql.connect(host="localhost",user='root',password='2180736',database="db666")
cursor = conn.cursor() 
sql = "insert into userinfo(username,password) values('root','123123')"
r=cursor.execute(sql)   #执行sql语句，r表示返回的受影响的行数
conn.commit()
cursor.close()
conn.close()

#方式二
user="'aaaa"
pwd="456"
import pymysql
conn=pymysql.connect(host="localhost",user='root',password='2180736',database="db666")
cursor = conn.cursor() 
sql = "insert into userinfo(username,password) values(%s,%s)"
r=cursor.execute(sql,(user,pwd,))
conn.commit()
cursor.close()
conn.close()


#增加多条数据
import pymysql
conn=pymysql.connect(host="localhost",user='root',password='2180736',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])   #executemany增加多个值,只在insert中使用
conn.commit()
cursor.close()
conn.close()

删和改只需把sql语句改为：

#修改
sql = "update userinfo set password="446" where username="alex" 
#删除
sql = "delete from userinfo where username="alex"

4、新插入数据的自增ID：cursor.lastrowid

import pymysql

conn = pymysql.connect(host="localhost",user='root',password='2180736',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)  #获取自增ID号
cursor.close()
conn.close()

#r如果插入多条数据，cursor.lastrowid显示的是最后一条的ID

练习题：

基于用户权限管理
    参考表结构：

        用户信息
        id username   pwd
         1   alex     123123

        权限
        1    订单管理 
        2    用户劵
        3    Bug管理
        ....

        用户类型&权限
        1     1
        1     2
        2     1
        3     1
    程序：
        用户登录
    

基于角色的权限管理
    
    用户信息
        id username     pwd     role_id
         1   alex     123123      1
         2   eric     123123      1

    权限
        1    订单管理 
        2    用户劵
        3    Bug管理
        ....
    
    角色表：
        1    IT部门员工
        2    咨询员工
        3    IT主管
        
    角色权限管理
        1     1
        1     2
        3     1
        3     2
        3     3
        
===> 
    1. 基于角色的权限管理
    2. 需求分析

exercise