Python

[Python] 16장 python의 flask MVC 패턴

반응형

 

DAO를 이용한 출력

 

DB에서 값을 가져와서 출력

 

 

 

  • 전체 출력

daoemp.py

=> dao 역할을 함

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)
    
    def selectList(self):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
        """
        self.cur.execute(sql)
        list = self.cur.fetchall()
        return list

    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    list = de.selectList()
    print("list",list)

 

결과 화면1

 

 

 

  • 한 줄 출력
import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)
    
    def selectList(self):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
        """
        self.cur.execute(sql)
        list = self.cur.fetchall()
        return list
    
    def select(self,e_id):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
            WHERE
                e_id = '{e_id}'
        """
        self.cur.execute(sql)
        list = self.cur.fetchall()
        # return list
        return list[0] #vo 방식으로 리턴해주기에 권장함


    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    vo = de.select('1')
    print("vo",vo)

 

결과 화면2 : return list로 사용할때

 

결과 화면3 :  return list[0]으로 사용할때

 

 

+ fetchone() 으로 사용할때 (한 줄만 출력할 때 권장!!)

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)
    
    def select(self,e_id):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
            WHERE
                e_id = '{e_id}'
        """
        self.cur.execute(sql)
        list = self.cur.fetchone()
        return list


    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    vo = de.select('1')
    print("vo",vo)

 

결과 화면4

 

 

 

DAO를 이용한 삽입

 

 

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)
    
    def insert(self,e_id,e_name,gen,addr):
        sql = f"""
            INSERT INTO 
                emp (e_id, e_name, gen, addr)
            VALUES ('{e_id}','{e_name}','{gen}','{addr}')
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt

    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    cnt = de.insert('3','3','3','3')
    print("cnt",cnt)

 

결과 화면5

 

변경된 것 확인

 

 

 

DAO를 이용한 업데이트

 

 

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)

    def update(self,e_id,e_name,gen,addr):
        sql = f"""
            UPDATE emp 
            SET
                e_name = '{e_name}',
                gen = '{gen}',
                addr = '{addr}'
            WHERE
                e_id = '{e_id}'
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt

    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    cnt = de.update('3','6','6','6')
    print("cnt",cnt)

 

결과 화면6

 

변경된 것 확인

 

 

 

DAO를 이용한 삭제

 

 

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)

    def delete(self,e_id):
        sql = f"""
            DELETE FROM emp 
            WHERE
                e_id = '{e_id}'
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt

    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    cnt = de.delete('3')
    print("cnt",cnt)

 

결과 화면7

 

변경된 것 확인

 

 

 

응용

 

 

daoemp.py

import pymysql

class Daoemp:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3305, user='root',
                              password='python', db='python', charset='utf8')

        self.cur = self.con.cursor(pymysql.cursors.DictCursor)
    
    def selectList(self):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
        """
        self.cur.execute(sql)
        list = self.cur.fetchall()
        return list
    
    def select(self,e_id):
        sql = f"""
            SELECT
                e_id,
                e_name,
                gen,
                addr
            FROM
                emp
            WHERE
                e_id = '{e_id}'
        """
        self.cur.execute(sql)
        # list = self.cur.fetchall()
        list = self.cur.fetchone()
        return list
        # return list[0] #vo 방식으로 리턴해주기에 권장함

    def insert(self,e_id,e_name,gen,addr):
        sql = f"""
            INSERT INTO 
                emp (e_id, e_name, gen, addr)
            VALUES ('{e_id}','{e_name}','{gen}','{addr}')
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt
        
    def update(self,e_id,e_name,gen,addr):
        sql = f"""
            UPDATE emp 
            SET
                e_name = '{e_name}',
                gen = '{gen}',
                addr = '{addr}'
            WHERE
                e_id = '{e_id}'
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt
        
    def delete(self,e_id):
        sql = f"""
            DELETE FROM emp 
            WHERE
                e_id = '{e_id}'
        """
        cnt = self.cur.execute(sql)
        self.con.commit() #꼭 해야함
        return cnt

    def __del__(self):
        self.cur.close()
        self.con.close()
        
if __name__ == '__main__':
    de = Daoemp()
    cnt = de.delete('3')
    print("cnt",cnt)

 

 

my_flask.py

from flask import Flask,request,render_template
from day08.daoemp import Daoemp

app = Flask(__name__)

@app.route('/')
@app.route('/emp_list')
def emp_list():
    de = Daoemp()
    list = de.selectList()
    return render_template("emp_list.html", list=list)

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

 

 

emp_list.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
table {
  margin: 10px 10px 10px 0px;
  width: 30%;
}
</style>
<script type="text/javascript">
function fn_add() {
	location.href = "emp_add";
}
</script>
</head>
<body>
	EMPLIST HTML <br>
	
	<table border=1>
		<tr>
			<th> 사번 </th>
			<th> 이름 </th>
			<th> 성별 </th>
			<th> 주소 </th>
		</tr>
		{% for e in list %}
		<tr>
			<td><a href="emp_detail?e_id={{ e.e_id }}"> {{ e['e_id'] }} </a></td>
			<td> {{ e.e_name }} </td>
			<td> {{ e.gen }} </td>
			<td> {{ e.addr }} </td>
		</tr>
		{% endfor %}
	</table>
	
	<input type="button" value="추가" onclick="fn_add()">
</body>
</html>

 

 

emp_detail.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
table {
  margin: 10px 10px 10px 0px;
}
td {
  width: 50%;
}
</style>
<script type="text/javascript">
function fn_mod() {
	location.href = "emp_mod?e_id={{ vo.e_id }}";
}

function fn_del() {
	var flag = confirm("한 번 지워진 데이터는 복구불가합니다.\n 그래도 삭제하시겠습니까?");
	if(!flag) {
		return;
	}
	document.frm.submit();
}
</script>
</head>
<body>
	EMP DETAIL <br>
	
	<form name="frm" action="emp_del" method="post">
		<input type="hidden" name="e_id" value="{{ vo.e_id }}">
		<table border=1>
			<tr>
				<th> 사번 </th>
				<td> {{ vo.e_id }} </td>
			</tr>
			<tr>
				<th> 이름 </th>
				<td> {{ vo.e_name }} </td>
			</tr>
			<tr>
				<th> 성별 </th>
				<td> {{ vo.gen }} </td>
			</tr>
			<tr>
				<th> 주소 </th>
				<td> {{ vo.addr }} </td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="button" onclick="fn_mod()" value="수정">
					<input type="button" onclick="fn_del()" value="삭제">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

 

emp_mod.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
table {
  margin: 10px 10px 10px 0px;
}
</style>
<script type="text/javascript">
function fn_mod_act() {
	document.frm.submit();
}
</script>
</head>
<body>
	EMP MOD<br>
	<form name="frm" action="emp_mod_act" method="post">
		<table border=1>
			<tr>
				<th> 사번 </th>
				<td> <input type="text" value="{{ vo.e_id }}" name="e_id"> </td>
			</tr>
			<tr>
				<th> 이름 </th>
				<td> <input type="text" value="{{ vo.e_name }}" name="e_name"> </td>
			</tr>
			<tr>
				<th> 성별 </th>
				<td> <input type="text" value="{{ vo.gen }}" name="gen"> </td>
			</tr>
			<tr>
				<th> 주소 </th>
				<td> <input type="text" value="{{ vo.addr }}" name="addr"> </td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="button" onclick="fn_mod_act()" value="저장">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

 

emp_mod_act.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<script type="text/javascript">
	var cnt = {{cnt}}; /* 에러처럼 보이지만 에러가 아님 */
	if(cnt > 0) {
		alert("정상적으로 수정되었습니다.");
		location.href = "emp_list";
	} else {
		/* location.href 를 사용하면 안 됨 */
		alert("수정도중 문제가 생겼습니다.");
		history.back();
	}
	
</script>
<body>
EMP MOD ACT HTML <br>

{{ cnt }}
</body>
</html>

 

 

emp_add.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function fn_add_act() {
	document.frm.submit();
}
</script>
</head>
<body>
	EMP ADD HTML <br>

	<form name="frm" action="emp_add_act" method="post">
		<table border=1>
			<tr>
				<th> 사번 </th>
				<td> <input type="text" name="e_id"> </td>
			</tr>
			<tr>
				<th> 이름 </th>
				<td> <input type="text" name="e_name"> </td>
			</tr>
			<tr>
				<th> 성별 </th>
				<td> <input type="text" name="gen"> </td>
			</tr>
			<tr>
				<th> 주소 </th>
				<td> <input type="text" name="addr"> </td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="button" onclick="fn_add_act()" value="저장">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

 

emp_add_act.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
	var cnt = {{cnt}};
	if(cnt > 0) {
		alert("정상적으로 저장되었습니다.")
		location.href = "emp_list";
	} else {
		alert("저장도중 문제가 생겼습니다.");
		history.back();
	}
</script>
</head>
<body>
	EMP ADD ACT <br>
	{{cnt}}
</body>
</html>

 

 

emp_list.html 화면

 

emp_detail.html 화면

 

emp_mod.html 화면

 

emp_mod_act.html 화면 : 정상 수정

 

emp_mod_act.html 화면 : 수정 오류

 

emp_add.html 화면

 

emp_add_act.html 화면

 

emp_del.html 화면1

 

emp_del.html 화면2

 

 

반응형

'Python' 카테고리의 다른 글

[Python] 18장 MVVM 패턴 개념  (0) 2024.04.05
[Python] 17장 과제  (0) 2024.04.03
[Python] 15장 python의 flask  (0) 2024.04.03
[Python] 14장 flask 설치 및 설정  (0) 2024.04.03
[Python] 13장 파이썬으로 하는 MySQL 데이터 삽입, 수정, 삭제  (0) 2024.04.02

티스토리툴바