Python 22 - SQLite를 이용한 CRUD (mem 테이블)

패키지 구조

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>