# coding=utf-8
try:
import pymysql
except:
!pip install pymysql
import pymysql
connection = pymysql.connect(host='remotemysql.com',user='PiFyrdk8HO',password='vZeZCaMkXu',database='PiFyrdk8HO',charset="utf8")
cur = connection.cursor()
#def create_tb():
# cur.execute('''CREATE TABLE tb(name VARCHAR(255), pw INT);''')
# connection.commit()
# connection.close()
#def insert_data():
# t=["python","564123"]
# cur.execute("insert into tb values(%s,%s)", t)
# connection.commit()
# connection.close()
def login():
while True:
acc=input("請輸入帳號: ")
if acc == "":
break
sql_1='''SELECT name, pw from tb WHERE name = "%s"'''%(acc)
cur.execute(sql_1)
sql_acc= cur.fetchone()
#print(sql_acc)
if (sql_acc==None):
print("{}帳號不存在".format(acc))
continue
mypwd = sql_acc[1]
#print(mypwd)
pwd=int(input("請輸入密碼: "))
if pwd=="":
break
if (pwd == mypwd):
print()
print("登入成功")
print()
mymenu2()
break
else:
print("密碼錯誤")
print()
def get_all_info():
sql = "SELECT name, pw FROM tb"
cur.execute(sql)
all_data = cur.fetchall()
print()
return all_data
def get_info_one():
choose=input("請輸入您要搜尋的欄位 1. 姓名 2. 密碼")
if(choose=='1'):
while True:
name=input("請輸入姓名: ")
if name == "":
break
sql='''SELECT name, pw FROM tb WHERE name ="%s"'''%(name)
cur.execute(sql)
one = cur.fetchone()
#print(one)
if one == None:
print("{}帳號不存在".format(name))
continue
else:
return one
else:
pwd=input("請輸入密碼: ")
sql_1='''SELECT name, pw FROM tb WHERE pw ="%s"'''%(pwd)
cur.execute(sql_1)
one_data = cur.fetchone()
return one_data
def insert_data():
while True:
name = input("請輸入姓名: ")
if name == "" :
print("姓名不能為空")
continue
sql = '''SELECT * FROM tb WHERE name = "%s"'''%(name)
cur.execute(sql)
data = cur.fetchone()
if not data == None:
print("{}帳號已存在".format(name))
continue
pwd = input("請輸入密碼: ")
sql_insert = '''INSERT INTO tb(name, pw) VALUES('%s','%s')'''%(name, pwd)
cur.execute(sql_insert)
connection.commit()
print("{}已註冊成功".format(name))
print()
break
def delete_data():
while True:
name = input("請輸入姓名: ")
if name == "" :
print("姓名為空格退出")
break
sql = '''SELECT * FROM tb WHERE name = "%s"'''%(name)
cur.execute(sql)
data = cur.fetchone()
if data == None:
print("{}帳號不存在".format(name))
continue
sql_del = '''DELETE FROM tb WHERE name="%s"'''%(name)
cur.execute(sql_del)
connection.commit()
print("{}已被刪除".format(name))
print()
break
def update_name():
while True:
name=input("請輸入要修改的姓名: ")
if name=="":
break
sql_2 = '''SELECT name, pw FROM tb WHERE name="%s"'''%(name)
cur.execute(sql_2)
name_data=cur.fetchone()
if(name_data==None):
print("{}無此帳號".format(name))
continue
#print(name)
newname = input("請輸入新的姓名: ")
sql_3= '''UPDATE tb SET name="%s" WHERE name="%s"'''%(newname,name)
cur.execute(sql_3)
connection.commit()
cur.execute(sql_2)
person=cur.fetchone()
print(person)
print("修改完成！")
print()
break
def myindex():
print("{ 管理系統 }")
print("*----------------*")
print("1.登入")
print("2.註冊帳號")
print("3.結束程式")
def myindex2():
print("{ 請選擇您要做的項目 }")
print("*----------------*")
print("0.查詢個別人員的資料")
print("1.查詢所人員的資料")
print("2.修改個別人員的資料")
print("3.刪除個別人員的帳號")
print("4.離開")
def mymenu2():
while True:
myindex2()
item=int(input("請輸入您執行的動作: "))
if item == 1 :
all_info=get_all_info()
print(all_info)
print()
elif item == 0 :
info=get_info_one()
print(info)
print()
elif item == 2 :
update_name() #更新資料
print()
elif item == 3 :
delete_data() #刪除資料
print()
elif item == 4 :
break
def mymenu():
while True:
myindex() #起始頁面
num = int(input("請輸入您要執行的動作: "))
#print()
if num == 1 :
login()
elif num == 2 :
insert_data()
elif num == 3 :
break #離開while loop
#主菜單
mymenu()
#關閉資料庫連線
cur.close()
connection.close()