python操作oracle数据库-查询
python操作oracle数据库-查询
参照⽂档
DB API 2.0 和 cx_Oracle 介绍
Python 数据库 API 规范 v2.0 是集体努⼒的成果，⽤于统⼀不同数据库系统的访问模型。拥有⼀组相对较少的⽅法和属性，在更换数据库供应商时就易于学习并保持⼀致。它不以任何⽅式将数据库对象映射到 Python 结构中。⽤户仍然需要⼿⼯编写 SQL。在更换到另⼀数据库后，此 SQL 可能需要重新编写。尽管如此，它还是出⾊妥善地解决了 Python 数据库的连接性问题。
该规范定义了 API 的各个部分，如模块接⼝、连接对象、游标对象、类型对象和构造器、DB API 的可选扩展以及可选的错误处理机制。
数据库和 Python 语⾔之间的⽹关是连接对象。它包含制作数据库驱动的应⽤程序所需的全部组件，不仅符合 DB API 2.0，⽽且是规范⽅法和属性的⼀个超集。在多线程的程序中，模块和连接可以在不同线程间共享，但是不⽀持游标共享。这⼀限制通常是可接受的，因为共享游标可能带来死锁风险。
Python ⼤量使⽤了异常模型，DB API 定义了若⼲标准异常，它们在调试应⽤程序中的问题时会⾮常有⽤。下⾯是⼀些标准异常，同时提供了原因类型的简要说明：
Warning — 数据在执⾏插⼊操作时被截断，等等
Error — 这⾥提到的除 Warning 外的所有异常的基类。
InterfaceError — 数据库接⼝⽽⾮数据库本⾝故障（本例为 cx_Oracle 问题）
DatabaseError — 严格意义上的数据库问题
DataError — 包含如下结果数据的问题除数为 0，值超出范围等
OperationalError — 与编程⼈员⽆关的数据库错误：连接丢失、内存分配错误、事务处理错误等
IntegrityError — 数据库的关系完整性受到了影响，例如，外键约束失败
InternalError — 数据库遇到内部错误，例如，游标⽆效、事务不同步
ProgrammingError — 未到表、SQL 语句中的语法错误、指定参数的数量错误等
NotSupportedError — 调⽤的 API 部件并不存在
连接过程⾸先从连接对象开始，这是创建游标对象的基础。除游标操作外，连接对象还使⽤ commit() 和 rollback() ⽅法对事务进⾏管理。执⾏ SQL 查询、发出 DML/DCL 语句和获取结果这些过程均受游标控制。
第⼀步：导⼊cx_Oracle ，建⽴连接
>>> import cx_Oracle
>>> db = t('hr', 'hrpwd', 'localhost:1521/XE')
>>> db1 = t('hr/hrpwd@localhost:1521/XE')
>>> dsn_tns = cx_Oracle.makedsn('localhost', 1521, 'XE')
>>> print dsn_tns
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=XE)))
>>> db2 = t('hr', 'hrpwd', dsn_tns)
# 通过客户端连接oracle
connection = t('test/test@ORCL')
第⼆步：建⽴ Cursor 光标，查询
您可以使⽤连接对象的 cursor() ⽅法定义任意数量的游标。简单的程序使⽤⼀个游标就可以了，该游标可以⼀再地重复使⽤。但较⼤的项⽬可能要求⼏个不同的游标。
>>> cursor = db.cursor()
应⽤程序逻辑通常需要明确区分针对数据库发出的语句的各个处理阶段。这有助于更好地理解性能瓶颈并编写更快且经过优化的代码。语句处理分三个阶段：
1. 分析（可选）
cx_Oracle.Cursor.parse([statement])
实际上并不需要调⽤，因为在执⾏阶段会⾃动分析 SQL 语句。该⽅法可以⽤于在执⾏语句前对其进⾏验证。当这类语句中检测出错误
时，会引发 DatabaseError 异常，相应的错误消息通常可能是“ORA-00900:invalid SQL statement, ORA-01031:insufficient privileges or
ORA-00921:unexpected end of SQL command.”
2. 执⾏
cx_ute(statement, [parameters], **keyword_parameters)
此⽅法可以接受单个参数 — ⼀条 SQL 语句 — 直接针对数据库来运⾏。通过 parameters 或 keyword_parameters 参数赋值的绑定变量可以指定为字典、序列或⼀组关键字参数。如果已经提供了字典或关键字参数，那么这些值将与名称绑定。如果给出的是序列，将根据这些值的位置对它们进⾏解析。如果是查询操作，此⽅法返回⼀个变量对象列表；如果不是，则返回 None。
cx_utemany(statement, parameters)
对于批量插⼊尤其有⽤，因为它可以将所需的 Oracle 执⾏操作的数量限制为仅⼀个。有关如何使⽤该⽅法的详细信息，请参见下⾯的“⼀次多⾏”部分。
3. 获取（可选）— 仅⽤于查询（因为 DDL 和 DCL 语句不返回结果）。在不执⾏查询的游标上，这些⽅法将引发 InterfaceError 异常。
cx_Oracle.Cursor.fetchall()
以字节组列表形式获取结果集中的所有剩余⾏。如果没有剩余的⾏，它返回⼀个空⽩列表。获取操作可以通过设置游标的 arraysize 属性进⾏调整，该属性可设置在每个底层请求中从数据库中返回的⾏数。arraysize 的设置越⾼，需要在⽹络中往返传输的次数越少。
arraysize 的默认值为 1。
cx_Oracle.Cursor.fetchmany([rows_no])
获取数据库中接下来的 rows_no ⾏。如果该参数未指定，该⽅法获取的⾏数是 arraysize 的数量。如果 rows_no ⼤于获取到的⾏的数
⽬，该⽅法获取的⾏数是剩余的⾏数。
cx_Oracle.Cursor.fetchone()
从数据库中获取单个字节组，如果没有剩余⾏，则返回 none。
在继续了解游标⽰例前，请先了解 pprint 模块的 pprint 函数。它⽤于以清晰、可读的形式输出 Python 数据结构。
# 获得游标对象
cursor = connection.cursor ()
try:
# 解析sql语句
cursor.parse("select *  dual")
# 捕获SQL异常
except cx_Oracle.DatabaseError as e:
print(e)  # ORA-00923: 未到要求的 FROM 关键字
# 执⾏sql 语句
# 提取⼀条数据，返回⼀个元祖
row = cursor.fetchone()
pprint(row)  # ('X',)
数据类型
在获取阶段，基本的 Oracle 数据类型会映射到它们在 Python 中的等同数据类型中。cx_Oracle 维护⼀个单独的、有助于这⼀转换的数据类型集合。Oracle - cx_Oracle - Python 映射为
查询列字段信息
# 查询列字段信息
column_data_types = ute('SELECT * FROM python_modules')
pprint(column_data_types.description)
# [('MODULE_NAME', <class 'cx_Oracle.STRING'>, 50, 50, None, None, 0),
#  ('FILE_PATH', <class 'cx_Oracle.STRING'>, 300, 300, None, None, 0)]
绑定变量模式
正如 Oracle ⼤师 Tom Kyte 介绍的那样，绑定变量是数据库开发的核⼼原则。它们不仅使程序运⾏更快，同时可以防范 SQL 注⼊攻击。按名称传递绑定变量要求执⾏⽅法的 parameters 参数是⼀个字典或⼀组关键字参数。下⾯的 query1 和 query2 是等同的：
>>> named_params = {'dept_id':50, 'sal':1000}
>>> query1 = ute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', named_params) >>> query2 = ute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000)
在使⽤已命名的绑定变量时，您可以使⽤游标的 bindnames() ⽅法检查⽬前已指定的绑定变量：
>>> print cursor.bindnames()
['DEPT_ID', 'SAL']
# 绑定变量模式查询
named_params = {'MODULE_NAME': 'cx_Oracle'}
# 在使⽤已命名的绑定变量时，您可以使⽤游标的 bindnames() ⽅法检查⽬前已指定的绑定变量：
print(cursor.bindnames())
pprint(cursor.fetchone())
# ('cx_Oracle',如何连接oracle数据库
#  'C:\\Program '
#  'Files\\Python36\\lib\\site-packages\\cx_Oracle.cp36-win_amd64.pyd')
# 在绑定时，您可以⾸先准备该语句，然后利⽤改变的参数执⾏ None。
# 根据绑定变量时准备⼀个语句即⾜够这⼀原则，
# Oracle 将如同在上例中⼀样对其进⾏处理。准备好的语句可执⾏任意次。
cursor.prepare('SELECT * FROM python_modules where MODULE_NAME =:MODULE_NAME')
pprint(cursor.fetchone())
# ('cx_Oracle',
#  'C:\\Program '
#  'Files\\Python36\\lib\\site-packages\\cx_Oracle.cp36-win_amd64.pyd')
⼀次多⾏
⼤型的插⼊操作不需求多次的单独插⼊，这是因为 Python 通过 cx_utemany ⽅法完全⽀持⼀次插⼊多⾏。
限制执⾏操作的数量极⼤地改善了程序性能，因此在编写存在⼤量插⼊操作的应⽤程序时应⾸先考虑这⼀功能。
我们⾸先为 Python 模块列表创建⼀个表，这次直接从 Python 开始。您将在以后删除该表。
import cx_Oracle
# ⽤于以清晰、可读的形式输出 Python 数据结构
from pprint import pprint
from sys import modules
# 通过客户端连接oracle
connection = t('test/test@testDB')
print(connection.version)
# 获得游标对象
cursor = connection.cursor ()
try:
# 解析sql语句
cursor.parse("select *  dual")
# 捕获SQL异常
except cx_Oracle.DatabaseError as e:
print(e)  # ORA-00923: 未到要求的 FROM 关键字
# 执⾏sql 语句
# 提取⼀条数据，返回⼀个元祖
row = cursor.fetchone()
pprint(row)  # ('X',)
create_table = """
CREATE TABLE python_modules (
module_name VARCHAR2(50) NOT NULL,
file_path VARCHAR2(300) NOT NULL
)
"""
# 执⾏创建表
create_flag = ute(create_table)
# 添加模块信息
M = []
for m_name, m_info in modules.items():
try:
M.append((m_name, m_info.__file__))
except AttributeError:
pass
print(len(M))
insert_sql = "INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)"
# 在prepare之后，你再去execute的时候，就不⽤写上sql语句参数了
cursor.prepare(insert_sql)
connectionmit()  # 提交
# 查询
r = ute("SELECT COUNT(*) FROM python_modules")
pprint(cursor.fetchone())
# 查询列字段信息
column_data_types = ute('SELECT * FROM python_modules')
pprint(column_data_types.description)
# [('MODULE_NAME', <class 'cx_Oracle.STRING'>, 50, 50, None, None, 0),
#  ('FILE_PATH', <class 'cx_Oracle.STRING'>, 300, 300, None, None, 0)]
# 取10条记录信息
pprint(len(cursor.fetchmany(10)))  # 10
# 取之后所有记录信息，不包括前10条
pprint(len(cursor.fetchall()))  # 41
# 绑定变量模式查询
named_params = {'MODULE_NAME': 'cx_Oracle'}
print(cursor.bindnames())
pprint(cursor.fetchone())
# ('cx_Oracle',
#  'C:\\Program '
#  'Files\\Python36\\lib\\site-packages\\cx_Oracle.cp36-win_amd64.pyd')
# 在绑定时，您可以⾸先准备该语句，然后利⽤改变的参数执⾏ None。
# 根据绑定变量时准备⼀个语句即⾜够这⼀原则，
# Oracle 将如同在上例中⼀样对其进⾏处理。准备好的语句可执⾏任意次。
cursor.prepare('SELECT * FROM python_modules where MODULE_NAME =:MODULE_NAME') ute(None, named_params)
pprint(cursor.fetchone())
# ('cx_Oracle',
#  'C:\\Program '
#  'Files\\Python36\\lib\\site-packages\\cx_Oracle.cp36-win_amd64.pyd')
# 删除python_modules
# 关闭游标
cursor.close()
# 关闭连接
connection.close ()
# BLOB & CLOB 格式的创建:
#
# binary_content = cursor.var(cx_Oracle.BLOB)
# binary_content.setvalue(0, content)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。