关于我们

质量为本、客户为根、勇于拼搏、务实创新

< 返回新闻公共列表

云南大王-Python操作Oracle数据库:cx_Oracle

发布时间:2020-04-13 00:00:00
  在前面的博客中已经介绍过如何使用Python来操作MySQL数据库,最近需要将一批数据从csv文件中迁移到Oracle数据库中,也打算用Python来实现,趁着这个机会,也写一篇博客学习总结一些如何使用Python来操作Oracle数据库。   1 安装与导入¶   Python操作Oracle数据库多用cx_Oracle这个第三方扩展,总体而言,cx_Oracle的使用方式与Python操作MySQL数据库的pymysql库还是很相似的,如果还没有安装,可以通过下面的命令进行安装: $ pip install -i https://pypi.douban.com/simple cx_oracle 使用前导入: In [1]: import cx_Oracle   千万注意,包名称cx_Oracle中,字母“O”是大写的,写成小写将会导入失败。 这里再附带多说一点,我在安装好cx_Oracle第一次使用时,出现这个异常:DatabaseError: DPI-1047,可以按照官方的思路解决:https://oracle.github.io/odpi/doc/installation.html#linux 2 创建连接¶ cx_Oracle提供了两种方式连接Oracle数据库,分别是创建独立的单一连接以及创建连接池。 2.1 单一连接¶ 创建单一连接主要是通过cx_Oracle模块中提供的connect()方法实现,虽然也可以直接通过Connection()类实现,但是不推荐。connect()方法参数有很多,说说其中最常用的四个: user:用户名 password:密码 dsn:数据库地址和服务名 encoding:编码,合适的编码可以避免出现乱码 这里要重点说一下dsn,dsn是data source name的缩写,用于描述数据源的详细地址,一般由数据库所在主机地址、端口和服务名组成。在默认情况下,Oracle数据库对应1521端口,在这种情况下,dsn中可以省略端口: In [3]: connection = cx_Oracle.connect("username", "password", "192.168.1.2/helowin", encoding="UTF-8")   其中,username是用户名,password是密码,192.168.1.2是数据库所在主机IP,helowin是服务名。 在一般情况下,可以这么写: In [10]: connection = cx_Oracle.connect("username", "password", "192.168.1.2:1521/helowin", encoding="UTF-8")   有时候,我们需要以管理员身份登录数据库,这时候,直接连接时不行的,将会跑出异常:DatabaseError: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER,这时候可以传递参数mode=cx_Oracle.SYSDBA。 In [7]: connection = cx_Oracle.connect("sys", "psdpassword", "192.168.1.2:1521/helowin",mode=cx_Oracle.SYSDBA, encoding="UTF-8")   当确定不在使用连接时,可以使用connection.close()关闭连接(这是个好习惯)。 In [8]: connection.close()   2.2 连接池¶ cx_Oracle中提供SessionPool()创建连接池,连接池一般是在应用程序初始化时创建。相比通过connect()方法创建单个数据库连接,使用SessionPool()创建连接池时,需要额外指定最少连接数(min)和最大连接数(max),连接池创建时会创建有min个数据库连接,当连接不够用时会继续新增连接,当连接未被使用时连接池将会自动减少连接的数量。在创建好连接池后,通过调用acquire()方法可以获取一个数据库连接,连接使用完毕之后,最好使用SessionPool.release(connection)或Connection.close()将连接放回连接池。 In [12]: # 创建连接池 pool = cx_Oracle.SessionPool("username", "password", "192.168.1.2:1521/helowin", min=2, max=5, increment=1, encoding="UTF-8") # 从连接池中获取一个连接 connection = pool.acquire() # 使用连接进行查询 cursor = connection.cursor() for result in cursor.execute("select * from scott.students"): print(result) # 将连接放回连接池 pool.release(connection) # 关闭连接池 pool.close()   (1, '张三', 20) (2, '李四', 30)   如果是在多线程下同时使用连接,那么在创建连接池时应该传递一个threaded参数,并将值设置为True: In [2]: # 创建连接池 pool = cx_Oracle.SessionPool("username", "password", "192.168.1.2:1521/helowin", min=2, max=5, increment=1, threaded=True, encoding="UTF-8") In [3]: pool.close()   3 游标¶   有了数据库连接之后,可以通过连接来获取游标: In [64]: cur = connection.cursor()   通过游标,可以执行SQL语句,实现与数据库的交互,但是记住,游标使用完之后记得关闭: In [65]: cur.close()   游标对象中定义有Cursor.excute()方法和Cursor.executemany()两个方法用于执行SQL语句,前者一次只能执行一条SQL语句,后者一次可执行多条SQL。当有类似的大量SQL语句需要执行时,使用Cursor.executemany()而不是多次执行Cursor.excute()可以极大提升性能。 另外,所有cx_Oracle执行的语句都含有分号“;”或斜杠“/”: In [66]: connection = cx_Oracle.connect("username", "password", "192.168.1.2/helowin", encoding="UTF-8") cur = connection.cursor() In [68]: cur.execute("select * from SCOTT.STUDENTS;") # 含有分号,抛出异常   --------------------------------------------------------------------------- DatabaseError Traceback (most recent call last) in ----> 1cur.execute("select * from SCOTT.STUDENTS;") # 含有分号,抛出异常 DatabaseError: ORA-00911: invalid character   4 执行SQL¶ 4.1 SQL语句拼接¶ (1)使用Python原生占位符拼接 在很多应用场景中,我们查询所用的SQL语句并不是固定的,而是根据当时环境灵活的对SQL进行拼接。最简单的方式就是直接使用Python原生提供的占位符进行拼接,不过要注意如果变量是字符串时,引号不能少。 In [123]: cur.execute("insert into SCOTT.STUDENTS (id, name, age) values ({student_id}, '{student_name}', {student_age})".format( student_id=4, student_name='李六', student_age=15 )) connection.commit() In [124]: student_id = 4 result = cur.execute("select * from SCOTT.STUDENTS where id={}".format(student_id)) In [125]: result.fetchone() Out[125]: (4, '李六', 15) In [118]: student_name = "张三" result = cur.execute("select * from SCOTT.STUDENTS where name='{}'".format(student_name)) In [119]: result.fetchone() Out[119]: (1, '张三', 20)   (2)通过变量名拼接 使用这种拼接方式时,字符串中的名称与真实变量名必须一一对应。 所有变量可以统一存储在一个字典中: In [127]: student = {'student_id':5, 'student_name':'陈七', 'student_age': 25} # 将所有变量存储到一个字典中 cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)',student) connection.commit()   也可以逐一赋值: In [128]: cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)', student_id=6,student_name='毛八',student_age=60) connection.commit()   (3)通过参数位置拼接 通过参数位置进行拼接时,所有变量可以统一存储在一个list中,list中的变量的顺序必须与字符串中定义的顺序保持一致。 In [129]: cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:student_id, :student_name, :student_age)', [7,'魏九',30]) connection.commit()   这时候,在字符串中也可以不显式的出现参数名,而是以数字来代替出现位置: In [130]: cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)', [8,'吴十',90]) connection.commit()   4.2 执行语句¶ cx_Oracle的游标中定义了execute()和executemany()两个方法用于执行SQL语句,区别在于execute()一次只能执行一条SQL,而executemany()一次能执行多条SQL。在大量结构一样,参数不同的语句需要执行时,使用executemany()而不是多次调用execute()执行可以大大提高代码性能。 (1)execute() 对于execute()方法,其实在上面代码实例中以及多次使用,大致形式如下: In [131]: cur.execute('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)', [9,'萧十一',32]) connection.commit()   (2)executemany() In [132]: students = [ [10,'萧十一',32], [11,'何十二',40], [12,'穆十三',35] ] cur.executemany('insert into SCOTT.STUDENTS (id, name, age) values (:1, :2, :3)', students) connection.commit()   cx_Oracle执行SQL时需要注意,若是执行查询,可通过游标获取查询结果,具体如何获取请继续看下文;若是执行insert或update操作,需要在执行后继续一步connection.commit()操作。 5 获取查询结果¶ 当使用游标进行查询后,可以直接迭代取出查询结果 In [79]: result = cur.execute("select * from SCOTT.STUDENTS") In [80]: for row in result: print(row)   (1, '张三', 20) (2, '李四', 30) (3, '王五', 40)   注意,这里的游标查询结果对象result虽然不是生成器,但是可以当做生成器来用,每一次使用next()方法时,可以获取一条记录。当然,也与生成器一样,查询结果只能迭代遍历一次,再次使用迭代不会有任何输出: In [82]: result = cur.execute("select * from SCOTT.STUDENTS") In [83]: next(result) Out[83]: (1, '张三', 20) In [84]: next(result) Out[84]: (2, '李四', 30) In [85]: next(result) Out[85]: (3, '王五', 40) In [86]: for row in result: # 没有任何输出结果 print(row)   其实,通过循环来获取查询结果时,每一次调用next()方法,result对象都会对数据库发起一次请求,获取一条查询记录,如果查询记录数量比较大时,性能会比较低,这时候,可以通过设置cur.arraysize参数改善性能。cur.arraysize参数配置的是每次请求获取的数据包大小,默认为100,当设置为更大值时,一次请求就可以获取更多的记录,减少客户端与数据库服务器端网络往返次数,从而提高性能,当然缺点就是消耗的内存会更大。 In [91]: cur.arraysize = 500 for row in cur.execute("select * from SCOTT.STUDENTS"): print(row)   (1, '张三', 20) (2, '李四', 30) (3, '王五', 40)   除了在循环中直接遍历外,还可以通过fetchone()、fetchmany()、fetchall()三个方法取出查询结果。 fetchone() fetchone()每次只取出一条记录,功能效果与直接对result使用next()方法一样。 In [93]: cur = connection.cursor() result = cur.execute("select * from SCOTT.STUDENTS") In [95]: result.fetchone() Out[95]: (1, '张三', 20) In [96]: result.fetchone() Out[96]: (2, '李四', 30) In [97]: result.fetchone() Out[97]: (3, '王五', 40)   fetchmany() fetchmany()可以一次取出指定数量的记录,如果不指定数量,表示一次性去除所有记录。 In [102]: cur = connection.cursor() result = cur.execute("select * from SCOTT.STUDENTS") In [103]: result.fetchmany(2) Out[103]: [(1, '张三', 20), (2, '李四', 30)] In [104]: result.fetchmany(2) Out[104]: [(3, '王五', 40)] In [105]: result.fetchmany(2) # 若所有记录都取出来了,返回空列表 Out[105]: []   fetchall() fetchall()一次性去除所有结果。 In [106]: cur = connection.cursor() result = cur.execute("select * from SCOTT.STUDENTS") In [107]: result.fetchall() Out[107]: [(1, '张三', 20), (2, '李四', 30), (3, '王五', 40)]

相关阅读

centos7系统中忘记了root管理员账号密码的解决方式公司管理必须的20条军规[参考]云南昆明天猫旗舰店如何开_怎么开_要什么条件云南大王-通俗理解spring源码(三)—— 获取xml的验证模式 云南大王-用户登录 云南大王-【Golang进阶】指针的详细讲解 云南大王-Java 单线程代码ThreadLocal串值问题 云南大王-Java 实例级别的锁和类级别的锁 云南大王-工作流引擎会签,加签,主持人,组长模式专题讲解 云南大王-Android连载5云南大王-NTP对时器(NTP对时服务器)重要性!京准电子科技 云南大王-关于redis单线程的分析 云南大王-CVE云南大王-PHP SESSION反序列化本地样例分析 云南大王-这不就是多线程ThreadPoolExecutor和阻塞队列吗 云南大王-Tomcat AJP 文件包含漏洞(CVE云南大王-讲真,这两款idea插件,能治愈你英语不好的病 云南大王-消息中间件ActiveMQ、RabbitMQ、RocketMQ、ZeroMQ、Kafka如何选型? 云南大王-JVM系列十三(类加载器). 云南大王-Win10安装MySQL8压缩包版 云南大王-初始WebApi(2) 云南大王-初识人工智能(二):机器学习(一):sklearn特征抽取 云南大王-Popup中ListBox的SelectChange事件关闭弹出窗体后主窗体点击无效BUG 云南大王-基础知识记录 云南大王-FastDFS搭建图片服务器 云南大王-git/sourcetree解决本地仓库历史合并到线上仓的历史数据合并问题_refusing to merge unrelated histories 云南大王-js判断字符是否在数组中【转】 云南大王-Python 云南大王-面向对象之多线程(可捎带电梯调度) 云南大王-Python练习题2.5求奇数分之一序列前N项和(存在问题) 云南大王-React 中的前端路由 react云南大王-VSCODE 远程开发树莓派 云南大王-React新闻网站云南大王-vs .net CS0006 C# 未能找到元数据文件 .dll 云南大王-Vue.js 技术揭秘 云南大王-流程控制语句云南大王-Python学习笔记:Python的时间操作(time,datetime,timedelta,calendar) 云南大王-流程控制语句云南大王-golang Gin framework with websocket 云南大王-多重判断if..else嵌套语句 云南大王-用户登录 云南大王-流程控制语句云南大王-密码类 云南大王-Unity2018发布WebGL注意事项总结 云南大王-web系统安全运营之基础云南大王- 流程控制语句云南大王-中型WPF客户端开发项目总结(3.3.4) 云南大王-流程控制语句云南大王-流程控制语句云南大王-流程控制语句云南大王-中型WPF客户端开发项目总结(4) 云南大王-流程控制语句云南大王-ASP.NET Core笔记(4) 云南大王-C# 基础知识系列云南大王-让 .NET 轻松构建中间件模式代码(二) 云南大王-基于 HTML5 WebGL 的 水泥工厂可视化系统 云南大王-.NET Core 3 WPF MVVM框架 Prism系列之导航系统 云南大王-《JavaScript异步编程》精读笔记 云南大王-合理使用CSS框架,加速UI设计进程 云南大王-CLSID 为 {000209FF云南大王-从零基础转行到前端大牛,需要经过哪几个阶段? 云南大王-一个简单的例子看明白 async await Task 云南大王-【目前】宇宙第一IDE Visual Studio 合并压缩css、js扩展组件云南大王-写一个通用的List集合导出excel的通用方法 云南大王-Bootstrap4 按钮组+徽章(Badges)+进度条+分页+列表组 云南大王-Web前端工程师需要学些什么? 云南大王-react嵌套路由 云南大王-【java框架】Struts2(2) 云南大王-javaSE笔记云南大王-.net core 集成 sentry 进行异常报警 云南大王-Java の 四种引用 云南大王-JVM 虚拟机&&类加载(一) 云南大王-使用Fastjson实现JSON与JavaBean之间互相转换 云南大王-Python操作Oracle数据库:cx_Oracle 云南大王-为什么要用内插字符串代替string.format 云南大王-作为字节跳动的面试官,有些话我不得不说! 云南大王-微信公众号自定义菜单与启用服务器配置冲突(图文消息、链接及文本回复) 云南大王-C#队列学习笔记:RabbitMQ延迟队列 云南大王-Disruptor 基础篇 云南大王-C#获取设备(Audio和Video)名称 简单整理 云南大王-基于注解的IOC配置 云南大王-C#调用EnumDevice获取设备信息 云南大王-Jenkins基础系统之更换镜像源 云南大王-Jenkins基础系统之完整的.net项目编译 云南大王-Scala学习系列(二)——环境安装配置 云南大王-WinForm中DataGridView复制选中单元格内容解决方案 云南大王-关键词匹配优化(第0篇)—— 问题和思路 云南大王-ASP.NET CORE WEBAPI文件下载 云南大王-GC垃圾回收器 云南大王-多线程之旅(Task 任务) 云南大王-当模板方法遇到了委托函数,你的代码又可以精简了 云南大王-基于.NetCore3.1搭建项目系列 —— 使用Swagger导出文档 (补充篇) 云南大王-关键词匹配优化(第1篇)—— 测试计算过程 云南大王-原理解密 → Spring AOP 实现动态数据源(读写分离),底层原理是什么 云南大王-Navicat 密码加密算法 云南大王-【WPF学习】第六十六章 支持可视化状态 云南大王-composer安装 windows 云南大王-ASP.NET Core中的Controller 云南大王-HttpClient来自官方的JSON扩展方法 云南大王-Python3标准库:http.cookies HTTP cookie
/template/Home/Zkeys/PC/Static