Python
Python 22 - SQLite를 이용한 CRUD (mem 테이블)
inderrom
2023. 1. 10. 17:29
1. sqlite에서 테이블을 생성하고 db파일을 복사한다.
2. import를 위한 sqlite-jdbc-3.34.0.jar 파일을 넣어준다.
<daomem.py>
import sqlite3
class DaoMem:
def __init__(self):
self.conn = sqlite3.connect("mem.db", isolation_level=None)
self.cursor = self.conn.cursor()
def selects(self):
sql = f"""
SELECT * FROM member
order by m_id desc
"""
self.cursor.execute(sql)
list = self.cursor.fetchall()
mdict = []
for i in list:
mdict.append({"m_id":i[0],"m_nm":i[1],"mobile":i[2],"address":i[3]})
return mdict
def select(self,m_id):
sql = f"""
SELECT * FROM member
where
m_id = '{m_id}'
"""
self.cursor.execute(sql)
i = self.cursor.fetchall()[0]
emp = {"m_id":i[0],"m_nm":i[1],"mobile":i[2],"address":i[3]}
return emp
def insert(self,m_id,m_nm,mobile,address):
sql = f"""
insert into member
(m_id,m_nm,mobile,address)
values
('{m_id}','{m_nm}','{mobile}','{address}')
"""
self.cursor.execute(sql)
self.conn.commit()
cnt = self.cursor.rowcount
return cnt
def update(self,m_id,m_nm,mobile,address):
sql = f"""
update member
set
m_nm = '{m_nm}',
mobile = '{mobile}',
address = '{address}'
where
m_id = '{m_id}'
"""
self.cursor.execute(sql)
self.conn.commit()
cnt = self.cursor.rowcount
return cnt
def delete(self,m_id):
sql = f"""
delete from member
where
m_id = '{m_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 = DaoMem()
cnt = de.delete('6')
print(cnt)
cnt = de.update('3', '9', '9', '9')
print(cnt)<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 day12.daomem import DaoMem
app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")
@app.get("/")
@app.get("/mem_list", response_class=HTMLResponse)
async def mem_list(request: Request):
dm = DaoMem()
list = dm.selects();
return templates.TemplateResponse("mem_list.html", {"request": request,"list":list})
@app.get("/mem_detail", response_class=HTMLResponse)
async def emp_detail(request: Request,m_id):
dm = DaoMem()
mem = dm.select(m_id)
return templates.TemplateResponse("mem_detail.html", {"request": request,"m": mem})
@app.get("/mem_mod", response_class=HTMLResponse)
async def emp_mod(request: Request,m_id):
dm = DaoMem()
mem = dm.select(m_id)
return templates.TemplateResponse("mem_mod.html", {"request": request,"m": mem})
@app.post("/mem_mod_act", response_class=HTMLResponse)
async def mem_mod_act(request: Request,m_id= Form(),m_nm= Form(),mobile= Form(),address= Form()):
dm = DaoMem()
cnt = dm.update(m_id, m_nm, mobile, address)
return templates.TemplateResponse("mem_mod_act.html", {"request": request,"cnt": cnt})
@app.get("/mem_ins", response_class=HTMLResponse)
async def mem_ins(request: Request):
return templates.TemplateResponse("mem_ins.html", {"request": request})
@app.post("/mem_ins_act", response_class=HTMLResponse)
async def mem_ins_act(request: Request,m_id= Form(),m_nm= Form(),mobile= Form(),address= Form()):
dm = DaoMem()
cnt = dm.insert(m_id, m_nm, mobile, address)
return templates.TemplateResponse("mem_ins_act.html", {"request": request,"cnt": cnt})
@app.post("/mem_del_act", response_class=HTMLResponse)
async def mem_del_act(request: Request,m_id= Form()):
dm = DaoMem()
cnt = dm.delete(m_id)
return templates.TemplateResponse("mem_del_act.html", {"request": request,"cnt": cnt})
if __name__ == "__main__":
uvicorn.run(app, host="localhost", port=8000)
LIST
<mem_list.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function fn_ins(){
location.href = "mem_ins";
}
</script>
</head>
<body>
<table border="1px">
<tr>
<td>아이디</td>
<td>이름</td>
<td>휴대폰</td>
<td>주소</td>
</tr>
{% for m in list %}
<tr>
<td><a href="mem_detail?m_id={{m.m_id}}">{{m.m_id}}</a></td>
<td>{{m.m_nm}}</td>
<td>{{m.mobile}}</td>
<td>{{m.address}}</td>
</tr>
{% endfor %}
</table>
<input type="button" value="추가" onclick="fn_ins()" >
</body>
</html>DETAIL
<mem_detail.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function fn_mod(){
location.href = "mem_mod?m_id={{m.m_id}}";
}
function fn_del_act(){
var frm = document.getElementById("frm");
frm.submit();
}
</script>
</head>
<body>
MEM_DETAIL<br/>
<form id="frm" action="mem_del_act" method="post">
<input type="hidden" name="m_id" value="{{m.m_id}}" />
</form>
<table border="1px">
<tr>
<td>아이디</td>
<td>{{m.m_id}}</td>
</tr>
<tr>
<td>이름</td>
<td>{{m.m_nm}}</td>
</tr>
<tr>
<td>휴대폰</td>
<td>{{m.mobile}}</td>
</tr>
<tr>
<td>주소</td>
<td>{{m.address}}</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>INSERT
<mem_ins.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
MEM_INS<br/>
<form action="mem_ins_act" method="post" >
<table border="1px">
<tr>
<td>사번</td>
<td><input type="text" name="m_id" value="" /></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="m_nm" value="" /></td>
</tr>
<tr>
<td>성별</td>
<td><input type="text" name="mobile" value="" /></td>
</tr>
<tr>
<td>주소</td>
<td><input type="text" name="address" value="" /></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="실행"/>
</td>
</tr>
</table>
</form>
</body>
</html><mem_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 = "mem_list";
} else {
alert("수정도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
MEM_INS_ACT<br/>
</body>
</html>
UPDATE
<mem_mod.html>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
MEM_MOD<br/>
<form action="mem_mod_act" method="post" >
<table border="1px">
<tr>
<td>사번</td>
<td><input type="text" name="m_id" value="{{m.m_id}}" /></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="m_nm" value="{{m.m_nm}}" /></td>
</tr>
<tr>
<td>성별</td>
<td><input type="text" name="mobile" value="{{m.mobile}}" /></td>
</tr>
<tr>
<td>주소</td>
<td><input type="text" name="address" value="{{m.address}}" /></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="실행"/>
</td>
</tr>
</table>
</form>
</body>
</html><mem_mod_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 = "mem_list";
} else {
alert("수정도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
MEM_MOD_ACT<br/>
</body>
</html>
DELETE
<mem_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 = "mem_list";
} else {
alert("삭제도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
EMP_DEL_ACT<br/>
</body>
</html>