Python - Connect to Oracle DB Examples @ DanBrother的部落格

Python - Connect to Oracle DB Examples

cx_Oracle is a Python extension module that enables access to Oracle Database and conforms to the Python database API specification. This module is currently built against version for Oracle Client 11.2 onwards and Python 2.7, 3.4, 3.5 and 3.6.

cx_Oracle can be downloaded from https://pypi.python.org/pypi/cx_Oracle

However, the fastest way to install cx_Oracle 6 Beta is with pip:
python -m pip install cx_Oracle –pre

oracle_example1.py

import cx_Oracle
# connect via SQL*Net string or by each segment in a separate argument
# connection = cx_Oracle.connect("user/password@TNS")

connection = cx_Oracle.connect("scott", "tiger", "testdb")

cursor = connection.cursor()
cursor.execute("""
        select empno,ename,job,sal,deptno
        from emp
   order by deptno,ename
"""
   )
print("empno \t ename\t\t job \t\t sal \t\t deptno \t")
print("===== \t ======\t\t ========== \t ====== \t ====== \t")
for empno, ename, job, sal, deptno in cursor:   print(str(empno).ljust(8),ename.ljust(15),job.ljust(15),
   str(sal).ljust(15),str(deptno).ljust(15))

cursor.close()
connection.close()

Python 3.6.1 (v3.6.1:69c0db5, Mar 21 2017, 17:54:52) [MSC v.1900 32 bit (Intel)] on win32

Type "copyright", "credits" or "license()" for more information.

>>>

===== RESTART: C:\\Python-Know-how\oracle_example1.py =====

oracle_example2.py

import cx_Oracle
# connect via SQL*Net string or by each segment in a separate argument
# connection = cx_Oracle.connect("user/password@TNS")

connection = cx_Oracle.connect("scott", "tiger", "testdb")
cursor = connection.cursor()
v_deptno = input("Please enter the department number:")
cursor.execute("""
        select empno,ename,job,sal,deptno
        from emp
   where deptno = :arg_1
   """,
   arg_1 = v_deptno
   )
print("empno \t ename\t\t job \t\t sal \t\t deptno \t")
print("===== \t ======\t\t ========== \t ====== \t ====== \t")
for empno, ename, job, sal, deptno in cursor:
   print(str(empno).ljust(8),ename.ljust(15),job.ljust(15),
   str(sal).ljust(15),str(deptno).ljust(15))

cursor.close()
connection.close()

Python 3.6.1 (v3.6.1:69c0db5, Mar 21 2017, 17:54:52) [MSC v.1900 32 bit (Intel)] on win32

Type "copyright", "credits" or "license()" for more information.

>>>

===== RESTART: C:\\Python-Know-how\oracle_example2.py =====

oracle_example3.py

import cx_Oracle
import os, sys
# connect via SQL*Net string or by each segment in a separate argument
# connection = cx_Oracle.connect("user/password@TNS")
connection = cx_Oracle.connect("scott", "tiger", "testdb")
cursor = connection.cursor()
v_deptno = input("Please enter the department number:")
cursor.execute("""
        select empno,ename,job,sal,deptno
        from emp
   where deptno = :arg_1
   """,
arg_1 = v_deptno
)
fd = os.open("Dept-"+v_deptno+".txt", os.O_RDWR | os.O_CREAT)
os.write(fd, b"Empno   Ename          Job            Sal            Deptno \n")
os.write(fd, b"=====   ======         ==========     ======         ====== \n")
for empno, ename, job, sal, deptno in cursor:
    v_tuple = str(empno).ljust(8),ename.ljust(15),job.ljust(15),
str(sal).ljust(15),str(deptno).ljust(15),"\n"
    v_string = ''.join(v_tuple)
    v_bytes = str.encode(v_string)
    os.write(fd,v_bytes)

cursor.close()
connection.close()
os.close(fd)