[파이썬] 오라클 DB 연동 CRUD MVVM, ajax방식 / (HELLO_EMP_MVVM)

settings.py

urls.py

from django.contrib import admin
from django.urls import path
from HELLO_EMP_MVVM import views

urlpatterns = [
    path('', views.emp),
    path('emp', views.emp),
    path('emp_selects', views.emp_selects),
    path('emp_select', views.emp_select),
    path('emp_insert', views.emp_insert),
    path('emp_update', views.emp_update),
    path('emp_delete', views.emp_delete),
]

views.py

from django.shortcuts import render
from django.http.response import HttpResponse, JsonResponse
from HELLO_EMP_MVVM.dao_emp import DaoEmp
from django.views.decorators.csrf import csrf_exempt


def emp(request):
    return render(request, 'emp.html')


def emp_selects(request):    
    de = DaoEmp()
    emps = de.myselects()
    data={'emps': emps}
    return JsonResponse(data)


def emp_select(request):    
    e_id = request.GET.get('e_id', '')
    de = DaoEmp()
    emp = de.myselect(e_id)
    data={'emp': emp}
    return JsonResponse(data)

@csrf_exempt 
#보안을 일시적으로 풀어주는 용도
def emp_insert(request):
    e_name = request.POST.get('e_name','')
    sex = request.POST.get('sex','')
    addr = request.POST.get('addr','')
    de = DaoEmp()
    cnt = de.myinsert(e_name, sex, addr)
    data={'cnt': cnt}
    return JsonResponse(data)

@csrf_exempt
def emp_update(request):
    e_id = request.POST.get('e_id','')
    e_name = request.POST.get('e_name','')
    sex = request.POST.get('sex','')
    addr = request.POST.get('addr','')
    de = DaoEmp()
    cnt = de.myupdate(e_id, e_name, sex, addr)
    data={'cnt': cnt}
    return JsonResponse(data)

@csrf_exempt
def emp_delete(request):
    e_id = request.POST.get('e_id','')
    de = DaoEmp()
    cnt = de.mydelete(e_id)
    data={'cnt': cnt}
    return JsonResponse(data)

dao_emp.py

import cx_Oracle

class DaoEmp:
    def __init__(self):
        self.conn = cx_Oracle.connect('python/python@localhost:1521/xe')
        self.cs = self.conn.cursor()
        
        
    def myselects(self):
        rs = self.cs.execute("select e_id,e_name,sex,addr from emp order by 1")
        emps = []    
        for r in rs:
            emps.append({'e_id':r[0],'e_name':r[1],'sex':r[2],'addr':r[3]})
        return emps
    
    def myselect(self,e_id):
        sql = f"""
            select 
                e_id,
                e_name,
                sex,
                addr
            from 
                emp
            where 
                e_id = '{e_id}'
        """
        rs = self.cs.execute(sql)
        for r in rs:
            emp = {'e_id':r[0],'e_name':r[1],'sex':r[2],'addr':r[3]}
        
        return emp
    
    def myinsert(self,e_name,sex,addr):
        sql = f"""
            insert into emp 
            (e_id,e_name,sex,addr)
            values 
            (e_seq.nextval,'{e_name}','{sex}','{addr}')
        """
        cnt = 0
        try:
            self.cs.execute(sql)
            self.conn.commit()
            cnt = self.cs.rowcount
        except: 
            print("insert error")
            cnt = 0
        return cnt
    
    def myupdate(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}'
        """
        cnt = 0
        try:
            self.cs.execute(sql)
            self.conn.commit()
            cnt = self.cs.rowcount
        except: 
            print("update error")
            cnt = 0
        return cnt
    
    def mydelete(self,e_id):
        sql = f"""
            delete from emp
            where 
                e_id ='{e_id}'
        """
        cnt = 0
        try:
            self.cs.execute(sql)
            self.conn.commit()
            cnt = self.cs.rowcount
        except: 
            print("delete error")
            cnt = 0
        return cnt
    
    def __del__(self):
        self.cs.close()    
        self.conn.close()
        

if __name__ == '__main__':
    de = DaoEmp()
    cnt = de.mydelete('23')
    print(cnt)# 1

emp.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<script type="text/javascript">
function fn_list(){
	$.ajax({
        url: 'emp_selects',
        type: 'GET',
        data: {
            'a':'777'
        },
        datatype: 'json', 
        success: function(data){ 
        	fn_list_cb(data.emps);
        	
        }
    });
}


function fn_list_cb(emps){
    console.log(emps);	
    var obj_disp = document.querySelector("#mydisp");
    
    var txt = "";
    
    for(var i=0;i<emps.length;i++){
        txt +="<tr>\n";
        txt +="	<td><a href='javascript:fn_one("+emps[i].e_id+")'>"+emps[i].e_id+"</a></td>\n";
        txt +="	<td>"+emps[i].e_name+"</td>\n";
        txt +="	<td>"+emps[i].sex+"</td>\n";
        txt +="	<td>"+emps[i].addr+"</td>\n";
        txt +="</tr>\n";    	
    }
    if(emps.length == 0){
    	txt += "<tr>";
    	txt += "<td colspan='4'>데이터가 없습니다.</td>";
    	txt += "</tr>";
    }

    
    obj_disp.innerHTML = txt;
}

function fn_one(e_id){
	console.log("e_id",e_id);
	$.ajax({
        url: 'emp_select',
        type: 'GET',
        data: {
            'e_id':e_id
        },
        datatype: 'json', 
        success: function(data){ 
        	console.log(data.emp);
        	$("#e_id").val(data.emp.e_id);
        	$("#e_name").val(data.emp.e_name);
        	$("#sex").val(data.emp.sex);
        	$("#addr").val(data.emp.addr);
        }
    });
}

function fn_add(){
	$.ajax({
        url: 'emp_insert',
        type: 'POST',
        data: {
        	'e_name':$("#e_name").val(),
        	'sex':$("#sex").val(),
        	'addr':$("#addr").val()
        },
        datatype: 'json', 
        success: function(data){ 
        	console.log(data.cnt);
        	if(data.cnt == 1){
        		alert("정상적으로 추가되었습니다.");
        		fn_list();
        		$("#e_id").val(""),
        		$("#e_name").val(""),
        		$("#sex").val(""),
        		$("#addr").val("")
        	} else {
        		alert("추가도중 문제가 발생했습니다.");
        	}
        }
    });
}

function fn_mod(){
	$.ajax({
        url: 'emp_update',
        type: 'POST',
        data: {
        	'e_id':$("#e_id").val(),
        	'e_name':$("#e_name").val(),
        	'sex':$("#sex").val(),
        	'addr':$("#addr").val()
        },
        datatype: 'json', 
        success: function(data){ 
        	console.log(data.cnt);
        	if(data.cnt == 1){
        		alert("정상적으로 수정되었습니다.");
        		fn_list();
        		$("#e_id").val(""),
        		$("#e_name").val(""),
        		$("#sex").val(""),
        		$("#addr").val("")
        	} else {
        		alert("수정도중 문제가 발생했습니다.");
        	}
        }
    });
}

function fn_del(){
	// 다시한번 되묻기
	var flag = confirm("한 번 지워진 데이터는 복구불가합니다. 그래도 지우시겠습니까?");
	if(!flag){
		return;
	}
	
	$.ajax({
        url: 'emp_delete',
        type: 'POST',
        data: {
        	'e_id':$("#e_id").val()
        },
        datatype: 'json', 
        success: function(data){ 
        	console.log(data.cnt);
        	if(data.cnt == 1){
        		alert("정상적으로 삭제되었습니다.");
        		fn_list();
        		$("#e_id").val(""),
        		$("#e_name").val(""),
        		$("#sex").val(""),
        		$("#addr").val("")
        	} else {
        		alert("삭제도중 문제가 발생했습니다.");
        	}
        }
    });
}

$(document).ready(function(){
	fn_list();
});
</script>
</head>
<body>
<table border="1px">
<thead>
	<tr>
		<td>사번</td>
		<td>이름</td>
		<td>성별</td>
		<td>주소</td>
	</tr>
</thead>
<tbody id="mydisp">
	<tr>
		<td colspan="4">데이터가 없습니다.</td>
	</tr>
</tbody>
</table>

<table border="1px">
	<tr>
		<td>사번</td>
		<td>
			<input type="text" id="e_id" />
		</td>
	</tr>
	<tr>
		<td>이름</td>
		<td>
			<input type="text" id="e_name" />
		</td>
	</tr>
	<tr>
		<td>성별</td>
		<td>
			<input type="text" id="sex" />
		</td>
	</tr>
	<tr>
		<td>주소</td>
		<td>
			<input type="text" id="addr" />
		</td>
	</tr>
	<tr>
		<td colspan="2">
			<input type="button" value="추가" onclick="fn_add()"/>
			<input type="button" value="수정" onclick="fn_mod()"/>
			<input type="button" value="삭제" onclick="fn_del()"/>
		</td>
	</tr>
	
	
	
</table>


</body>
</html>