The basic operation of python3 to connect to oracle

Connect to oracle database

To operate a database, the first is to connect to the database. cx_Oracle provides two connection interfaces, one is Connection and the other is connect. Both connections return a connection object, and the two connection interfaces are defined as follows:

cx_Oracle.Connection([user, password, dsn, mode, handle, pool,
threaded, events, cclass, purity, newpassword,encoding, nencoding,
edition, appcontext, tag, matchanytag ])
cx_Oracle.connect( [user, password, dsn, mode, handle, pool, threaded,
events, cclass, purity, newpassword,encoding, nencoding, edition,
appcontext, tag, matchanytag ])

Parameter analysis

dsn: 数据源名称,可以从makedsn函数返回,如果只传一个参数,那么字符格式为user/[email protected]

mode: 如果这个参数被指定了,那么这个参数一定是SYSDBA, SYSASM 或 SYSOPER,否则它默认使用正常连接模式

handle: 如果handle参数被指定,那么它的类型必须是OCISvcCtx*,并且它仅仅被使用在已经建立连接的python应用程序中

pool: 该参数期望是一个session pool对象,使用了此参数,需要调用pool.acquire()才从连接池中获取连接对象

threaded: 该参数期望是一个布尔表达式,表示Oracle是否应该包含对互斥体的连接访问。 在单线程应用程序中执行此操作会带来性能降低10-15%,这就是该参数默认设置为False

events: 该参数期望是一个布尔表达式,它表示是否需要初始化Oracle事件模式

cclass: 该参数期望是一个字符串,是被定义为驻留在数据库中的连接池

purity: 该参数期望是ATTR_PURITY_NEW, ATTR_PURITY_SELF, 或ATTR_PURITY_DEFAULT中的一个

newpassword: 该参数期望是一个字符串,如果该参数被指定,那么在连接过程中是用设置的登录密码

encoding: 该参数期望是一个字符串,如果该参数被指定且被设置,那么该编码被设置为数据库的常规字符串

nencoding: 该参数期望是一个字符串,如果指定,并将国家编码设置为用于国家字符集数据库字符串

edition: 该参数期望是一个字符串,如果被指定且被设置,那么它被用于session版本,它只在客户端和服务端版本至少是Oracle Database 11.2中有效

appcontext: 该参数期望是一个3元组的列表,如果被指定了,那么它将设置应用上下文给数据库连接。应用程序上下文通过使用sys_context()PL / SQL方法在数据库中可用,并且可以在登录触发器以及任何其他PL / SQL过程中使用。 列表中的每个条目都将包含三个字符串:命名空间,名称和值

tag: 该参数期望是一个字符串,如果被指定它将限制可从中返回的会话会话池,除非matchanytag参数设置为True。在这种情况下与指定标签的会话将优于其他人,但是如果没有这样的会话可用,则可以返回具有不同标签的会话代替。 在任何情况下,如果没有指定标签的会话可用,将始终返回未标记的会话。 会话被释放回池时被标记

Example:

import cx_Oracle

host = 'localhost'  # IP地址
port = 1521  # 端口号
database = 'orcl'  # 服务名 进Oracle数据库在要查询的数据库下面执行select value from v$parameter where name like '%service_name%';
dsn = cx_Oracle.makedsn(host, port, database)  # 构建数据源

username = "wangtaozhi"  # 用户名
password = "admin"  # 密码
connection = cx_Oracle.connect(username, password, dsn)  # 创建连接
cursor = connection.cursor()  # 创建游标

sql = 'select * from s'  # sql语句
cursor.execute(sql)  # 执行sql语句
result = cursor.fetchall()
print("Total: " + str(cursor.rowcount))
for row in result:
	print(row)

cursor.close()  # 关闭游标
connection.close()  # 关闭数据库连接

Operational database

Cursors must be used to operate the database. The interface of the cursor is defined as follows

cx_Oracle.Cursor(connection)

Construct a cursor, return a cursor object, the parameter connection is Connection, the connection object returned by the connect interface

Performing database operations
There are three interface functions for performing database operations, namely execute, executemany, and executemanyprepared. The three interfaces are defined as follows:

1. The Cursor.execute(statement[, parameters ], **keywordParameters)
parameter can be a field, sequence, or keyword. If the parameter is a dictionary, it will be bound to a name. If it is a sequence, it will be bound by position from left to right. Position is not recommended Binding, it is recommended to use parameter binding.
Example:

import cx_Oracle
import pdb
# 构建数据源
dsn = cx_Oracle.makedsn('loaclhost', '1521', 'tomp').replace('SID', 'SERVICE_NAME')
# 获取数据库连接
conn = cx_Oracle.connect('username', 'password', dsn)
# 获取游标
cursor = conn.cursor()
# 使用序列参数执行数据库操作
cursor.execute('insert into test(name, age) values(:1, :2)', ('gavin', '20'))
# 使用字典参数执行数据库操作
cursor.execute('insert into test(name, age) values(:name, :age)', {'name':'kevin', 'age':'30'})
# cursor.execute('insert into test(name, age) values(:name, :age)', {'age':'30', 'name':'kevin'})
# 使用关键字参数执行数据库操作
args = {'name':'kuke', 'age':'40'}
cursor.execute('insert into test(name, age) values(:name, :age)', **args)
conn.commit()
cursor.close()
conn.close()

The advantage of using dictionary parameter binding is that your parameter position can be changed at will, and will not affect the result of your operation

2. Cursor.executemany(statement, parameters, batcherrors=False, arraydmlrowcounts=False)
Literally know that this interface can perform multiple data operations at once, and this parameter requires a list parameter.
batcherrors: If true, the batcherrors parameter enables batch error support in Oracle and ensures that even if an exception occurs in one or more parameter sequences, the call will succeed. You can then use getbatcherrors() to retrieve the errors.
arraydmlrowcounts: If true, the arraydmlrowcounts parameter can be used to retrieve the row count using Oracle DML after executing the Oracle method and then the row count
can be retrieved using getarraydmlrowcounts().
Example:

import cx_Oracle
import pdb
# 构建数据源
dsn = cx_Oracle.makedsn('localhost', '1521', 'tomp').replace('SID', 'SERVICE_NAME')
# 获取数据库连接
conn = cx_Oracle.connect('username', 'password', dsn)
# 获取游标
cursor = conn.cursor()
# 使用序列参数执行数据库操作
params = [('gavin', '20'), ('kevin', '20'), ('kuke', '20')]
cursor.executemany('insert into test(name, age) values(:1, :2)', params)
# 使用字典参数执行数据库操作
params = [{'name':'kevin', 'age':'30'}, {'name':'gavin', 'age':'30'}, {'name':'kuke', 'age':'30'}]
cursor.executemany('insert into test(name, age) values(:name, :age)', params)
# 使用预处理执行数据库操作
cursor.prepare('insert into test(name, age) values(:name, :age)')
params = [{'name':'kevin', 'age':'30'}, {'name':'gavin', 'age':'31'}, {'name':'kuke', 'age':'32'}]
cursor.executemany(None, params)
conn.commit()
cursor.close()
conn.close()

3. Cursor.executemanyprepared(numIters)
This interface is to execute a statement that has been prepared and bound parameters for a specified number of times.
Example:

import cx_Oracle
import pdb
# 构建数据源
dsn = cx_Oracle.makedsn('localhost', '1521', 'tomp').replace('SID', 'SERVICE_NAME')
# 获取数据库连接
conn = cx_Oracle.connect('username', 'password', dsn)
# 获取游标
cursor = conn.cursor()
# 使用预处理执行数据库操作
cursor.prepare("""insert into test(name, age) values('gavin', '30')""")
# insert语句将被执行3次,表中的结果会有3条记录,这个函数还有没有其它的使用方式暂时还没有摸索出来,如果你知道请留言给我。
cursor.executemanyprepared(3)
conn.commit()
cursor.close()
conn.close()

Query data

There are mainly 4 interfaces defined in the result set returned by query data as follows

Cursor.fetchall()

Get all the query results, the result set it returns is only a list of tuples

Cursor.fetchmany([numRows=cursor.arraysize ])

If the interface does not pass parameters, its effect is the same as fetchall. If parameters are passed, it means to query the specified number of records. If the result in the table is less than the specified query number, all data in the table will be returned.

Cursor.fetchone()

This interface is to obtain records one by one, and return None after obtaining all records.

Cursor.fetchraw

This interface returns the number of rows actually obtained, and extracts the next set of query results into the internal buffer defined by the cursor.
Example:

import cx_Oracle
import pdb
# 构建数据源
dsn = cx_Oracle.makedsn('192.168.102.37', '1521', 'tomp').replace('SID', 'SERVICE_NAME')
# 获取数据库连接
conn = cx_Oracle.connect('doron_tomp', 'doron1234~', dsn)
# 获取游标
cursor = conn.cursor()
cursor.execute('select * from test')
# 返回所有的查询结果
result = cursor.fetchall()
print result
# 返回指定数量的查询结果
result = cursor.fetchmany(10)
print result
# 一行一行获取所有查询结果
while True:
	result = cursor.fetchone()
	print result
	if result is None:
		break
		
# 返回实际获取的行数,它将下一组查询结果提取到游标定义的内部缓冲区中
result = cursor.fetchraw(10)
print result
cursor.close()
conn.close()