Python 과 Oracle 연동 #04 cx_Oracle : Bind Variable 사용하기

cx_Oracle.Cursor.execute( ) 함수에서 bind variable을 사용하는 방법에 대해 알아보겠습니다.
역시 Python 답게 아주 쉽습니다.
parameters 로 Dictionary를 전달할 경우에는 bind variable 이름과 값을 전달해 주면 되며,
parameters 로 List를 전달할 경우에는 bind variable의 순서대로 값을 전달해 주면 됩니다.

아래에 설명할 예제에 앞서 먼저 DB에 연결을 합니다.

import cx_Oracle

conn = cx_Oracle.connect('sys/orange@localhost:1521/orcl', mode = cx_Oracle.SYSDBA)

cursor = conn.cursor()

1. List를 이용한 bind variable 구현

>>> cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , [ 2000, 20 ]) <cx_Oracle.Cursor on <cx_Oracle.Connection to sys@localhost:1521/orcl>> >>> print(cursor.fetchall()) [(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20), (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20), (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)]

2. Dictionary를 이용한 bind variable 구현

>>> cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , { 'SAL' : 2000 , 'DEPTNO' : 20 }) <cx_Oracle.Cursor on <cx_Oracle.Connection to sys@localhost:1521/orcl>> >>> print(cursor.fetchall()) [(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20), (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20), (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)]

3. Bulk insert

cx_Oracle.Cursor.executemany( ) 함수를 이용하여 bulk insert 구현이 가능합니다.
하나의 statement에 bind variable을 List에 담아서 전달하면 됩니다.
각각의 row는 Tuple로 만들어서 List에 담습니다.

먼저 Test로 사용할 Table을 생성합니다.

CREATE_SCRIPT = """
CREATE TABLE SCOTT.TESTPY (
    ID    INT,
    NAME  VARCHAR(32)
)
"""
cursor.execute(CREATE_SCRIPT)

Bind varialbe을 List로 생성합니다.

ARRAY = []
for i in range(0,10): ARRAY.append((i, 'TEST' + str(i)))

이제 bulk insert를 실행 후 결과를 확인해보겠습니다.

cursor.prepare('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )')
cursor.executemany(None, ARRAY)
cursor.execute('SELECT * FROM SCOTT.TESTPY')

>>> print(cursor.fetchall())
[(0, 'TEST0'), (1, 'TEST1'), (2, 'TEST2'), (3, 'TEST3'), (4, 'TEST4'), (5, 'TEST5'), (6, 'TEST6'), (7, 'TEST7'), (8, 'TEST8'), (9, 'TEST9')]

cx_Oracle.Cursor.executemany( )에 statement도 같이 전달하는 방법도 있습니다.
일단 값들을 삭제 한 후 시도해 보겠습니다.

>>> cursor.execute('DELETE SCOTT.TESTPY')
>>> print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
[]

>>> cursor.executemany('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )', ARRAY)
>>> print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
[(0, 'TEST0'), (1, 'TEST1'), (2, 'TEST2'), (3, 'TEST3'), (4, 'TEST4'), (5, 'TEST5'), (6, 'TEST6'), (7, 'TEST7'), (8, 'TEST8'), (9, 'TEST9')]

* 예제 Code

	import cx_Oracle
	conn = cx_Oracle.connect('sys/orange@localhost:1521/orcl', mode = cx_Oracle.SYSDBA)
	cursor = conn.cursor()
	cursor.execute('SELECT * FROM SCOTT.EMP')
	print(cursor.fetchone())
	print(cursor.fetchone())
	print(cursor.fetchmany(2))
	print(cursor.fetchall())
	cursor.execute('SELECT * FROM SCOTT.EMP')
	for record in cursor:
	print(record)
	cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , [ 2000, 20 ])
	print(cusor.fetchall())
	cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , { 'SAL' : 2000 , 'DEPTNO' : 20 })
	print(cusor.fetchall())
	CREATE_SCRIPT = """
	CREATE TABLE SCOTT.TESTPY (
	ID INT,
	NAME VARCHAR(32)
	)
	"""
	cursor.execute(CREATE_SCRIPT)
	ARRAY = []
	for i in range(0,10): ARRAY.append((i, 'TEST' + str(i)))
	cursor.prepare('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )')
	cursor.executemany(None, ARRAY)
	cursor.execute('SELECT * FROM SCOTT.TESTPY')
	print(cusor.fetchall())
	cursor.execute('DELETE SCOTT.TESTPY')
	print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
	cursor.executemany('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )', ARRAY)
	print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
	cursor.execute('DROP TABLE SCOTT.TESTPY')
	conn.rollback()
	cursor.close()
	conn.close()

view raw pyOracleEx03_bind.py hosted with ❤ by GitHub