--Using Oracle Cloud
Same as the basic SELECT statement, execute the SQL statement with the execute () method of the Cursor object. Below is a sample.
sample10a.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)
"""
SQL2 = "insert into dept values(10, 'ACCOUNTING', 'NEW YORK')"
SQL3 = "commit"
SQL4 = "select * from dept"
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL1)
cursor.execute(SQL2)
cursor.execute(SQL3)
print(cursor.execute(SQL4).fetchone())
The handling of bind variables is basically the same as 7th. Below is a sample. It is assumed that the above sample10a.py is executed.
sample10b.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = "insert into dept values(:deptno, :dname, :loc)"
SQL2 = "commit"
binds = [{"deptno":20, "dname":"RESEARCH", "loc":"DALLAS"},
{"deptno":30, "dname":"SALES", "loc":"CHICAGO"}]
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.prepare(SQL1)
cursor.executemany(None, binds)
cursor.execute(SQL2)
The executemany () method on the second line from the bottom executes a PreparedStatement for the number of elements in the list of the second parameter, as you can imagine from the usage.
The null value of DB corresponds to None on Python. If you want to store NULL, set the bind variable to None. If you SELECT a null value, None is stored in the variable.
So far, we've only seen examples of bind variables that pass values to SQL. In fact, there are also bind variables that are received from SQL. In this case, you can use the var method of the Cursor object to create a Python variable that acts as a bind variable that receives the value. The following is a sample of using bind variables that receive the RETURNING clause. It is assumed that the above sample10b.py is executed.
sample10c.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
update dept set deptno = deptno + 10 where deptno > 0
returning deptno into :out_deptno
"""
SQL2 = "rollback"
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
bind_deptno = cursor.var(int)
cursor.execute(SQL1, out_deptno = bind_deptno)
print(f"DEPTNO = {bind_deptno.getvalue()}")
print(f"RowCount = {cursor.rowcount}")
cursor.execute(SQL2)
The fifth line from the bottom is the definition of Python variables for the bind variables that receive the output from SQL. Specify the Python data type when receiving data in the argument of the var variable. The third line from the bottom, getvalue (), receives the contents of the bind variable in the RETURNING clause of the SQL statement. The argument defaults to 0 and specifies the position of the bind variable. Since there is only one bind variable this time, 0 (first) is fine. In the case of this example, the updated column values that are printed are returned in the form of a list of ints.
The second line from the bottom of the previous sample is applicable. After executing the update SQL, you can get the updated number of rows by accessing the rowcount attribute of the Cursor object in the sample. If you access this attribute value in a SELECT statement, the number of rows fetched at that time is stored.
Recommended Posts