import pymysql
# MySQL语句 插入数据行，即注册行为
register_sql = 'insert into user(name,password) values(%s,%s)'
# MySQL语句 查看name=username的数据行 判断是否已存在该用户
check_sql = 'select name from user where BINARY name =%s ' # 加入BINARY关键字，可使查询结果区分大小写
# check_sql = 'select name from user where name =%s ' # 这种查询无法区分大小写
# MySQL语句 查看所有符合该账号与密码的用户 若无则有误，若有则输出登录成功
login_sql = 'select * from user where name=%s and password=%s'
# 连接数据库函数
def MySQL_conn():
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "123",
database = "day48",
charset = "utf8",
autocommit = True
) # 连接数据库
# 以字典形式输出数据 不设置则默认为元组类型输出
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return cursor
# 登录函数
def login(cursor):
while True:
username = input("请输入用户名：").strip()
if username.lower == "q":
return
password = input("请输入密码：").strip()
res = cursor.execute(login_sql,(username,password)) # 返回值为数据行数，或字典键值对个数
if res:
print("登陆成功！")
print('数据行数：',res) # 2
print(cursor.fetchall()) # [{'id': 1, 'name': 'Jil', 'password': '1118'}, {'id': 3, 'name': 'Jil', 'password': '1118'}]
cursor.scroll(-1,"relative") # 相对于光标所在的位置继续往前移动1位
print(cursor.fetchall()) # 光标位置取到取到最后 [{'id': 3, 'name': 'Jil', 'password': '1118'}]
return
else:
print("用户名或密码错误！")
# 注册函数
def register(cursor):
while True:
username = input("注册用户名：").strip()
if username.lower == "q":
return
password = input("设定密码：").strip()
re_password = input("确认密码：").strip()
if password != re_password:
print("两次密码输入不一致，请重新输入！")
continue
res = cursor.execute(check_sql,(username))
# print(res)
# print(cursor.fetchall())
if res:
print("用户名【%s】已存在，请重新输入！"%username)
continue
else:
cursor.execute(register_sql,(username,password))
print("注册成功！")
return
if __name__ == '__main__':
cursor = MySQL_conn()
while True:
print("登录或注册".center(50,"="))
print('''1、登录
2、注册''')
cmd = input("请输入您需要的操作数：").strip()
if cmd not in ["1","2",'q']:
print("请输入正确指令！")
if cmd == '1':
login(cursor)
elif cmd == '2':
register(cursor)
else:
break
