mySQL(MariaDB)에 있는 데이터로 CRUD 예제
DAO
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)HTML
emp_list.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>EMP LIST Page</h3>
<table border="1">
<tr>
<th>사번</th>
<th>이름</th>
<th>성별</th>
<th>주소지</th>
</tr>
{% for i in row %}
<tr>
<td><a href="http://localhost/detail?e_id={{i.e_id}}">{{i.e_id}}</a></td>
<td>{{i.e_name}}</td>
<td>{{i.gen}}</td>
<td>{{i.addr}}</td>
</tr>
{% endfor %}
</table>
<input type="button" value="추가" onclick="location.href='http://localhost/add'">
</body>
</html>emp_detail.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script>
window.onload = function(){
const btn = document.querySelector('#del');
btn.onclick = () => {
if (confirm("정말로 삭제하시겠습니까?")) {
// 삭제 동작 수행
var form = document.getElementById("myForm");
form.submit();
} else {
// 취소 동작 수행
window.history.back();
}
}
}
</script>
</head>
<body>
<h3>EMP DETAIL Page</h3>
<form action="delete" method="post" id="myForm">
<table border="1">
<tr>
<th>사번</th>
<td>{{data.e_id}}</td>
</tr>
<tr>
<th>이름</th>
<td>{{data.e_name}}<input hidden value="{{data.e_name}}" name="e_id"></td>
</tr>
<tr>
<th>성별</th>
<td>{{data.gen}}</td>
</tr>
<tr>
<th>주소지</th>
<td>{{data.addr}}</td>
</tr>
<tr>
<td colspan="2">
<input type="button" value="수정" onclick="location.href='http://localhost/mod?e_id={{data.e_id}}'">
<input type="button" value="삭제" id="del">
</td>
</tr>
</table>
</form>
</body>
</html>emp_mod.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script>
window.onload = function(){
const btn = document.querySelector('#save');
btn.onclick = () => {
if (confirm("정말로 수정하시겠습니까?")) {
// 삭제 동작 수행
var form = document.getElementById("myForm");
form.submit();
} else {
// 취소 동작 수행
window.history.back();
}
}
}
</script>
</head>
<body>
<h3>EMP Modify Page</h3>
<form action="http://localhost/modify" method="post" id="myForm">
<table border="1">
<tr>
<th>사번</th>
<td><input type="text" value="{{data.e_id}}" name="e_id" readonly="readonly"></td>
</tr>
<tr>
<th>이름</th>
<td><input type="text" value="{{data.e_name}}" name="e_name"></td>
</tr>
<tr>
<th>성별</th>
<td><input type="text" value="{{data.gen}}" name="gen"></td>
</tr>
<tr>
<th>주소지</th>
<td><input type="text" value="{{data.addr}}" name="addr"></td>
</tr>
</table>
<input value="저장" type="button" id="save">
</form>
</body>
</html>emp_mod_act.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>EMP MOD ACT 화면</h3>
{% if cnt > 0 : %}
수정이 완료되었습니다<br>
<input type="button" onclick="location.href='emp_list'" value="Main">
{% else %}
수정에 실패했습니다<br>
<input type="button" onclick="location.href='detail?e_id={{e_id}}'" value="돌아가기">
{%endif%}
</body>
</html>방법 2. (alert로 띄우고 바로 이동시킬때)
더보기
<!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 = "emp_list"
}else{
alert("수정도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
EMP MOD ACT 화면
</body>
</html>emp_add.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script>
window.onload = function(){
const btn = document.querySelector('#add');
btn.onclick = () => {
if (confirm("정말로 추가하시겠습니까?")) {
// 삭제 동작 수행
var form = document.getElementById("myForm");
form.submit();
} else {
// 취소 동작 수행
window.history.back();
}
}
}
</script>
</head>
<body>
<h3>EMP Modify Page</h3>
<form action="http://localhost/insert" method="post" id="myForm">
<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>
</table>
<input value="저장" type="button" id="add">
</form>
</body>
</html>emp_add_act.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>EMP ADD ACT 화면</h3>
{% if cnt > 0 : %}
추가가 완료되었습니다<br>
<input type="button" onclick="location.href='emp_list'" value="Main">
{% else %}
추가에 실패했습니다<br>
<input type="button" onclick="location.href='add'" value="돌아가기">
{%endif%}
</body>
</html>방법 2 . alert창 띄우고 이동
더보기
<!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 = "emp_list"
}else{
alert("추가도중 문제가 생겼습니다.");
history.back();
}
</script>
</head>
<body>
EMP ADD ACT 화면
</body>
</html>flask
from flask import Flask
from flask.globals import request
from flask.templating import render_template
from day09.daoemp import DaoEmp
app = Flask(__name__)
# localhost
@app.route('/')
@app.route('/emp_list')
def index():
dao = DaoEmp()
rows = dao.selectList()
return render_template("emp_list.html", row=rows)
@app.route('/add')
def add():
return render_template("emp_add.html")
@app.route('/insert', methods=['POST'])
def insert():
dao = DaoEmp()
e_id = request.form['e_id']
e_name = request.form['e_name']
gen = request.form['gen']
addr = request.form['addr']
cnt = dao.insert(e_id, e_name, gen, addr)
return render_template("emp_add_act.html", cnt=cnt)
@app.route('/detail')
def detail():
dao = DaoEmp()
e_id = request.args.get('e_id')
row = dao.selectOne(e_id)
return render_template("emp_detail.html", data=row)
@app.route('/mod')
def mod():
dao = DaoEmp()
e_id = request.args.get('e_id')
row = dao.selectOne(e_id)
return render_template("emp_mod.html", data=row)
@app.route('/modify', methods=['POST'])
def modify():
dao = DaoEmp()
e_id = request.form['e_id']
e_name = request.form['e_name']
gen = request.form['gen']
addr = request.form['addr']
cnt = dao.update(e_id, e_name, gen, addr)
return render_template("emp_mod_act.html", cnt=cnt, e_id=e_id)
@app.route('/delete', methods=['POST'])
def delete():
dao = DaoEmp()
e_id = request.form['e_id']
cnt = dao.delete(e_id)
rows = dao.selectList()
return render_template("emp_list.html", row=rows)
if __name__ == '__main__':
# app.run(debug=True)
app.run(host="0.0.0.0", port=80, debug=True)'Python' 카테고리의 다른 글
| [Python] AJAX / fetch / axios (0) | 2024.07.09 |
|---|---|
| [Python] MVVM - js예제 / jquery예제 (0) | 2024.07.08 |
| [Python] Flask (0) | 2024.07.03 |
| [Python] pymongo (0) | 2024.07.03 |
| [Python] pymysql 설치 및 실행(select, insert) (0) | 2024.07.02 |
