Python
Python 20 - CRUD (emp 테이블)
inderrom
2023. 1. 6. 18:56
emp 테이블 crud 화면 구조
emp_list : (리스트)
emp_detail : (상세보기)
emp_mod (수정폼)
emp_mod_act (수정)
emp_del_act (삭제)
emp_ins
act : act가 붙은 건 화면에 띄워지지 않음
EMPLIST - 전체 리스트 출력
<main.py>
from fastapi import FastAPI,Form,Request
import uvicorn
from starlette.responses import HTMLResponse
from starlette.staticfiles import StaticFiles
from starlette.templating import Jinja2Templates
import pymssql
from day10.daoemp import DaoEmp
app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")
@app.get("/")
@app.get("/emp_list", response_class=HTMLResponse)
async def emp_list(request: Request):
de = DaoEmp()
list = de.selects()
return templates.TemplateResponse("emp_list.html", {"request": request,"list": list})
@app.get("/emp_detail", response_class=HTMLResponse)
async def emp_detail(request: Request,e_id):
de = DaoEmp()
emp = de.select(e_id)
return templates.TemplateResponse("emp_detail.html", {"request": request,"e": emp})
@app.get("/emp_mod", response_class=HTMLResponse)
async def emp_mod(request: Request,e_id):
de = DaoEmp()
emp = de.select(e_id)
return templates.TemplateResponse("emp_mod.html", {"request": request,"e": emp})
@app.post("/emp_mod_act", response_class=HTMLResponse)
async def emp_mod_act(request: Request,e_id= Form(),e_name= Form(),sex= Form(),addr= Form()):
de = DaoEmp()
cnt = de.update(e_id, e_name, sex, addr)
return templates.TemplateResponse("emp_mod_act.html", {"request": request,"cnt": cnt})
@app.get("/emp_ins", response_class=HTMLResponse)
async def emp_ins(request: Request):
return templates.TemplateResponse("emp_ins.html", {"request": request})
@app.post("/emp_ins_act", response_class=HTMLResponse)
async def emp_ins_act(request: Request,e_id= Form(),e_name= Form(),sex= Form(),addr= Form()):
de = DaoEmp()
cnt = de.insert(e_id, e_name, sex, addr)
return templates.TemplateResponse("emp_ins_act.html", {"request": request,"cnt": cnt})
@app.post("/emp_del_act", response_class=HTMLResponse)
async def emp_del_act(request: Request,e_id=Form()):
de = DaoEmp()
cnt = de.delete(e_id)
return templates.TemplateResponse("emp_del_act.html", {"request": request,"cnt": cnt})
if __name__ == "__main__":
uvicorn.run(app, host="localhost", port=8000)daoemp.py 에서 만들었던 메소드를 가져와 연결
전체 리스트 출력
<emp_list.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
EMP DETAIL <br/>
<table border="1">
<tr>
<td>사번</td>
<td>{{emp.e_id}}</td>
<tr>
<tr>
<td>이름</td>
<td>{{emp.e_name}}</td>
<tr>
<tr>
<td>성</td>
<td>{{emp.sex}}</td>
<tr>
<tr>
<td>주소</td>
<td>{{emp.addr}}</td>
<tr>
<tr>
<td colspan="2">
<input type="button" value="수정"/>
<input type="button" value="삭제"/>
</td>
<tr>
</table>
</body>
</html>
상세보기
<emp_detail.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function fn_mod(){
location.href = "emp_mod?e_id={{e.e_id}}";
}
function fn_del_act(){
var frm = document.getElementById("frm");
frm.submit();
}
</script>
</head>
<body>
EMP_DETAIL<br/>
<form id="frm" action="emp_del_act" method="post">
<input type="hidden" name="e_id" value="{{e.e_id}}">
</form>
<table border="1px">
<tr>
<td>사번</td>
<td>{{e.e_id}}</td>
</tr>
<tr>
<td>이름</td>
<td>{{e.e_name}}</td>
</tr>
<tr>
<td>성별</td>
<td>{{e.sex}}</td>
</tr>
<tr>
<td>주소</td>
<td>{{e.addr}}</td>
</tr>
<tr>
<td colspan="2">
<input type="button" value="수정" onclick="fn_mod()"/>
<input type="button" value="삭제" onclick="fn_del_act()"/>
</td>
</tr>
</table>
</body>
</html>
결과
수정하기
<emp_mod.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
EMP_MOD<br/>
<form action="emp_mod_act" method="post">
<table border="1px">
<tr>
<td>사번</td>
<td>
<input type="text" id="id" name="e_id" value="{{emp.e_id}}"/>
</td>
</tr>
<tr>
<td>이름</td>
<td>
<input type="text" id="name" name="e_name" value="{{emp.e_name}}"/>
</td>
</tr>
<tr>
<td>성</td>
<td>
<input type="text" id="sex" name="sex" value="{{emp.sex}}"/>
</td>
</tr>
<tr>
<td>주소</td>
<td>
<input type="text" id="addr" name="addr" value="{{emp.addr}}"/>
</td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="실행" />
</td>
</tr>
</table>
</form>
</body>
</html>
<emp_mod_act.html> - POST방식
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script>
var cnt = "{{cnt}}"
if(cnt == 1){
alert("정상적으로 수정되었습니다.")
location.href="emp_list";
}else{
alert("수정도중 문제가 생겼습니다.")
history.back();
}
</script>
</head>
<body>
EMP_MOD_ACT <br>
</body>
</html>
수정이 되면 전체 리스트 출력 화면으로 넘어간다
추가하기
<main.py> ins를 추가해준다.
@app.get("/emp_ins", response_class=HTMLResponse)
async def emp_ins(request: Request):
return templates.TemplateResponse("emp_ins.html", {"request": request})
@app.post("/emp_ins_act", response_class=HTMLResponse)
async def emp_ins_act(request: Request,e_id= Form(),e_name= Form(),sex= Form(),addr= Form()):
de = DaoEmp()
cnt = de.insert(e_id, e_name, sex, addr)
return templates.TemplateResponse("emp_ins_act.html", {"request": request,"cnt": cnt})
<emp_ins_html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
EMP_INS<br/>
<form action="emp_ins_act" method="post" >
<table border="1px">
<tr>
<td>사번</td>
<td><input type="text" name="e_id" value="" /></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="e_name" value="" /></td>
</tr>
<tr>
<td>성별</td>
<td><input type="text" name="sex" value="" /></td>
</tr>
<tr>
<td>주소</td>
<td><input type="text" name="addr" value="" /></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="완료"/>
</td>
</tr>
</table>
</form>
</body>
</html><emp_ins_act.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
var cnt = "{{cnt}}";
if(cnt == 1){
alert("정상적으로 추가되었습니다.");
location.href = "emp_list";
} else {
alert("추가도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
EMP_INS_ACT<br/>
</body>
</html>
삭제하기
메인에 del 추가하기
@app.post("/emp_del_act", response_class=HTMLResponse)
async def emp_del_act(request: Request,e_id=Form()):
de = DaoEmp()
cnt = de.delete(e_id)
return templates.TemplateResponse("emp_del_act.html", {"request": request,"cnt": cnt})
<mep_del_act.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
var cnt = "{{cnt}}";
if(cnt == 1){
alert("정상적으로 삭제되었습니다.");
location.href = "emp_list";
} else {
alert("삭제도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
EMP_MOD_ACT<br/>
</body>
</html>
리스트의 정렬 순서를 고치고 싶다면
daoemp의 select문에 orderby를 추가해준다.
def selects(self):
sql = f"""
SELECT * FROM emp
order by e_id desc
"""
self.cursor.execute(sql)
list = self.cursor.fetchall()
mdict = []
for i in list:
mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
return mdict