파이썬에서 오라클 데이터베이스 연결 후 쿼리 사용하기

 

파이썬 주피터 노트북(jupyter notebook)에서 오라클 데이터베이스 연결 후 정의어(DDL), 질의어(DML) 등을 실행하는 코드입니다. 

 

테이블 생성과 데이터를 입력하고 fetchall()을 통해 조회된 데이터를 확인하고, 데이터 업데이트와 삭제를 하는 예시 코드입니다. 

 

파이썬에서 오라클 데이터베이스 연결

파이썬을 사용하여 오라클 데이터베이스에 연결하기 위한 라이브러리(cx_Oracle) 설치

 

 

명령어 : pip insall cx_Oracle
* anaconda 환경에서는 conda install cx_Oracle

(base) C:\User\admin>pip install cx_oracle

(base) C:\User\admin>conda install cx_oracle

 

주피터 노트북에서 설치

 

 

설치가 완료된 후 아래와 같은 명령들로 데이터베이스 테이블에 연결할 수 있음

 

라이브러리 임포트 명령 import cx_Oracle as oci 실행이 정상적으로 되면 설치가 완료된 것임

 

데이터베이스 연결 명령과 닫는 명령은 아래와 같이 함

dsn = oci.makedsn('localhost', '1521', 'xe')
conn = oci.connect('scott', 'tiger', dsn)
conn.close()

• 로컬서버(컴퓨터)가 아닌 경우, 'localhost' 대신에 해당 서버의 ip를 입력함
• 'scott', 'tiger'은 계정(db)과 비번임
• xe(Express Edition) : db를 식별할 수있는 이름
• dsn : Data Source Name
• db를 열어 작업을 한 후 반드시 연결 상태를 종료(conn.close())하는 습관이 필요함

 

위에서 데이터베이스 연결 명령을 한줄로 하면, 다음 코드처럼 할 수 있음

conn = oci.connect('scott/tiger@localhost:1521/xe')

 

 

 

 

cursor, execute, fetch 함수

▶cursor

커서 : SQL 문법의 결과를 가리키는 포인트 (마우스 커서(포인터)를 생각하면 이해하기 쉬움)

커서는 데이터베이스 SQL문(쿼리, 질의) 실행 후 나오는 결과(반환된 데이터)를 가리키는 가상의 포인터(작업 도구)라고 할 수 있음.

SQL 쿼리의 실행 결과는 하나 이상의 행과 열을 가진 테이블 형태(집합)인데, 커서는 이 결과 집합을 가리키면서 여러 행 중에서 현재 처리 중인 행을 가리킴.

커서를 사용하면,
데이터베이스에서 필요한 정보를 검색(특정 조건을 충족하는 행 검색)하거나 조작하는 데 유용하고,
하나의 행에서 다음 행으로 이동하면서 반복문을 실행하는 데 사용될 수 있음.(각 행에 접근)

 

▶execute

커서에 SQL 문법(질의)을 적용시키는 함수이며, 인자로 받은 SQL문(질의)을 문자열로 입력함

데이터베이스 시스템에서 SQL 쿼리를 실행하고 그 결과를 얻기 위해 사용하는 함수(또는 메서드)

execute()는 SQL 문장이나 쿼리를 실행하는데 사용되는 것으로, DB 연결 후 SQL 명령을 전달하고 실행하는 과정이 필요한데, 이때 execute()가 사용됨.

즉, execute()는 데이터베이스에서 SQL 문장을 실행하고 그 결과를 얻기 위해 사용되는 중요한 함수임.

 

▶fetch

커서에 저장된 결과에서 값(레코드, 컬럼, 데이터 값 등)을 가져올 때 사용하며, 

그 종류는 fetchall, fetchmany, fetchone 등이 있으며 몇 개의 값를 가져올지에 따라서 해당 함수를 사용함

 

fetchall, fetchmany, fetchone

• fetchall : 커서 결과에서 모든 데이터를 가져옴
• fetchmany : 지정된 숫자만큼 데이터를 가져오며, 커서에 데이터가 남아 있기 때문에 다시 사용하면 데이터를 이어서 가져옴 
• fetchone : 단 하나의 데이터만 가져오며, fetchmany와 마찬가지로 커서에 데이터가 남아 있기 때문에 다시 사용하면 데이터를 이어서 가져옴 

data = cursor.fetchall()
print(data)

data = cursor.fetchmany(2)
print(data)

data = cursor.fetchone()
print(data)

 

 

테이블 생성, 삭제

sql = "create table scott.publisher (publisher_id number(5) primary key, publisher_name varchar2(50))"
cursor.execute(sql)
conn.commit()

cursor.execute("drop table scott.publisher")
conn.commit()

 

 

테이블 생성 시 외래키(Foreign) 사용 예시

sql = "create table scott.book_store (bscode number(5) primary key, name varchar2(100), tel varchar2(12), \
        country varchar2(20), city varchar2(20))"
cursor.execute(sql)
conn.commit()

sql = "create table scott.book_list (bcode number(5) primary key, title varchar2(200), author varchar2(20),\
        year_of_publication varchar2(30), publisher number(5), price number(10), \
        constraint fk_publisher foreign key(publisher) references scott.book_store(bscode))"
cursor.execute(sql)
conn.commit()

• book_store 테이블은 bscode, book_list 테이블은 bcode 컬럼이 pk(primary key)이고,
• book_list 테이블의 publisher 컬럼은 book_store 테이블의 bscode를 대상으로 외래키(froeign) 설정 (constraint ~ foreign key ~ references ~)

 

select, insert, update, delete 연습

Oracle SQL Developer 툴에서 emp 테이블로 데이터 조작(DML) 연습을 하는데,

 

 

오라클 DB와 연결된 파이썬에서도 emp 테이블을 활용하여 select, insert, update, delete 쿼리를 전달하고 나오는 결과를 fetch를 사용해서 확인해보는 연습을 할 수 있음 

# dsn = oci.makedsn('localhost', '1521', 'xe')
# conn = oci.connect('scott', 'tiger', dsn)
conn =  oci.connect('scott/tiger@localhost:1521/xe')  # 위 2줄 대신에 1줄로 처리 시
cursor = conn.cursor()
sql = "select * from emp"
cursor.execute(sql)
rs = cursor.fetchall()
print(rs)

# 'empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm', 'deptno'
cursor.execute("insert into emp (empno, ename, job) values(18, 'choi', 'sales')")
conn.commit()

sql = '''insert into emp values(:1, :2, :3, :4, :5, :6, :7, :8)'''
cursor.execute(sql, [4, 'park', None, None, None, None, None, None])
conn.commit()

sql = "insert into emp values(:1, :2, :3, :4, :5, :6, :7, :8)"
cursor.execute(sql, [20, 'park', 'sales', 7839, '22/01/03', 3200, 600, 10])
conn.commit()

sql = "insert into emp (empno, ename) values(:1, :2)"
cursor.execute(sql, [17, 'kim'])
conn.commit()

sql = "update emp set ename = 'sbpark' where ename = 'park'"
cursor.execute(sql)
conn.commit()

sql = "update emp set job = :1 where empno = 4"
cursor.execute(sql, ['salesman'])
conn.commit()

sql = "delete from emp where empno = 4"
cursor.execute(sql)
conn.commit()

cursor.execute("select * from emp")
rs = cursor.fetchall()
for row in rs:
    print(row)

• :1, :2, :3, ~ 부분은 쿼리가 실행될 때 실제 값으로 대체될 컬럼의 위치를 나타내는 바인드 변수(bind variable)라고 함