Python 19 - DAO 만들기

EMP 테이블을 이용해 (MS SQL 이용)

 

DAO 만들기 

 

<mssql_select2>

import pymssql
 
conn = pymssql.connect(server="", user="sa", password="", database="python", charset='utf8')
cursor = conn.cursor()
 
cursor.execute('SELECT * FROM emp;')
 

list = cursor.fetchall()
print(list)

cursor.close()
conn.close()

 

---

<daoemp.py>

SELECT (전체출력)

import pymssql

class DaoEmp:
    def __init__(self):
        self.conn = pymssql.connect(server="이름", user="sa", password="비밀번호", database="python", charset='utf8')
        self.cursor = self.conn.cursor()
        
    def selects(self):
        self. cursor.execute('SELECT * FROM emp;')
        list = self.cursor.fetchall() 
        return list
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    list = de.selects()
    print(list)

많이 쓰는 건 전역변수로 만들어준다

전역변수로 만드려면 self

 

배열형태 출력

import pymssql

class DaoEmp:
    def __init__(self):
        self.conn = pymssql.connect(server="DESKTOP-N1VNJGL", user="sa", password="python", database="python", charset='utf8')
        self.cursor = self.conn.cursor()
        
    def selects(self):
        self. cursor.execute('SELECT * FROM emp;')
        list = self.cursor.fetchall()
        mdict = []
        for i in list:
            mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
         
        return mdict
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    list = de.selects()
    print(list)

결과

 

 

 

return None

• return을 생략
• return 만 작성해도
• return None이 자동으로 된다.

    def select(self,e_id):
        return None

---

SELECT(한 행)

JSON 형태로 출력

import pymssql

class DaoEmp:
    def __init__(self):
        self.conn = pymssql.connect(server="DESKTOP-N1VNJGL", user="sa", password="python", database="python", charset='utf8')
        self.cursor = self.conn.cursor()
        
    def selects(self):
        self. cursor.execute('SELECT * FROM emp;')
        list = self.cursor.fetchall()
        mdict = []
        for i in list:
            mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
         
        return mdict
    
    def select(self,e_id):
        self. cursor.execute('SELECT * FROM emp where e_id='+ e_id)
        list = self.cursor.fetchall()
        mdict = []
        for i in list:
            mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
        
        return mdict[0]
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    emp = de.select("3")
    print(emp)

위에 코드를 뜯어보자면 

 

파라미터로 e_id 값을 받는 메소드를 만들어서

해당 조건에 해당하는 행을 조회

  def select(self,e_id):
        self. cursor.execute('SELECT * FROM emp where e_id='+ e_id)
        list = self.cursor.fetchall()
        mdict = []
        for i in list:
            mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
        
        return mdict[0]

한 행만 출력해야하므로 return mdict[0] 을 반환한다.

 

main 에서 파라미터에 e_id 값을 주면

해당 e_id 에 해당하는 데이터가 출력된다.

import pymssql

class DaoEmp:
    def __init__(self):
        self.conn = pymssql.connect(server="DESKTOP-N1VNJGL", user="sa", password="python", database="python", charset='utf8')
        self.cursor = self.conn.cursor()
        
    def selects(self):
        self. cursor.execute('SELECT * FROM emp;')
        list = self.cursor.fetchall()
        mdict = []
        for i in list:
            mdict.append({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
         
        return mdict
    
    def select(self,e_id):
        sql = f"""
            SELECT * FROM emp
            where 
                e_id= {e_id}
        """
        self. cursor.execute(sql)
        i = self.cursor.fetchall()[0]
        emp = ({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
        return emp
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    emp = de.select("3")
    print(emp)

 

두번째 방법

    def select(self,e_id):
        sql = f"""
            SELECT * FROM emp
            where 
                e_id= {e_id}
        """
        self. cursor.execute(sql)
        i = self.cursor.fetchall()[0]
        emp = ({"e_id":i[0],"e_name":i[1],"sex":i[2],"addr":i[3]})
        return emp

 

---

 

INSERT

첫번째 방법(내가 푼 것)

    def insert(self,e_id,e_name,sex,addr):
        sql =f"""
            INSERT INTO emp
            VALUES({e_id},{e_name},{sex},{addr})
        """
        self.cursor.execute(sql)
        self.conn.commit()
        cnt = self.cursor.rowcount
        return cnt
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    # list = de.selects()
    # emp = de.select("3")
    cnt = de.insert("7","7","7","7")
    print(cnt)

 

UPDATE

 def update(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}
                
            """
        self.cursor.execute(sql)
        self.conn.commit()
        cnt = self.cursor.rowcount
        return cnt
    
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    # list = de.selects()
    # emp = de.select("3")
    # cnt = de.insert("7","7","7","7")
    cnt = de.update("7","8","8","8")
    print(cnt)

 

DELETE

    def delete(self,e_id):
        sql = f"""
            DELETE FROM EMP
            WHERE 
                e_id={e_id}
        """
        self.cursor.execute(sql)
        self.conn.commit()
        cnt = self.cursor.rowcount
        return cnt
        
    def __del__(self):
        self.cursor.close()
        self.conn.close() 
        
if __name__ == '__main__':
    de = DaoEmp()
    # list = de.selects()
    # emp = de.select("3")
    # cnt = de.insert("7","7","7","7")
    # cnt = de.update("7","8","8","8")
    cnt = de.delete("7")
    print(cnt)