python-day43_MySQL数据库2

1，navicat

　　连接MYSQL数据库的客户端工具软件

　　

2，数据库备份

　　mysqldump -u root -p db1 > db1.sql　　#备份：数据表结构+数据

　　-d　　表示只备份结构，不备份数据

3，数据库导入

　　mysqldump -u root -p db1 < db1.sql

 4，AS

　　1）把查询结果+AS+新的临时表，新的临时表用于其它操作

　　SELECT corse_id FROM

　　(SELECT corse_id FROM score WHERE student_id =1)) AS A ;

　　2）把函数结果AS做为一个变量，用于其它操作

　　SELECT COUNT(1) AS new_count FROM student;

　　3)在SELECT中把同一个表，进行合并组合后（表行数*表行数），同时输出

　　SELECT * FROM score AS S1,score AS S2

 5，CASE WHEN ... THEN ... ELSE

　　CASE WHEN number<60 THEN 0 ELSE 1 END

　　当条件为真，则输出0，否则输出1

6，pymysql模块

   pip3 install pymysql -i https://pypi.douban.com/simple

   Python模块，对数据库进行操作（SQL语句）

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)
# select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

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

有SQL注入问题的代码

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \
    charset='utf8')
# conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "SELECT * FROM userinfo WHERE username=%s AND password=%s"
cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})    #实测会报错
# cursor.execute(sql,user,pwd)   #实测会报错
result = cursor.fetchone()
cursor.close()
conn.close()

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

增，删，改

conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \
    charset='utf8')
cursor=conn.cursor()
sql="INSERT INTO userinfo(username,password) VALUES('egon','123')"
ret=cursor.execute(sql)　　#返回受影响的行数
conn.commit()　　#提交操作
cursor.close()
conn.close()

      

查

conn = pymysql.connect(host="192.168.231.10",port=3306,user='alex',password='redhat',database='pydb', \
    charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)　　#函数中参数使结果以list+字典类型返回，默认为元组
sql="SELECT * FROM userinfo"
ret=cursor.execute(sql)
result=cursor.fetchall()　　#fetchall()一次取到所有记录
cursor.close()
conn.close()      

# result = cursor.fetchone()　　#fetchone()是一次取一行
# print(result)      

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

练习：

    权限管理
    
        权限表：
            1   订单管理
            2   用户管理
            3   菜单管理
            4   权限分配
            5   Bug管理
        
        用户表：
            1   Alex
            2   egon
        
        用户权限关系表：
            1    1
            1    2
            2    1
            
    Python实现：

        某个用户登录后，查看自己拥有所有权限




CREATE TABLE userinfo (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username char(20) NOT NULL,
    password CHAR(30)
     )ENGINE=INNODB DEFAULT CHARSET = utf8;

CREATE TABLE privilege(
    pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pname char(20)
     )ENGINE=INNODB DEFAULT CHARSET = utf8;

CREATE TABLE user_priv(
    uid INT NOT NULL PRIMARY KEY,
    pid INT,
    UNIQUE uni_uid(uid,pid),
    CONSTRAINT fk_uid_pid FOREIGN KEY(pid) REFERENCES privilege(pid)
     )ENGINE=INNODB DEFAULT CHARSET = utf8;

INSERT INTO userinfo(username,password) VALUES('alex','123'),('egon','123')
INSERT INTO privilege(pname) VALUES('订单管理'),('用户管理'),('菜单管理'),('权限分配'),('Bug管理')
INSERT INTO user_priv(uid,pid) VALUES(1,1),(2,1)      

练习题

import pymysql

def show_priv(user):
    conn = pymysql.connect(host='10.10.66.45', port=3306, user='alex', password='redhat', database='pydb', \
                           charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "SELECT user_priv.uid,userinfo.username,privilege.pname FROM user_priv \
        LEFT JOIN privilege ON user_priv.pid=privilege.pid \
        LEFT JOIN userinfo ON user_priv.uid=userinfo.id \
        WHERE userinfo.username=%s"
    cursor.execute(sql, [user])
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

def manu(user):
    manu_list=['查看权限','退出']
    print("="*20)
    for index,i in enumerate(manu_list,1):
        print(index,i)
    usercli =input("请输入选项：")
    while usercli!='2':
        if usercli=='1':
            result=show_priv(user)  #执行SQL
            print(result)
        print("="*20)
        for index, i in enumerate(manu_list, 1):
            print(index, i)
        usercli = input("请输入选项：")
    print("谢谢使用！")

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host='10.10.66.45',port=3306,user='alex',password='redhat',database='pydb',\
                       charset='utf8')
cursor=conn.cursor()
sql = "SELECT * FROM userinfo WHERE username=%s AND password=%s"
cursor.execute(sql,[user,pwd])
result = cursor.fetchone()
cursor.close()
conn.close()

if result:
    print("登陆成功")
    manu(user)
else:
    print("登陆失败")      

homework

转载于:https://www.cnblogs.com/yygy/p/11039801.html