Chinaunix首页 | 论坛 | 博客
  • 博客访问: 871440
  • 博文数量: 275
  • 博客积分: 3904
  • 博客等级: 中校
  • 技术积分: 4605
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-17 21:10
文章分类

全部博文(275)

文章存档

2014年(9)

2013年(124)

2012年(142)

分类: 系统运维

2013-12-30 13:17:53

Python 3.x 连接Oracle 数据 【1】首先下载驱动:(cx_Oracle)
要注意一下版本,根据你的情况加以选择。

2】安装:

执行exe安装程序就可以了,它会copy一个cx_Oracle.pyd{PYTHON_HOME}\Lib\site-packages目录下

3】执行一段测试程序

       import cx_Oracle

con = cx_Oracle.connect( "xjtu_test""37343734","xjtu.world")
cursor = con.cursor()
cursor.close()
con.close()

       里边connect中的3个参数从左到右分别是:user, pass, TNS
那个TNS可以用Oracle客户端工具中的NetConfiguration Assistant来配置。

也可以:

cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE') 建立连接,3个参数分开写

这里的 'localhost:1521/XE'可以是你oracle net manager配置的链接名,如oracl

cx_Oracle.connect('hr/hrpwd@localhost:1521/XE') 建立连接,3个参数连写

 

4】具体的cx_Oracle API可以参考:
html/cx_Oracle.html

 

cx_Oracle.connect时报告RuntimeError: Unable to acquire Oracle environment handle:

将oracle instantclient目录下两个文件copy到python安装目录或将其加到环境变量(注意:会对PLSQL Developer造成影响)

oraociei10.dll

orasqlplusic10.dll

 

 

在从oracle取出数据的时候,考虑到它的数据类型了吗?下面就是数据类型的对应表

http://www.blogjava.net/pts/archive/2010/11/02/336835.html

Datatypes

During the fetch stage, basic Oracle data types get mapped into their Python equivalents. cx_Oracle maintains a separate set of data types that helps in this transition. The Oracle - cx_Oracle - Python mappings are:

   

Oracle

cx_Oracle

Python

VARCHAR2
NVARCHAR2
LONG

cx_Oracle.STRING

str

CHAR

cx_Oracle.FIXED_CHAR

NUMBER

cx_Oracle.NUMBER

int

FLOAT

float

DATE

cx_Oracle.DATETIME

datetime.datetime

TIMESTAMP

cx_Oracle.TIMESTAMP

CLOB

cx_Oracle.CLOB

cx_Oracle.LOB

BLOB

cx_Oracle.BLOB

带参数的查询:

>>> named_params = {'dept_id':50, 'sal':1000}
>>> query1 = cursor.execute('SELECT * FROM employees 
WHERE department_id=:dept_id AND salary>:sal', named_params)
>>> query2 = cursor.execute('SELECT * FROM employees 
WHERE department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000)

这种是名字参数,还可以按位置参数:

r1 = cursor.execute('SELECT * FROM locations 
WHERE country_id=:1 AND city=:2', ('US', 'Seattle'))

注意:

当只有一次参数的时候,也要把它写成元组的形式,比如

Cursor.execute(‘select name from user where id=:1’,(login_Id,))

千万要注意,login_id后面还带有一个逗号,如果没有逗号,他其实就是一个数据对象,但是当他后面有个逗号的时候,他就变成了元组的一个数据项。

Cursor. Prepare的用法,

这个方法就是在prepare之后,你再去execute的时候,就不用写上sql语句参数了

>>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')

>>> r = cursor.execute(None, {'min':1000}) #注意,第一个参数是None,

一次执行多条sql语句

Large insert operations don't require many separate inserts because Python fully supports inserting many rows at once with the cx_Oracle.Cursor.executemany method. Limiting the number of execute operations improves program performance a lot and should be the first thing to think about when writing applications heavy on INSERTs.

Let's create a table for a Python module list, this time directly from Python. You will drop it later.

>>> create_table = """
CREATE TABLE python_modules (
module_name VARCHAR2(50) NOT NULL,
file_path VARCHAR2(300) NOT NULL
)
"""
>>> from sys import modules
>>> cursor.execute(create_table)
>>> M = []
>>> for m_name, m_info in modules.items():
...   try:
...     M.append((m_name, m_info.__file__))
...   except AttributeError:
...     pass
...
>>> len(M)
76
>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")
>>> cursor.executemany(None, M)
>>> db.commit()
>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")
>>> print cursor.fetchone()
(76,)
>>> cursor.execute("DROP TABLE python_modules PURGE")

 

查看oracle版本命令

1  查看oracle的版本信息

1)用客户端连接到数据库,执行select* from v$instance
            查看version

2select * fromproduct_component_version

3)或查询V$VERSION查看组件级信息

阅读(3037) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~