实践2-1 python连接Oracle数据库
java连接mysql数据库在:
这部分包含：python连接Oracle、以及window定时任务schtasks的一些简单设置。
1、python连接Oracle数据库
1.1 oracle连接环境配置
注意：1.1部分的环境配置仅仅是其中一个我自己使用的方法，在1.2中会包含官网中使用代码指定oracle_client的方法。
一般来说，在你本地配置plsql访问oracle的环境，配置成功后，再运行python连接oracle数据库就没问题了。
在配置之前，ping一下oracle的ip，确定网络是互通的。然后安装好合适版本位数的plsql。
由于远端oracle并非自己配置的，访问的驱动、配置文件等也是别人给的，而且还有点乱七八糟，所以有关版本的问题个人也不是很清楚。
按照教程所说:"到oracle官网下载instantclient basic包，解压缩到E:\ProgramFile\instantclient。"
1.1.1 环境变量配置
我是按图索骥，如果之前配置成功过，可以打开配置好的plsql(可以处于未登录状态)，查看帮助-支持信息，以及电脑中相关的环境变量在新环境中进行如下配置。
设置环境变量如下，
Path=...;E:\ProgramFile\instantclient NLS_lANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK TNS_ADMIN= E:\ProgramFile\instantclient ORACLE_HOME= E:\ProgramFile\instantclient
即：
-
将instantclient目录添加到Path，
-
NLS_lANG配置则是防止中文乱码等情况的出现，
-
TNS_ADMIN指向tnsnames.ora所在目录，
-
ORACLE_HOME我就不怎么确定了，因为我的oci.dll和tnsnames.ora在两个目录中，plsql的安装目录又是另一个（我不想管了，能跑就行），我ORACLE_HOME指向的是oci.dll所在的目录，但是后面涉及的某个错误中描述的是"在环境变量中增加 ORACLE_HOME 指向64位安装目录，而不是 instantclient 目录"
1.1.2 PlSQL 配置
中文版进入“工具”-“首选项”-oracle“连接”，将oci.dll所在目录及oci.dll文件路径填入下面红框框的位置，一般是
/* Oracle主目录 */ E:\ProgramFile\instantclient /* OCI库 */ E:\ProgramFile\instantclient\oci.dll
1.1.3 配置tnsnames.ora文件
这个文件需要配置，包含你连接数据库的ip、端口(1521)及服务名等信息。可配置多个连接，也可以只配置一个
# tnsnames.ora Network Configuration File: 文件目录\tnsnames.ora # Generated by Oracle configuration tools. 连接名1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip2)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 服务名) ) ) 连接名2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 服务名) ))
1.1.4 一些报错
一般来说是配置过程出错的原因，出错首先检查配置过程，过程没问题再考虑其他，仅仅记录一部分
-
登录界面连接为空：tnsnames.ora未正确配置，好像无法连接远端还会报错'tns....'之类的
-
登录之后界面正常，但是select的结果界面为空，无论访问任何表(甚至dual)都是这样子。初步怀疑是版本问题，所以后面我按照之前的方法将两个文件包都配置进去了。
-
Error while trying to retrieve text for error，如下
1.1.5 [报错] Error while trying to retrieve text for error
这个问题出现在我未配置ORACLE_HOME的时候，说是plsql客户端和oracle服务之间32位与64位的冲突，给出的解决方法是“在环境变量中增加 ORACLE_HOME 指向64位安装目录，而不是 instantclient 目录。”以及最后根本的更换下载的instantclient basic包的版本。
我自己的解决方法（由于之前配置成功过，但是相关的文件包比较乱），将ORACLE_HOME指向合适版本的oci.dll所在目录。
1.2 cx_Oracle
cx_Oracle的主页为:https://oracle.github.io/python-cx_Oracle/index.html
其开发文档为:https://cx-oracle.readthedocs.io/en/latest/index.html
1.2.1 安装与配置
若是该命令无法安装，可下载相关安装包后手动配置安装，也可以添加代理。
pip install cx_Oracle python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade
在window上，提供了两种配置方法，一种如1.1部分一样，在自己本地配置好一个可用的客户端，配置好环境变量。还有一个是配置Oracle即时客户端。具体可看:https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#installing-cx-oracle-on-windows
-
在 Windows 上，cx_Oracle 按如下方式查找 Oracle 客户端库：
-
在
lib_dir调用中指定的目录中 -
如果
lib_dir未指定，则在安装 cx_Oracle 二进制模块的目录中查找 Oracle 客户端库。此目录应包含解压缩的 Instant Client 'Basic' 或 'Basic Light' 包中的库。如果未找到库，则不会引发异常并继续搜索，请参阅下一个要点。 -
在系统库搜索路径上的目录中，例如
PATH环境变量。如果无法加载 Oracle 客户端库，则会引发异常。
-
import cx_Oracle cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_11")
如果要使用可选的Oracle配置文件，如tnsnames.ora,selnet.ora或oraacess.xml与即时客户端，将对应文件放在某个可访问的路径下，如c:\oracle\dir，然后在代码中指定。
import cx_Oracle cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_11", config_dir=r"C:\oracle\your_config_dir")
1.2.2 数据库连接
cx_Oracle进行数据库连接的方式有两种，独立连接与连接池。
独立连接由cx_Oracle.connect()或其别名创建cx_Oracle.Connection()，其参数如下所示
cx_Oracle.connect(user=None, password=None, dsn=None, mode=cx_Oracle.DEFAULT_AUTH, handle=0, pool=None, threaded=False, events=False, cclass=None, purity=cx_Oracle.ATTR_PURITY_DEFAULT, newpassword=None, encoding=None, nencoding=None, edition=None, appcontext=[], tag=None, matchanytag=None, shardingkey=[], supershardingkey=[])
比较常用的参数有user,password,dsn，其中dsn有以下几种形式：
import cx_Oracle # 简单连接 dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1\sname') #使用默认端口1521 dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1：1521\sname') #指定端口 # 通过配置好的dns连接 # 方法1：通过封装的方法配置dhs dns=cx_Oracle.makedsn("127.0.0.1", 1521, service_name="sname") dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns=dns,encoding="UTF-8") # 方法2：直接配置 dsn = """(DESCRIPTION= (FAILOVER=on) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=196.121.78.3)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=sname)))""" dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns=dns,encoding="UTF-8") # 方法3：将dns的配置放在tnsnames.ora文件中，如上面1.1.3中配置的“连接1” dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='连接1',encoding="UTF-8")
其他的参数如threaded在使用多线程访问数据库的时候（并没有设置池），有设置为true，其具体功能并未深究。
而数据库在使用完毕的时候需要关闭数据库，可以使用dbconn.close()，或者说更建议使用with语句
with cx_Oracle.connect(user=user, password=password, dsn="dbhost.example.com/orclpdb1", encoding="UTF-8") as connection: cursor = connection.cursor() cursor.execute("insert into SomeTable values (:1, :2)", (1, "Some string")) connection.commit()
当应用程序频繁连接与断开与数据库连接时，连接池对于性能很重要。池支持Oracle的高可用性特性，建议用于必须可靠的应用程序。池是cx_Oracle.SessionPool()在应用程序初始化时创建的。连接池具体的使用留到后面多线程访问的时候整理。
更加详细的可看:
1.2.3 数据库操作
执行SQL语句是Python应用程序与Oracle数据库通信的主要方式。语句是使用方法Cursor.execute()或执行Cursor.executemany()。语句包括查询、数据库操作语音(DML)和数据定义语言(DLL)。也可以执行一些特殊语句。
SQL语句不应包含尾随分号;或正斜杠/。
查询只能使用Cursor.execute()执行，可通过迭代行，或者可以使用Cursor.fetchone()、Cursor.fetchmany()或Cursor.fetchall()来获取数据。
其中光标Cursor由Connect.cursor()返回，用于执行sql语句。光标使用结束后需要确保关闭，可以使用close()方法或者with语句。
import cx_Oracle dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1：1521\sname') cur = dbconn.cursor() #创建光标 # 方法1 迭代行 for row in cur.execute('select * from my_table') print(row) # 方法2 fetchone cur.execute('select * from my_table') while True: rs = cur.fetchone() if rs==None: break print(rs) # 方法3 fetchmany--返回指定行数的数据 cur.execute('select * from my_table') row_nums=10 while True: rows = cur.fetchmany(row_nums) if not rows: break for r in rows: print(r) # 方法4 fetchall --返回全部数据 cur.execute('select * from my_table') rows = cur.fetchall() for r in rows: print(r) cur.close()#关闭光标
SQL查询还可以查询列名和数据类型。
with connection.cursor() as cur: cur.execute("select * from MyTable") for column in cur.description: print(column)
1.2.3.2 可滚动光标
通过scrollable=True在创建光标时设置参数来创建可滚动光标。
cursor = connection.cursor(scrollable=True) cursor.execute("select * from ChildTable order by ChildId") cursor.scroll(mode="last") print("LAST ROW:", cursor.fetchone()) cursor.scroll(mode="first") print("FIRST ROW:", cursor.fetchone()) cursor.scroll(8, mode="absolute") print("ROW 8:", cursor.fetchone()) cursor.scroll(6) print("SKIP 6 ROWS:", cursor.fetchone()) cursor.scroll(-4) print("SKIP BACK 4 ROWS:", cursor.fetchone())
1.2.3.3 限制行
查询数据通常分为一组或多组：
-
给出查询必须处理的行数的上限，这有助于提高数据库的可伸缩性。
-
执行“网页分页”，允许根据需要从一组行移动到下一组或上一组。
-
用于获取连续小集合中的所有数据以进行批处理，这个可通过调用fecthmany来处理
而其他的方法都要依托于SQL语句来实现。在文档中有提到过"Oracle Database 12c SQL 引入了一个OFFSET/FETCH子句，它类似于LIMITMySQL的关键字。"，只是这个我并没有使用过。
一般来说，使用的都是ROWNUM、MAX_NUM、MIN_NUM和ROW_NUMBER()来进行限制。
需要注意的一点在于,ROWNUM似乎是与查询结果绑定而不是表中结构绑定，譬如说，分页取结果，若是使用下面的方法，是错误的。
select * from my_table where rownum<=10; --取前10 select * from my_table where rownum>10 and rownum<=20; --并不能取到10-20，,结果为空
使用ROWNUM进行行数限制的时候，需要先绑定变量。
select * from (select t.*,rownum as rnum from my_table t) where rnum >10 and rnum <=20;
1.2.3.4 数据更新插入
可以使用cx_Oracle轻松执行SQL数据操作语言语句(DML)，例如INSERT和UPDATE。
with connection.cursor() as cur: cur.execute("insert into MyTable values (:idbv, :nmbv)", [1, "Fredico"])
-
不要将用户数据连接或插入到 SQL 语句中。请参阅
-
有关提交和回滚数据更改的最佳实践，请参阅
-
处理多个数据值时，
当运行SQL语句时涉及到参数传递时，可以直接使用python拼接字符串，只是并不推荐。
cur.execute("insert into MyTable values ({}, {})".format(1, "Fredico"))
一般使用绑定变量，防止SQL注入。
cur.execute("insert into MyTable values (:idbv, :nmbv)", [1, "Fredico"])
而数据更新及插入时的事务处理，则是与方法
-
自动提交connection.autocommit在合适的时候设置为True，则不需要自己调用提交和回滚。
-
当存在多条语句操作时可在结束后再进行提交。
-
当数据库连接关闭时，或者当引用连接的变量超出范围时，任何未提交的事务都将被回滚，
1.2.3.5 批处理语句执行
data = [ (10, 'Parent 10'), (20, 'Parent 20'), (30, 'Parent 30'), (40, 'Parent 40'), (50, 'Parent 50') ] cursor.executemany("insert into ParentTable values (:1, :2)", data)
处理数据错误，输出报错的语句。
data = [ (60, 'Parent 60'), (70, 'Parent 70'), (70, 'Parent 70 (duplicate)'), (80, 'Parent 80'), (80, 'Parent 80 (duplicate)'), (90, 'Parent 90') ] cursor.executemany("insert into ParentTable values (:1, :2)", data, batcherrors=True) for error in cursor.getbatcherrors(): print("Error", error.message, "at row offset", error.offset) ''' Error ORA-00001: unique constraint (PYTHONDEMO.PARENTTABLE_PK) violated at row offset 2 Error ORA-00001: unique constraint (PYTHONDEMO.PARENTTABLE_PK) violated at row offset 4
更多
一些类型、精度等需要的话，更加详细的可看：
调用function及存储过程:
1.3 数据库连接池
由于维护数据库连接是一笔很大的开销，当线程需要频繁地创建连接、关闭连接时，使用数据库连接池是一个很不错的选择。而且数据库连接池还可以统一控制同一时间最大连接数。
cx_Oracle.SessionPool()提供了创建数据库连接池的方法。
# 创建数据库连接池 pool = cx_Oracle.SessionPool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb1", min=2, max=5, increment=1, encoding="UTF-8") # 请求池中的连接 connection = pool.acquire() # 使用连接 cursor = connection.cursor() for result in cursor.execute("select * from mytab"): print(result) # 将连接返回池中 pool.release(connection) # 关闭数据库连接池 pool.close()
2、代码
#!/usr/bin/pyhon #coding=utf-8 import cx_Oracle import datetime class Oracle_util: url = 'ip:端口/服务名' user_name = '账号名' psw = '密码' # 构造函数 def __init__(self): self.db = cx_Oracle.connect(user_name,psw,url) self.cursor = self.db.cursor() # close方法 def close(self): self.cursor.close() self.db.close() # 查询数据 def query(self,name): self.cursor.execute('select * from my_table where name=:name',name=name) # 获取全部查询结果 def get_all(self): return self.cursor.fetchall() # 逐一获取查询结果,返回的是元组 def get_one(self): return self.cursor.fetchone() # 数据插入 def insert(self,rows): if 'id' not in rows.keys() or 'name' not in rows.keys() or 'time' not in rows.keys(): print('数据插入错误：传入数据格式错误。') return cur = self.db.cursor() cur.execute('insert into my_table(id,name,time) values (:id,:name,:time',rows) cur.close() self.db.commit() # 更新语句需要 # 数据更新 def update(self,rows): if 'id' not in rows.keys() or 'name' not in rows.keys(): print('数据更新错误：传入数据格式错误。') return cur = self.db.cursor() cur.execute('update my_table set name=:name where id=:id',rows) cur.close() self.db.commit() # 更新语句需要 if __name__=='__main__': Oracle = Oracle_util() Oracle.query() c = 0 while(1): rs = Oracle.get_one() c = c+1 print(c) if(rs==None): break Oracle.insert(c,rs[1],datetime.datetime.now().strftime('%Y%m%d')) Oracle.update(2,'事实上') Oracle.close()