BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]Win10 Python3.6 连接Oracle12c的简单步骤

2017-12-05

[原创]Win10 Python3.6 连接Oracle12c的简单步骤

开始研究Python学习,首先配置好环境,目前使用windows10.

前期准备

安装的Python版本是3.6,至于Python3的下载安装,请自行官网下载,而在windows下都比较傻瓜,点击Next…

C:\Users\honglin>python -V
Python 3.6.2rc1

C:\Users\honglin>

Python的开发工具有很多,如Notepad++, WingIDE, PyCharm, eclipse etc. 从轻到重…

比较常用的是 Anaconda + PyCharm 用anaconda集成的ipython做工作台,做一些分析和小段程序调试的工作; 用Pycharm写相应脚本和程序包的开发。 更重要的是这两个工具都是跨平台的,也都有免费版本。

Anaconda 5.0.1 下载

Anaconda 5.0.1 For Windows Installer

至于PyCharM的安装使用,可以参考如下文档:

PyCharm安装及使用

Anaconda 5.0.1 For Windows Installer

准备Oracle链接相关产品

下载如下: Oracle12c Client安装包和 cx_Oracle 插件

Oracle Database 12c Release 2 Client (12.2.0.1.0) for Microsoft Windows (x64)

  • Basic默认安装
  • 设置把Client的lib的路径加入到PATH

    Install Oracle Client

    Using cx_Oracle requires Oracle Client libraries to be installed. The libraries provide the necessary network connectivity allowing applications to access an Oracle Database instance. They also provide basic and advanced connection management and data features to cx_Oracle. cx_Oracle uses the shared library loading mechanism available on each supported platform to load the Oracle Client library at runtime. Oracle Client versions 12.2, 12.1 and 11.2 are supported.

    The simplest Oracle Client is the free Oracle Instant Client. Only the “Basic” or “Basic Light” package is required. Oracle Client libraries are also available in any Oracle Database installation or full Oracle Client installation.

    Make sure your library loading path, such as PATH on Windows, or LD_LIBRARY_PATH on Linux, is set to the location of the Oracle Client libraries. On macOS the libraries should be in ~/lib or /usr/local/lib.

    On Windows, Microsoft Windows Redistributables matching the version of the Oracle client libraries need to be installed.

cx_Oracle 5.3

  • cx_Oracle-5.3-12c.win-amd64-py3.6-2.exe (因为要连接DB12c的数据库,选择12c的版本)

cx_Oracle的安装也可以使用PyCharm的Pip命令来安装,一般会安装成为最新版本,推荐是有这个安装

PyCharm_pip_cx_Oracle

安装都是点击下一步…

启动本地虚拟机数据库和监听

数据库

SQL> set pagesize 1000
SQL> set linesize 1000
SQL> select * from v$version;

BANNER										     CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production		  0
PL/SQL Release 12.2.0.1.0 - Production							  0
CORE	12.2.0.1.0	Production								  0
TNS for Linux: Version 12.2.0.1.0 - Production						  0
NLSRTL Version 12.2.0.1.0 - Production							  0

SQL> 

监听程序

[oracle@databasevm ~]$ lsnrctl status
~~省略~~

Service "PRODCDB" has 1 instance(s).
  Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@databasevm ~]$ 

连接测试(虚拟机上):

[oracle@databasevm ~]$ sqlplus sys/oracle@127.0.0.1:1521/orclpdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 11 23:55:01 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB1
SQL> show pdbs;

	CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 ORCLPDB1			  READ WRITE NO
SQL> 

另外通过Develop工具上

Develop_Conn

注意关于win10链接虚拟机NAT的设置,通过端口跳转来进行处理,我的环境如下设置,具体解释请自行参考谷歌等

NAT_Setting

通过以上安装和测试,直接用MS-DOS方式如下,可以连接虚拟机的DB了

C:\Users\honglin>python
Python 3.6.2rc1 (heads/3.6:268e1fb, Jun 17 2017, 19:01:44) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> db = cx_Oracle.connect("system/oracle@localhost:1521/orclpdb1")
>>> print (db.version)
12.2.0.1.0
>>> exit();

C:\Users\honglin>

进一步测试

ora12c_connect.py

import cx_Oracle
import sys
import os
import configparser


def connect_Oracle( server, port, user, pswd, service ):

	try:

		conn = cx_Oracle.connect( user, pswd, server + ':' + port + '/' + service )
		cur = conn.cursor()

		return ( cur )

	except ( cx_Oracle.DatabaseError ) as ex:
		print ( sys.exc_info()[1] )
		raise ex


def exec_Oracle_SQL( cur, sql ):

	try:

		cur.execute( sql )
		rows = cur.fetchall()

		return ( rows )

	except ( cx_Oracle.DatabaseError ) as ex:
		print ( sys.exc_info()[1] )
		raise ex


if __name__ == '__main__':
	cur = connect_Oracle( '127.0.0.1', '1521', 'system', 'oracle', 'orclpdb1' )
	rows = exec_Oracle_SQL( cur, 'select username, user_id from user_users' )
	for row in rows:
		print ( row[0] + ', ', end = "" )

Test_conn

参考资料

Welcome to cx_Oracle’s documentation!

++++++++++++++++ EOF LinHong ++++++++++++++++


Similar Posts

Comments