[Python] SPA 형태의 CRUD

JAVA/JSP

[Python] SPA 형태의 CRUD

아잠만_ 2024. 7. 10. 11:27

SPA(Single Page Application)

서버로부터 완전한 새로운 페이지를 불러오지 않고 현재의 페이지를 동적으로 다시 작성함

ajax(axios로 CRUD) 생성

daoemp.py

import pymysql
class DaoEmp :
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1', 
                       port=3305,
                       user='root', 
                       password='python',
                       db='python', 
                       charset='utf8')

        # DictCursor, Python에서 MySQL 쿼리 결과에 컬럼 자동으로 붙이기
        # 칼럼이름 : value형식으로 출력됨
        self.cur = self.conn.cursor(pymysql.cursors.DictCursor)
        
    def selectList(self):
        # sql문
        sql = f"""
            SELECT *
            FROM emp
            """
        # sql문을 cur에 입력
        self.cur.execute(sql)
        # 데이타 Fetch (가져오기)
        rows = self.cur.fetchall()
        return rows
    
    def selectOne(self, eId):
        # sql문
        sql = f"""
            SELECT *
            FROM emp
            WHERE e_id='{eId}'
            """
        # sql문을 cur에 입력
        self.cur.execute(sql)
        # 데이타 Fetch (가져오기)
        vo = self.cur.fetchone()
        return vo
        # list = self.cur.fetchall()
        # return list[0]
    
    def insert(self, eId, eName, gen, addr):
        # sql문
        sql = f"""
            INSERT INTO emp
            VALUES({eId}, {eName}, {gen}, {addr})
            """
        # sql문을 cur에 입력
        cnt = self.cur.execute(sql)
        self.conn.commit()
        return cnt
        
    def update(self, eId, eName, gen, addr):
        # sql문
        sql = f"""
            UPDATE emp
            SET e_name='{eName}', gen='{gen}', addr='{addr}'
            WHERE e_id='{eId}'
            """
        # sql문을 cur에 입력
        cnt = self.cur.execute(sql)
        self.conn.commit()
        return cnt
        
    def delete(self, eId):
        # sql문
        sql = f"""
            DELETE FROM emp
            WHERE e_id ='{eId}'
            """
        # sql문을 cur에 입력
        cnt = self.cur.execute(sql)
        self.conn.commit()
        return cnt
    
    
    def __del__(self):
        self.cur.close()
        self.conn.close()
        
if __name__ == '__main__':
    de = DaoEmp()
    cnt = de.insert(6, 6, 6, 6)
    print(cnt)
    list = de.selectOne(6)
    print(list)

myflask.py

from flask import Flask, redirect
from flask.json import jsonify
from flask.globals import request
from day12.daoemp import DaoEmp

app = Flask(__name__)

@app.route('/')
def main():
    return redirect('/static/emp.html')

@app.route('/emp_list.ajax', methods=['POST'])
def ajax_emp_list():
    dao = DaoEmp()
    data = dao.selectList()
    return jsonify({"emp" : data})

@app.route('/emp_one.ajax', methods=['POST'])
def ajax_emp_one():
    dao = DaoEmp()
    e_id = request.get_json()['e_id']
    data = dao.selectOne(e_id)
    return jsonify({"emp" : data})

@app.route('/emp_add.ajax', methods=['POST'])
def ajax_emp_add():
    data = request.get_json()
    e_id = data['e_id']
    e_name = data['e_name']
    gen = data['gen']
    addr = data['addr']
    dao = DaoEmp()
    cnt = dao.insert(e_id, e_name, gen, addr)
    return jsonify({"cnt" : cnt})

@app.route('/emp_modify.ajax', methods=['POST'])
def ajax_emp_modify():
    data = request.get_json()
    e_id = data['e_id']
    e_name = data['e_name']
    gen = data['gen']
    addr = data['addr']
    dao = DaoEmp()
    cnt = dao.update(e_id, e_name, gen, addr)
    return jsonify({"cnt" : cnt})

@app.route('/emp_remove.ajax', methods=['POST'])
def ajax_emp_remove():
    data = request.get_json()
    e_id = data['e_id']
    dao = DaoEmp()
    cnt = dao.delete(e_id)
    return jsonify({"cnt" : cnt})

if __name__ == '__main__':
    # app.run(debug=True)
    app.run(host="0.0.0.0", port=80, debug=True)

emp.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
input[type="text"]{
	width: 100px;
}
</style>
<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script>
window.onload = function(){
	fn_list();
}
function fn_list(){
	// emp_list.ajax
	axios.post('/emp_list.ajax').then(function(res){
		let emp = res.data.emp;
		let str = "";
		emp.forEach(function(a){
			str += `<tr>
						<td><a href="javascript:fn_one(${a.e_id})">${a.e_id}</a></td>
						<td>${a.e_name}</td>
						<td>${a.gen}</td>
						<td>${a.addr}</td>
					</tr>`;
		}) 
		if(emp.length==0){
			str = `<tr>
					<td colspan="4">검색된 데이터가 없습니다</td>
				   </tr>`;
		}
		document.querySelector('#my_tbody').innerHTML = str;
	});
}
function fn_one(e_id){
	axios.post('/emp_one.ajax', {"e_id" : e_id}).then(function(res){
		let emp = res.data.emp;
		document.querySelector('#e_id').value = emp.e_id;
		document.querySelector('#e_name').value = emp.e_name;
		document.querySelector('#gen').value = emp.gen;
		document.querySelector('#addr').value = emp.addr;
	});
}

function add(){
	let id = document.querySelector('#e_id').value;
	let name = document.querySelector('#e_name').value;
	let gen = document.querySelector('#gen').value;
	let addr = document.querySelector('#addr').value;
	
	let data = {
			"e_id" : id,
			"e_name" : name,
			"gen" : gen,
			"addr" : addr
	};
	axios.post('/emp_add.ajax', data).then(function(res){
		var cnt = res.data.cnt
		if(cnt==0){
			alert('문제가 생겨 추가가 완료되지 않았습니다')
			return;
		}
		fn_list();
		reset();
	});
}
function modify(){
	let id = document.querySelector('#e_id').value;
	let name = document.querySelector('#e_name').value;
	let gen = document.querySelector('#gen').value;
	let addr = document.querySelector('#addr').value;
	
	let data = {
			"e_id" : id,
			"e_name" : name,
			"gen" : gen,
			"addr" : addr
	};
	axios.post('/emp_modify.ajax', data).then(function(res){
		var cnt = res.data.cnt
		if(cnt==0){
			alert('문제가 생겨 수정이 완료되지 않았습니다')
			return;
		}
		fn_list();
		reset();
	});
}
function remove(){
	let id = document.querySelector('#e_id').value;
	let data = {
			"e_id" : id,
	};
	if(confirm("정말로 삭제하시겠습니까?")){
		axios.post('/emp_remove.ajax', data).then(function(res){
			var cnt = res.data.cnt
			if(cnt==0){
				alert('문제가 생겨 삭제가 완료되지 않았습니다');
				return;
			}
			fn_list();
			reset();
		});
	}
}

function reset(){
	document.querySelector('#e_id').value = "";
	document.querySelector('#e_name').value = "";
	document.querySelector('#gen').value = "";
	document.querySelector('#addr').value = "";
}
</script>
</head>
<body>
<a href="javascript:fn_list()">목록 가져오기</a>
<table border="1">
	<thead>
		<tr>
			<th>사번</th>
			<th>이름</th>
			<th>성별</th>
			<th>주소</th>
		</tr>
	</thead>
	<tbody id="my_tbody">
		<tr>
			<td colspan="4">검색된 데이터가 없습니다</td>
		</tr>
	</tbody>
</table>

<br>

<table border="1">
	<tbody>
		<tr>
			<th>사번</th>
			<td><input type="text" id="e_id"></td>			
		</tr>
		<tr>
			<th>이름</th>
			<td><input type="text" id="e_name"></td>			
		</tr>
		<tr>
			<th>성별</th>
			<td><input type="text" id="gen"></td>			
		</tr>
		<tr>
			<th>주소</th>
			<td><input type="text" id="addr"></td>			
		</tr>
		<tr>
			<td colspan="2">
				<input type="button" value="추가" onclick="add()">
				<input type="button" value="수정" onclick="modify()">
				<input type="button" value="삭제" onclick="remove()">
			</td>
		</tr>
	</tbody>
</table>


</body>
</html>

jquery ajax로 CRUD

(dao는 동일)

myflask.py

from flask import Flask, redirect
from flask.json import jsonify
from flask.globals import request
from day13.daoemp import DaoEmp

app = Flask(__name__)

@app.route('/')
def main():
    return redirect('/static/emp.html')

@app.route('/emp_list.ajax', methods=['POST'])
def ajax_emp_list():
    dao = DaoEmp()
    data = dao.selectList()
    return jsonify({"emp" : data})

@app.route('/emp_one.ajax', methods=['POST'])
def ajax_emp_one():
    dao = DaoEmp()
    e_id = request.form['e_id']
    data = dao.selectOne(e_id)
    return jsonify({"emp" : data})

@app.route('/emp_add.ajax', methods=['POST'])
def ajax_emp_add():
    e_id = request.form['e_id']
    e_name = request.form['e_name']
    gen = request.form['gen']
    addr = request.form['addr']
    print(e_id)
    dao = DaoEmp()
    cnt = dao.insert(e_id, e_name, gen, addr)
    return jsonify({"cnt" : cnt})

@app.route('/emp_modify.ajax', methods=['POST'])
def ajax_emp_modify():
    e_id = request.form['e_id']
    e_name = request.form['e_name']
    gen = request.form['gen']
    addr = request.form['addr']
    dao = DaoEmp()
    cnt = dao.update(e_id, e_name, gen, addr)
    return jsonify({"cnt" : cnt})

@app.route('/emp_remove.ajax', methods=['POST'])
def ajax_emp_remove():
    e_id = request.form['e_id']
    dao = DaoEmp()
    cnt = dao.delete(e_id)
    return jsonify({"cnt" : cnt})

if __name__ == '__main__':
    # app.run(debug=True)
    app.run(host="0.0.0.0", port=80, debug=True)

emp.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
input[type="text"]{
	width: 100px;
}
</style>
<script src="./jquery-3.7.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script>
window.onload = function(){
	fn_list();
}
function fn_list(){
	// emp_list.ajax
	$.ajax({
		url : "/emp_list.ajax",
		type : "POST",
		success : function(res){
			// console.log(res);
			let emp = res.emp;
			let str = "";
			$.each(emp, function(){
				str += `<tr>
							<td><a href="javascript:fn_one(${this.e_id})">${this.e_id}</a></td>
							<td>${this.e_name}</td>
							<td>${this.gen}</td>
							<td>${this.addr}</td>
						</tr>`;
			}) 
			if(emp.length==0){
				str = `<tr>
						<td colspan="4">검색된 데이터가 없습니다</td>
					   </tr>`;
			}
			$('#my_tbody').html(str);
		}
	})
}
function fn_one(e_id){
	$.ajax({
		url : "/emp_one.ajax",
		type : "POST",
		data : {"e_id" : e_id},
		success : function(res){
			let emp = res.emp;
			$('#e_id').val(emp.e_id);
			$('#e_name').val(emp.e_name);
			$('#gen').val(emp.gen);
			$('#addr').val(emp.addr);
		}
	});
}

function add(){
	let id = $('#e_id').val();
	let name = $('#e_name').val();
	let gen = $('#gen').val();
	let addr = $('#addr').val();
	
	let data = {
			"e_id" : id,
			"e_name" : name,
			"gen" : gen,
			"addr" : addr
	};
	$.ajax({
		url : "/emp_add.ajax",
		type : "POST",
		data : data,
		success : function(res){
			// console.log(cnt);
			var cnt = res.cnt
			if(cnt==0){
			alert('문제가 생겨 추가가 완료되지 않았습니다')
			return;
		}
		fn_list();
		reset();
		}
	});
}
function modify(){
	let id = $('#e_id').val();
	let name = $('#e_name').val();
	let gen = $('#gen').val();
	let addr = $('#addr').val();
	
	let data = {
			"e_id" : id,
			"e_name" : name,
			"gen" : gen,
			"addr" : addr
	};
	$.ajax({
		url : "/emp_modify.ajax",
		type : "POST",
		data : data,
		success : function(res){
			var cnt = res.cnt
			if(cnt==0){
			alert('문제가 생겨 수정이 완료되지 않았습니다')
			return;
		}
		fn_list();
		reset();
		}
	});
}
function remove(){
	let id = $('#e_id').val();
	if(confirm("정말로 삭제하시겠습니까?")){
		$.ajax({
			url : "/emp_remove.ajax",
			type : "POST",
			data : {"e_id" : id},
			success : function(res){
				var cnt = res.cnt
				if(cnt==0){
				alert('문제가 생겨 삭제가 완료되지 않았습니다');
				return;
			}
			fn_list();
			reset();
			}
		});
	}
}

function reset(){
	$('#e_id').val("");
	$('#e_name').val("");
	$('#gen').val("");
	$('#addr').val("");
}
</script>
</head>
<body>
<a href="javascript:fn_list()">목록 가져오기</a>
<table border="1">
	<thead>
		<tr>
			<th>사번</th>
			<th>이름</th>
			<th>성별</th>
			<th>주소</th>
		</tr>
	</thead>
	<tbody id="my_tbody">
		<tr>
			<td colspan="4">검색된 데이터가 없습니다</td>
		</tr>
	</tbody>
</table>

<br>

<table border="1">
	<tbody>
		<tr>
			<th>사번</th>
			<td><input type="text" id="e_id"></td>			
		</tr>
		<tr>
			<th>이름</th>
			<td><input type="text" id="e_name"></td>			
		</tr>
		<tr>
			<th>성별</th>
			<td><input type="text" id="gen"></td>			
		</tr>
		<tr>
			<th>주소</th>
			<td><input type="text" id="addr"></td>			
		</tr>
		<tr>
			<td colspan="2">
				<input type="button" value="추가" onclick="add()">
				<input type="button" value="수정" onclick="modify()">
				<input type="button" value="삭제" onclick="remove()">
			</td>
		</tr>
	</tbody>
</table>
</body>
</html>

MVC MVVM 비교

MVC	MVVM
화면 깜빡 (화면이 넘어감)	한 화면에서 이루어짐
화면 이동 용이
크롤링이 용이