[Python] flask mariadb crud 쿼리

day09/myflask.py

from flask.app import Flask, request
from flask.templating import render_template
import pymysql

app = Flask(__name__)

@app.route('/emp')
def main():

    #메인코드
    con = pymysql.connect(host='127.0.0.1', port=3300, user='root', password='python',
                           db='python', charset='utf8') #접속 정보
    cur = con.cursor(pymysql.cursors.DictCursor) #커서 생성
    
    sql = "select * from emp"
    cur.execute(sql)
    
    #cur.fetchone() -> 하나만 가져옴
    list = cur.fetchall()
    print(list)
    
    cur.close()
    con.close()
    
    return render_template('emp_list.html', list=list)

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

day09/daoemp.py

import pymysql

class DaoEmp:
    
    #자주 사용하는 것들을 전역 변수로
    def __init__(self):
        self.con = pymysql.connect(host='127.0.0.1', port=3300, user='root', password='python',
                       db='python', charset='utf8') #접속 정보
        
        self.cur = self.con.cursor(pymysql.cursors.DictCursor) #커서 생성    
        
    def selectList(self):
        sql = "select * from emp"
        self.cur.execute(sql)

        list = self.cur.fetchall()
        return list    
    
    def selectOne(self, e_id):
        sql = f"""
        select
        e_id, e_name, gen, addr
        from emp
        where e_id = '{e_id}' 
        
        """
        print(sql)
        self.cur.execute(sql)
        
        #list = self.cur.fetchall()
        # return list[0]
        vo = self.cur.fetchone()
        return 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}')
        """
        print(sql)
        
        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}'
        """
        print(sql)
        
        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}'
        """
        print(sql)
        
        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("6", "6", "6", "6")
    #cnt = de.update("6", "7", "7", "7")
    cnt = de.delete("6", "7", "7", "7")
    print("cnt",cnt)

day09/myflask.py :: dao 직접 부르기

from flask.app import Flask, request
from flask.templating import render_template
import pymysql

from day09.daoemp import DaoEmp

app = Flask(__name__)

@app.route('/')
def main():
    
    # dao에서 이미 다 만들어 뒀기 때문에 필요없음!
    # #메인코드
    # con = pymysql.connect(host='127.0.0.1', port=3300, user='root', password='python',
    #                        db='python', charset='utf8') #접속 정보

    de = DaoEmp()
    list = de.selectList()
    return render_template('emp_list.html', list=list)

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