Pandas cx_Oracle使用方法
来源:互联网 发布:linux网络配置 编辑:程序博客网 时间:2024/06/05 06:57
正确安装好cx_oracle之后,要使用它来连接到oracle数据库进行操作,具体应该分3步走:
第一步:导入cx_Oracle,建立连接
>>> import cx_Oracle #导入模块
>>> db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE') 建立连接,3个参数分开写
>>> 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', 'hrpwd', dsn_tns)
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
(CONNECT_DATA=(SID=XE)))
>>> db2 = cx_Oracle.connect('hr', 'hrpwd', dsn_tns)
>>> <strong style="line-height: normal;">print db.version</strong><br style="line-height: normal;" />10.2.0.1.0<br style="line-height: normal;" />>>> <strong style="line-height: normal;">versioning = db.version.split('.')</strong><br style="line-height: normal;" />>>> <strong style="line-height: normal;">print versioning</strong><br style="line-height: normal;" />['10', '2', '0', '1', '0']<br style="line-height: normal;" />>>> <strong style="line-height: normal;">if versioning[0]=='10':</strong><br style="line-height: normal;" />... <strong style="line-height: normal;">print "Running 10g"</strong><br style="line-height: normal;" />... <strong style="line-height: normal;">elif versioning[0]=='9':</strong><br style="line-height: normal;" />... <strong style="line-height: normal;">print "Running 9i"</strong><br style="line-height: normal;" />...<br style="line-height: normal;" />Running 10g<br style="line-height: normal;" />>>> <strong style="line-height: normal;">print db.dsn</strong><br style="line-height: normal;" />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
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)
>>> 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'))
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 = """<br style="line-height: normal;" />CREATE TABLE python_modules (<br style="line-height: normal;" /> module_name VARCHAR2(50) NOT NULL,<br style="line-height: normal;" /> file_path VARCHAR2(300) NOT NULL<br style="line-height: normal;" />)<br style="line-height: normal;" />"""<br style="line-height: normal;" />>>> from sys import modules<br style="line-height: normal;" />>>> cursor.execute(create_table)<br style="line-height: normal;" />>>> M = []<br style="line-height: normal;" />>>> for m_name, m_info in modules.items():<br style="line-height: normal;" />... try:<br style="line-height: normal;" />... M.append((m_name, m_info.__file__))<br style="line-height: normal;" />... except AttributeError:<br style="line-height: normal;" />... pass<br style="line-height: normal;" />...<br style="line-height: normal;" />>>> len(M)<br style="line-height: normal;" />76<br style="line-height: normal;" />>>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")<br style="line-height: normal;" />>>> cursor.executemany(None, M)<br style="line-height: normal;" />>>> db.commit()<br style="line-height: normal;" />>>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")<br style="line-height: normal;" />>>> print cursor.fetchone()<br style="line-height: normal;" />(76,)<br style="line-height: normal;" />>>> cursor.execute("DROP TABLE python_modules PURGE")
阅读全文
0 0
- Pandas cx_Oracle使用方法
- cx_Oracle使用方法
- cx_Oracle使用方法
- pandas基本使用方法示例
- pandas get_dummies的使用方法
- cx_Oracle
- pandas中apply的使用方法
- pandas.cut与pandas.qcut使用方法与区别
- Python中pandas模块的使用方法
- python数据分析包pandas的使用方法
- pandas
- pandas
- Pandas
- pandas
- pandas
- pandas
- pandas
- Pandas
- Web前端案例_正则表达式案例
- :app:compileDebugJavaWithJavac
- 在Eclipse下安装C++插件CDT
- 链表与表——随心所欲地进出任何队列
- SpringMVC中@RequestBody的作用
- Pandas cx_Oracle使用方法
- TCP和UDP的区别
- WPF Bind 绑定
- mysql数据库导出备份及导入恢复
- LRU缓存算法
- 大数据乘法算法
- 优雅设计封装基于Okhttp3的网络框架(二):多线程下载功能原理设计 及 简单实现
- BugFree 3.0.4添加多个admin权限
- java性能优化 –gc日志收集与分析