OracleDB연동 CRUD 구현💢

OracleDB 연동을 이용한 CRUD 구현

---

➰주요 API

• connect() - db접속
• cursor() - 접속된 db객체인connection 으로부터 모든 sql 문장 실행 및 결과를 보유하게 되는 Cursor객체 생성
• execute() - DML/ DDL/ TCL 즉 모든 sql문장 실행
• fetchone() - select한 결과가 하나의 row인 경우 데이터 획득
• fetchall() - select 한 결과가 다수일대 반환 단 반복문으로 하나씩 row 반환
• close() - Cursor와 Connection 순으로 둘다 반환 필수
• commit() - DML 문장 실행 직후 필수
• rollback() - 의 문장 실행 후 복원

✅table생성

기존에 있던 dept 테이블을 손상시키지 않기위해 dept01이라는 테이블을 생성하는 과정이다. dept01이 있다면 삭제하는 과정을 거치고(예외상황 처리)
execute()를 통해 sql을 이용하여 테이블을 생성해주고 키값을 설정해준다.
그리고 마지막으로 잊어먹지 말아야 하는게 있는데 바로 자원 반환을 하는 close()함수이다. 모든 과정들이 거쳐지고 나서 cur.close()와 conn.close()는 잊으면 안된다는 것을 명심하자!

def dept01_create():
    conn = cx_Oracle.connect(user="SCOTT", password="TIGER", dsn="xe")
    cur = conn.cursor()

    try:    
        try:
            #dept01 table 삭제
            cur.execute('drop table dept01')
        except Exception as e:
            print(e)

        #제약조건 제외한 복제
        cur.execute('create table dept01 as select * from dept')
        # dept02 테이블에 제약조건 추가
        cur.execute('alter table dept01 add constraint dept01_pk_deptno primary key(deptno)')
        print("생성")

    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()



    cur.execute('create table dept02 as select * from dept')
    print("무조건 정상실행")

✅특정 부서 번호로 하나의 row만 검색

여기서는 함수의 파라미터로 들어온 deptno를 쿼리문에 어떻게 대입하는지가 핵심이다

아래의 코드를 통하여 어떤식으로 처리하는지 확인해보자~

def dept01_query(deptno):
    try:
        conn = cx_Oracle.connect(user="SCOTT", password="TIGER", dsn="xe")
        cur = conn.cursor()


        # 존재할 경우 하나의 row값만 보유
        cur.execute('select * from dept01 where deptno=: v', v=deptno)
        print(cur.fetchone()) # (10, 'ACCOUNTING', 'NEW YORK')
        print(cur.fetchone()) # None
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

쿼리문이 들어간 부분을 보면 deptno=: v라는 구문을 사용하였다. 이 구문을 통해 파라미터로 넘어 들어오면 deptno를 담아줄 수 있는 v를 만들어 넣어준 것이다.

✅특정이름이 포함된 부서 검색 출력(select ~ like %s%)

여기서는 %를 이용한 like함수를 사용해야 할 것이라는 감이 올 것이다. 그러면 파라미터 처리를 어떤 식으로 처리하는지 확인해보자~

def dept01_all(dname):
    try:
        conn = cx_Oracle.connect(user="SCOTT", password="TIGER", dsn="xe")
        cur = conn.cursor()

        # 여러개의 부서정보를 보유 따라서 데이터 row수 만큼 tuple을 보유한 list
        cur.execute('select * from dept01 where dname like :v', v='%' + dname+'%')
        rows = cur.fetchall() # [(20, 'RESEARCH', 'DALLAS')] fetall이기때문에 여러개 값을 뽑는 리스트로 출력
        print(rows)

        for row in rows:
            print(row)  #(20, 'RESEARCH', 'DALLAS') 개별로도 뽑을 수 있음

    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

sql에서 적용되는 쿼리문이 형성될 수 있도록 v라는 변수 자체에 % + dname+ %를 넣어주면 조건에 만족하는 모든 행들이 출력된다. 여기서는 like함수를 사용했기 때문웨 여러개의 값들이 나오므로 fetchall() 함수를 사용한 걸 볼 수 있다.

✅데이터 저장(Insert)

지금까지는 하나의 파라미터만 받아와 처리했지만 데이터를 저장할때는 여러개의 데이터 값을 받을 수 밖에 없다. 이럴때 어떻게 처리하는지 확인해보자.

def dept01_insert(ndeptno, ndname, nloc):
    try:    
        conn = cx_Oracle.connect(user="SCOTT", password="TIGER", dsn="xe")
        cur = conn.cursor()

        try:
            cur.execute('insert into dept01 values (:v1, :v2, :v3)', v1=ndeptno, v2=ndname, v3=nloc)
            conn.commit()
        except :
            print('저장시 예외 발생')

    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

위에서 확인할 수 있듯이 쿼리문 안에 (:v1, :v2, :v3)형태로 지정해준다. 그리고 , 뒤에 받아온 파라미터값들을 쿼리문 처리해주는 v1, v2, v3,에 넣어주어 처리하면 된다.

위에 함수들을 이용해 처리하고 싶은 것들을 if name == 'main': 안에서 필요한 함수들을 구현해 볼 수 있다.

if __name__ == '__main__':
    # dept01_create()
    # dept01_query(10)
    # dept01_query(20)
    # dept01_all('SALES')
    # dept01_all('RESEARCH')
    # dept01_insert(50, '교육부', '남부')
    dept01_all('SAL')


>>>    
생성
무조건 정상실행
(10, 'ACCOUNTING', 'NEW YORK')
None
(20, 'RESEARCH', 'DALLAS')
None
[(30, 'SALES', 'CHICAGO')]
(30, 'SALES', 'CHICAGO')
[(20, 'RESEARCH', 'DALLAS')]
(20, 'RESEARCH', 'DALLAS')  
[(30, 'SALES', 'CHICAGO')]
(30, 'SALES', 'CHICAGO')
>>>