def delete():
try:
userinfo = input('输入你要删除的用户：').strip()
sql = "delete from user_list where name = '{}'".format(userinfo)
conn_db(sql)
except Exception as e:
print(e)
def update():
try:
userinfo = input('输入你要修改的用户：').strip()
update_info = input('输入你要修改的用户信息(age|tel|addr|name)：').strip()
value = input('输入值:').strip()
sql = "update user_list set {} = '{}' where name = '{}'".format(update_info,value,userinfo)
conn_db(sql)
except Exception as e:
print(e)
def add():
try:
userinfor = input('输入添加的用户信息(format：name age tel addr):').split()
if len(userinfor) != 4:
print('参数个数不对，请重新输入')
logging.warning('参数个数不对')
else:
sql = "insert into user_list (name,age,tel,addr) values ('{}',{},'{}','{}')".format(userinfor[0],int(userinfor[1]),userinfor[2],userinfor[3])
conn_db(sql)
logging.debug('用户{}添加成功。用户信息name = {},age = {},tel = {}, addr = {}'.format(userinfor[0],userinfor[0],int(userinfor[1]),userinfor[2],userinfor[3]))
except Exception as e:
print(e)
def main():
res = login()
cnt = 3
sql = "select if((select date_sub(now(),interval 1 day)) > (select max(lock_time) from admin_lock ),1,0); "
data = conn_db(sql)
while True:
if res.status_code == 200 and data[0][0] == 1:
print('登陆成功')
while True:
info ='''
* * * * * * * * * * * * * * * *
*1: 查看全部用户
*2：添加用户
*3：查找用户
*4：删除用户
*5：更新用户
*6：导出csv
*7：查看配置文件
*exit：退出系统 *
* * * * * * * * * * * * * * * *
'''
print(info)
op = input('输入动作：').strip()
if op == '1':
action.query()
if op == '2':
action.add()
if op == '3':
action.select()
if op == '4':
action.delete()
if op == '5':
action.update()
if op == '6':
action.dump_csv()
if op == '7':
action.config()
if op == 'exit':
sys.exit()
else:
print('登陆失败,还有{}次机会'.format(cnt - 1))
cnt = cnt - 1
print(cnt)
if cnt == 0:
print('用户已锁定，24小时后重新登录。')
sql = "insert into admin_lock(name) values ('*****@*****.**');"
conn_db(sql)
break
def exec_sql(sql_query):
text = ""
# function to establish connection with PSOTGRES DB
conn, cur = dbconnect.conn_db()
if conn != None and cur != None:
try:
text += "========================SQL Query Insertion Start=========================\n"
text += sql_query
cur.execute(sql_query)
conn.commit()
text += "\n==================RESULT: INSERT into table successful====================\n"
writeSQLData2file.write_to_file(text, "Insertquery-results.txt")
except UniqueViolation as e:
error = e.pgcode
text += "\n======RESULT: Execute sql error:A duplicate record already exists=========\n"
writeSQLData2file.write_to_file(text, "Insertquery-results.txt")
conn.rollback()
except (Exception, Error) as error:
print("\nExecute sql error:{}".format(error))
text += "\n================RESULT: Execute sql error:================================\n"
text += str(error)
text += "==========================================================================\n"
writeSQLData2file.write_to_file(text, "Insertquery-results.txt")
conn.rollback()
# close the cursor and connection
cur.close()
conn.close()
def test_conn_db_NOK(self):
dbname = postgres_dbconfig.dbname
user = postgres_dbconfig.user
host = postgres_dbconfig.host
password = "******"
port = postgres_dbconfig.port
self.assertEquals(
dbconnect.conn_db(dbname, user, host, password, port),
(None, None))
def query():
try:
sql = 'select * from user_list;'
data = conn_db(sql)
studo = PrettyTable()
studo.field_names = ['id', 'name', 'age', 'tel', 'adress', 'creaet_time', 'update_time']
for i in data:
studo.add_row(i)
print(studo)
except Exception as e:
print(e)
def dump_csv():
try:
sql = 'select * from user_list;'
data = conn_db(sql)
csvFile = open("test.csv", 'w', newline='')
writer = csv.writer(csvFile)
writer.writerow(('id', 'name', 'age', 'tel', 'addr', 'create_time', 'update_time'))
for i in data:
writer.writerow((i[0], i[1], i[2], i[3], i[4], i[5], i[6]))
csvFile.close()
print('导出cvs文件成功。')
except Exception as e:
print(e)
def main():
res = login()
cnt = 3
sql = "select if((select date_sub(now(),interval 1 day)) > (select max(lock_time) from admin_lock ),1,0); "
data = conn_db(sql)
while True:
if res.status_code == 200 and data[0][0] == 1:
print('登陆成功')
while True:
menu = "\nMenu: \n\t1:查看全部用户:\n\t2：添加用户:\n\t3：查找用户:\n\t4：删除用户：\n\t5：更新用户:\n\t6：导出csv:\n\t7：导出html\n\t8：查看配置文件\n\texit：退出系统\nPlease input your action:"
mapf = {'1':action.query, '2':action.add, '3':action.select, '4':action.delete, '5':action.update, '6':action.dump_csv, '8':action.config,'7':jinja1, 'exit':sys.exit}
op = input(menu).strip()
mapf[op]()
else:
print('登陆失败,还有{}次机会'.format(cnt - 1))
cnt = cnt - 1
print(cnt)
if cnt == 0:
print('用户已锁定，24小时后重新登录。')
sql = "insert into admin_lock(name) values ('*****@*****.**');"
conn_db(sql)
break
def select():
try:
username = input('输入你要查找的用户：').strip()
sql = "select * from user_list where name = '{}'".format(username)
data = conn_db(sql)
if data == ():
print('无此用户，请重新输入')
else:
studo = PrettyTable()
studo.field_names = ['id', 'name', 'age', 'tel', 'adress', 'creaet_time', 'update_time']
for i in data:
studo.add_row(i)
print(studo)
except Exception as e:
print(e)
