ORACLE学习经验(二)-- Python连接ORACLE数据库
前提:确保使用SQLPLUS可以成功连接到数据库
C:\xxxx>sqlplus system/xxxx@KDB as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 6月 6 10:37:17 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
然后进入今天的正题:在Python中实现数据库连接及SQL操作
库:cx_Oracle
通过pip工具或者Pycharm设置中的ProjectInterpreter安装此库。
官方给出的API:
https://cx-oracle.readthedocs.io/en/latest/index.html
- 连接函数
Connection1 = cx_Oracle.Connection(....)
Connection2 = cx_Oracle.connect(....)
(上述两个函数我实际使用感觉没有区别)
若需要登录特殊身份账号:
官方给出以下API:
cx_Oracle.SYSDBA
This constant is used to specify that SYSDBA access is to be acquired.
该API返回一个变量。
但从官方文档中没看懂具体用法,
此处引用他人博客:
http://blog.itpub.net/27042095/viewspace-757576/
conn = cx_Oracle.connect("user/pw@HOST:PORT/SERVICE_NAME",mode=cx_Oracle.SYSDBA)
- 创建游标
Cursor1 = Connection1.cursor()
该函数返回一个空的游标变量。 - 游标赋值
Cursor1.execute("select * from dba_tables")
该函数的输入为待执行的SQL语句,执行过后游标既有值。
储存在其内部变量Cursor1.description
中。 - 输出结果
print(Cursor1.description)
整合成代码如下:
import cx_Oracle
Connection1 = cx_Oracle.connect("system/Clkws123@localhost:1521/KDB",mode=cx_Oracle.SYSDBA)
Cursor1 = Connection1.cursor()
Cursor1.execute("select table_name from dba_tables where initial_extent=139264")
print(Cursor1.description)
执行结果:
Connected to pydev debugger (build 191.6605.12)
[('TABLE_NAME', <class 'cx_Oracle.STRING'>, 30, 30, None, None, 0)]
Process finished with exit code 0