練習
分別有權限表，用戶表，用戶對應權限表三個表
需求：
1、用戶登錄驗證帳號密碼；
2、查看自己的權限；
權限管理練習：
權限表：
1、訂單管理
2、用戶管理
3、菜單管理
4、權限分配
5、BUG管理
用戶表：
1、cce 1
2、csw 2
分配表：
1、 1 3
2、 2 1
表結構創建
drop table if exists allocation;
drop table if exists permission;
create table permission(id int auto_increment,pname char(32) not null,primary key(id)) engine=innodb default charset=utf8;
insert into permission(pname) values('訂單管理'),('用戶管理'),('菜單管理'),('權限分配'),('BUG管理');
drop table if exists users;
create table users(id int auto_increment,username char(32) not null,passwd char(32) not null,primary key(id),unique key(username)) engine=innodb default charset=utf8;
insert into users(username,passwd) values ('cce','caichangen'),('csw','caishuiwang');
create table allocation(id int auto_increment,user_id int,permission_id int,primary key(id),unique key(user_id,permission_id),foreign key(permission_id) references permission(id),foreign key(user_id) references users(id));
insert into allocation(user_id,permission_id) values (1,3),(2,1);
實現代碼
# mysql工具類
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time : 2018/7/6 15:59
# @Author : CaiChangEn
# @Email : [email protected]
# @Software: PyCharm
import pymysql
class mysqldb:
def __init__(self, dbhost, dbport, dbuser, dbpassword, dbdatabase):
self.dbhost = dbhost
self.dbport = dbport
self.dbuser = dbuser
self.dbpassword = dbpassword
self.dbdatabase = dbdatabase
self.connect()
def connect(self):
self.conn = pymysql.connect(host=self.dbhost, port=self.dbport, user=self.dbuser, password=self.dbpassword,
database=self.dbdatabase, charset='utf8')
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
def selected(self, statement):
action, *_ = statement.split(' ')
if hasattr(self, action):
func = getattr(self, action)
return func(statement)
def select(self, statement):
self.cursor.execute(statement)
data = self.cursor.fetchall()
if data:
return data[0]
else:
raise ValueError("The query is wrong")
def update(self, statement):
self.insert(statement)
def alter(self, statement):
self.insert(statement)
def insert(self, statement):
try:
self.cursor.execute(statement)
return self.conn.commit()
except Exception:
self.conn.rollback()
def close(self):
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
conn = mysqldb(host, port, username, password, database)
result = conn.selected('select * from users where id=1')
# result = conn.selected("update users set passwd='caichangen' where id='1' ")
# result = conn.selected("alter table users add vae int(11) ")
# result = conn.selected("insert into users(username,passwd) values ('cfj','caifengjun')")
print(result)
conn.close()
# 邏輯代碼
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from mysqldb import *
host = '127.0.0.1'
port = 3306
username = 'root'
password = ''
database = 'cce'
conn = mysqldb(host, port, username, password, database)
def program():
count = 0
while count<3:
username=input('請輸入賬號：')
passwd=input('請輸入密碼：')
if not username or not passwd:count += 1 ;continue
data=conn.selected("select * from users where username='%s'" %username)
if username != data['username'] or passwd != data['passwd']:
print('Error')
count += 1
else:
choice=''
while choice.lower() !='q':
choice = input('1:查看權限;(q/Q):退出：')
if not choice:continue
if choice == '1':
data = conn.selected("select pname from permission where id in (select permission_id from allocation where user_id in (select id from users where username='%s'));" %username)
for i in data.values():
print(i)
else:
break
program()
conn.close()
閱讀更多 動漫資深愛好者和IT 的文章
