Python(파이썬)과 Oracle(오라클) 연동

Python(파이썬)과 Oracle(오라클) 연동-CRUD

 

1. oracle(오라클)에서 테이블 생성

CREATE TABLE SAMPLE(
		    COL01 VARCHAR2(20),
                    COL02 VARCHAR2(20),
                    COL03 VARCHAR2(20)
                    
    CONSTRAINT SAMPLE_PK PRIMARY KEY (COL01)
);
                    

 

2. select

import cx_Oracle

conn = cx_Oracle.connect('사용자이름/비밀번호@localhost:1521/xe')
cs = conn.cursor()	--cursor => 쿼리문에 의해 반환되는 결과값을 저장하는 메모리 공간
rs = cs.execute("select col01,col02,col03 from sample")

for record in rs:
    print(record[0])
    
cs.close()	
conn.close()

 

3. insert

import cx_Oracle

conn = cx_Oracle.connect('사용자이름/비밀번호@localhost:1521/xe')
cs = conn.cursor()
sql = "insert into sample (col01,col02,col03) values (:1,:2,:3)"
cs.execute(sql,('3','3','3'))	--execute => sql문 실행
print(cs.rowcount)

cs.close()
conn.commit()
conn.close()

 

4. update

import cx_Oracle

conn = cx_Oracle.connect('사용자이름/비밀번호@localhost:1521/xe')
cs = conn.cursor()
sql = "update sample set col02 = :1 , col03 = :2 WHERE col01=:3"
cs.execute(sql,('3','3','1'))

# cs.rowcount : java에서의 cnt
print(cs.rowcount)

cs.close()
conn.commit()
conn.close()

 

5. delete

import cx_Oracle

conn = cx_Oracle.connect('사용자이름/비밀번호@localhost:1521/xe')
cs = conn.cursor()
sql = "delete from sample where col01=:1"
cs.execute(sql,('1'))
print(cs.rowcount)

cs.close()
conn.commit()
conn.close()