cx_Oracle使⽤⽅法
cx_Oracle使⽤⽅法
正确安装好cx_oracle之后，要使⽤它来连接到oracle数据库进⾏操作，具体应该分3步⾛：
第⼀步：导⼊cx_Oracle ，建⽴连接
>>> import cx_Oracle      # 导⼊模块
>>> db = t('hr', 'hrpwd', 'localhost:1521/XE') 建⽴连接，3个参数分开写
>>> db1 = t('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 = t('hr', 'hrpwd', 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
之后，我们可以调⽤这个ute（‘SQL‘）来执⾏SQL语句。⽐如：
>>&ute(‘select * from tabs’)
执⾏完毕以后，可以调⽤cursor.fetchall()⼀次取完所有结果，或者cursor.fetchone()⼀次取⼀⾏结果
>>>row=cursor.fetchall()
>>>for row in rows:
For v in row:
Print v,
Print
这样就可以按照表格的形式打印取得的结果了！
在从oracle取出数据的时候，考虑到它的数据类型了吗？下⾯就是数据类型的对应表
带参数的查询：
>>> named_params = {'dept_id':50, 'sal':1000}
>>> query1 = ute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', named_params)
>>> query2 = ute('SELECT * FROM employees WHERE department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000)
这种是名字参数，还可以按位置参数：
r1 = ute('SELECT * FROM locations WHERE country_id=:1 AND city=:2', ('US', 'Seattle'))
注意：
当只有⼀次参数的时候，也要把它写成元组的形式，⽐如
千万要注意，login_id后⾯还带有⼀个逗号！
Cursor. Prepare的⽤法，
这个⽅法就是在prepare之后，你再去execute的时候，就不⽤写上sql语句参数了
>>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')
>>> r = ute(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_utemany 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
>>> ute(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)") >>> utemany(None, M)
>>> dbmit()
>>> r = ute("SELECT COUNT(*) FROM python_modules")
oracle游标的使用
>>> print cursor.fetchone()
(76,)
>>> ute("DROP TABLE python_modules PURGE")

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。