PYTHON
python과 mssql DB연동하여 CRUD하기 2
9400
2023. 1. 9. 16:54
myfast01.py
from fastapi import FastAPI,Form,Request
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from fastapi.responses import HTMLResponse
import pymssql
import uvicorn
from day10.daoemp import DaoEmp
app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")
de = DaoEmp()
@app.get("/")
async def root():
return "Hello World"
@app.get("/emp_list", response_class=HTMLResponse)
async def emp_list(request: Request):
mdict = de.selects()
return templates.TemplateResponse("emp_list.html", {"request": request,"mdict": mdict})
@app.get("/emp_detail", response_class=HTMLResponse)
async def emp_detail(request: Request,e_id):
mdict = de.select(e_id);
print(mdict)
return templates.TemplateResponse("emp_detail.html", {"request": request,"mdict": mdict})
@app.get("/emp_mod", response_class=HTMLResponse)
async def emp_mod(request: Request,e_id):
mdict = de.select(e_id);
return templates.TemplateResponse("emp_mod.html", {"request": request,"mdict": mdict})
@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()):
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()):
cnt = de.insert(e_id, e_name,sex,addr)
mdict = de.select(e_id)
return templates.TemplateResponse("emp_ins_act.html", {"request": request,"cnt": cnt ,"mdict": mdict})
@app.post("/emp_del", response_class=HTMLResponse)
async def emp_del(request: Request,e_id=Form()):
print(e_id);
cnt = de.delete(e_id);
return templates.TemplateResponse("emp_del.html", {"request": request,"cnt": cnt})
if __name__ == "__main__":
uvicorn.run(app, host="localhost", port=8000)daoemp.py
import pymssql
class DaoEmp:
def __init__(self):
self.conn = pymssql.connect(host="192.168.145.14",database="python",user="sa",password="python")
self.cursor = self.conn.cursor()
def selects(self):
self.cursor.execute('SELECT * FROM emp order by e_id desc')
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
def select(self,e_id):
sql =f"""
SELECT * FROM emp
where e_id = '{e_id}'
"""
self.cursor.execute(sql)
list = self.cursor.fetchall()
i =list[0]
mdict = {"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]}
return mdict
def insert(self,e_id,e_name,sex,addr):
sql =f"""
insert into emp
values ('{e_id}','{e_name}','{sex}','{addr}')
"""
self.cursor.execute(sql)
self.conn.commit()
cnt = self.cursor.rowcount
return cnt
def update(self,e_id,e_name,sex,addr):
sql =f"""
update emp
set e_name = '{e_name}',
sex = '{sex}',
addr = '{addr}'
where e_id = '{e_id}'
"""
self.cursor.execute(sql)
self.conn.commit()
cnt = self.cursor.rowcount
return cnt
def delete(self,e_id):
sql =f"""
delete from emp
where e_id = '{e_id}'
"""
self.cursor.execute(sql)
self.conn.commit()
cnt = self.cursor.rowcount
return cnt
def __del__(self):
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
de = DaoEmp()
emp = de.delete('6')
print(emp)
emp_list
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function ins(){
location.href="/emp_ins";
}
</script>
</head>
<body>
<table border="1">
<tr>
<td>사번</td>
<td>이름</td>
<td>성별</td>
<td>주소</td>
</tr>
{% for x in mdict %}
<tr>
<td><a href="/emp_detail?e_id={{ x.e_id }}">{{ x.e_id }}</a></td>
<td>{{ x.e_name }}</td>
<td>{{ x.sex }}</td>
<td>{{ x.addr }}</td>
</tr>
{% endfor%}
</table>
<input type="button" value="추가" onclick="ins()">
</body>
</html>
emp_detail
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
Fn_mod= function() {
location.href="/emp_mod?e_id={{ mdict.e_id }}";
}
function Fn_del(){
var frm = document.getElementById("frm");
frm.submit();
}
</script>
</head>
<body>
EMP_DETAIL
<form id="frm" action="/emp_del" method="post">
<input type="hidden" name="e_id" value="{{mdict.e_id}}" />
</form>
<table border="1">
<tr>
<td>사번</td>
<td>{{ mdict.e_id }}</td>
</tr>
<tr>
<td>이름</td>
<td>{{ mdict.e_name }}</td>
</tr>
<tr>
<td>성별</td>
<td>{{ mdict.sex }}</td>
</tr>
<tr>
<td>주소</td>
<td>{{ mdict.addr }}</td>
</tr>
<tr>
<td colspan="2">
<button type="button" onclick="Fn_mod()">수정</button>
<input type="button" onclick="Fn_del()" value="삭제" />
</td>
</tr>
</table>
</form>
</body>
</html>
삭제
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
let cnt = "{{cnt}}"
if(cnt == 1){
alert("정상적으로 삭제되었습니다.");
location.href="emp_list";
}else{
alert("삭제 도중 문제가 생겼습니다.")
history.back();
}
</script>
</head>
<body>
delete
</body>
</html>
emp_ins
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="/emp_ins_act" method="post">
<table border="1">
<tr>
<td>사번</td>
<td><input type="text" name="e_id" /></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="e_name" /></td>
</tr>
<tr>
<td>성별</td>
<td><input type="text" name="sex" /></td>
</tr>
<tr>
<td>주소</td>
<td><input type="text" name="addr"/></td>
</tr>
<tr>
<td colspan="4">
<input type="submit" value="확인" >
</td>
</tr>
</table>
</form>
</body>
</html>
emp_ins_act
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
let cnt = "{{cnt}}"
if(cnt == 1){
alert("정상적으로 등록되었습니다.");
location.href="emp_list";
}else{
alert("등록 도중 문제가 생겼습니다.")
history.back();
}
</script>
</head>
<body>
</body>
</html>
emp_mod
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
EMP_MOD
{{mdict.e_id}}
<form action="emp_mod_act" method="post">
<table border="1">
<tr>
<td>사번</td>
<td><input type="text" name="e_id" value="{{mdict.e_id}}"/></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="e_name" value="{{mdict.e_name}}" /></td>
</tr>
<tr>
<td>성별</td>
<td><input type="text" name="sex" value="{{mdict.sex}}" /></td>
</tr>
<tr>
<td>주소</td>
<td><input type="text" name="addr" value="{{mdict.addr}}" /></td>
</tr>
<tr>
<td colspan="4">
<input type="submit" value="실행" >
</td>
</tr>
</table>
</form>
</body>
</html>
emp_mod_act
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<script type="text/javascript">
var cnt = "{{cnt}}"
if(cnt == 1){
alert("정상적으로 수정되었습니다.");
location.href="emp_list";
}else{
alert("수정 도중 문제가 생겼습니다.")
history.back();
}
</script>
<title>Insert title here</title>
</head>
<body>
act
</body>
</html>
