八月七号——pymysql模块作业

# 尝试着利用pymysql操作MySQL
#     完成新建数据库
#     创建用户表
#     插入用户数据
#     并尝试书写一个用户登录功能(结合mysql数据库)
# 并
# .扩展题:
#     用户的注册、用户的登录
#     获取用户名和密码之后去之前写入用户信息的文件中
#     比对是否有该用户名和密码(一行行读内容、split切割)

import pymysql

def new_db():   #建库建表插数据
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123'
    )
    cur = conn.cursor()
    cur.execute("create database user character set utf8;")
    cur.execute("use user;")
    cur.execute("create table user(u varchar(64),p varchar(255));")
    try:
        cur.execute("insert into user(u,p) values('a','a')")
        conn.commit()
        print('开始数据库插入操作')
    except Exception as e:
        conn.rollback()
        print('数据库插入操作错误回滚')
    finally:
        conn.close()


def s_u_db(user):   #查询用户信息
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123',
        database = 'user',
        charset = 'utf8'
    )
    cur = conn.cursor()
    cur.execute("select * from user where u='%s'"%user)
    ret = cur.fetchall()
    conn.close()
    return dict(ret)



def i_u_db(nu,np):   #写入数据用户信息
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123',
        database = 'user',
        charset = 'utf8'
    )
    try:
        cur = conn.cursor()
        cur.execute("insert into user(u,p) values('%s','%s')"%(nu,np))
        conn.commit()
    except Exception as e:
        conn.rollback()
        return 'x'
    finally:
        conn.close()



def Sign(): #登录
    uu = input('请输入用户名：').strip()
    up = input('请输入密码：').strip()
    s = s_u_db(uu)
    if s == {}:
        print('用户不存在')
    elif s[uu] == up:
        input('登录成功')
        global a
        a = 0
    else:
        print('密码错误')


def register(): #注册
    nu = input('请输入用户名：').strip()
    if s_u_db(nu) != {}:
        print('用户名已存在！')
    else:
        np1 = input('请输入密码：').strip()
        np2 = input('请确认密码：').strip()
        if np1 != np2:
            print('两次密码不同')
        else:
            c = i_u_db(nu,np1)
            if c == 'x':
                print('注册失败')
            else:
                print('注册成功')


#目录
catalog = {
    '1':['登录',Sign],
    '2':['注册',register],
    '0':['退出']
}


#锚变量
a = 1
b = None

# 主程序
while a == 1:
    for i in catalog:
        print('%s\t%s'%(i,catalog[i][0]))
    b = input('请输入指令：')
    if b == "0":
        break
    elif b in catalog:
        catalog[b][1]()
    else:
        print('命令不存在')