pandas连oracle,Pandas cx_Oracle使用方法

正确安装好cx_oracle之后，要使用它来连接到oracle数据库进行操作，具体应该分3步走：

第一步：导入cx_Oracle，建立连接

>>>

import cx_Oracle #导入模块

>>>

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

特别注意：这里的'localhost:1521/XE'可以是你oracle net manager配置的链接名，如oracl，我就是在这里耽搁了半天

>>>

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

>>>

dsn_tns = cx_Oracle.makedsn('localhost',1521,'XE')

>>>

print dsn_tns

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

(CONNECT_DATA=(SID=XE)))

>>>

db2 = cx_Oracle.connect('hr',dsn_tns)

>>> print db.version

10.2.0.1.0

>>> versioning = db.version.split('.')

>>> print versioning

['10','2','0','1','0']

>>> if versioning[0]=='10':

... print "Running 10g"

... elif versioning[0]=='9':

... print "Running 9i"

...

Running 10g

>>> print db.dsn

localhost:1521/XE

第二步：建立Cursor光标

>>>cursor = db.cursor() 建立一个cursor

之后，我们可以调用这个cursor.execute(‘sql‘) 来执行sql语句。比如：

>>>cursor.execute(‘select * from tabs’)

执行完毕以后，可以调用cursor.fetchall()一次取完所有结果，或者cursor.fetchone()一次取一行结果

>>>row=cursor.fetchall()

>>>for x in row:

For y in x:

Print y,

Print

这样就可以按照表格的形式打印取得的结果了！

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

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")

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。