2)查询数据…查
① fetchone():一次获取一条记录
import cx_Oracle # 注意:一定要加下面这两行代码,负责会中文乱码; import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() cursor.execute('select count(*) from emp1') aa = cursor.fetchone() print(aa) cursor.execute('select ename,deptno,sal from emp1') for i in range(aa[0]): a,b,c = cursor.fetchone() d = "我的名字叫{},所在部门是{},工资是{}美元".format(a,b,c) display(d) db.close()
截取部分结果如下:
② fetchall():一次获取所有记录
import cx_Oracle # 注意:一定要加下面这两行代码,负责会中文乱码; import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() cursor.execute('select ename,deptno,sal from emp1') aa = cursor.fetchall() # print(aa) for a,b,c in aa: d = "我的名字叫{},所在部门是{},工资是{}美元".format(a,b,c) display(d) db.close()
截取部分结果如下:
③ 使用pandas中的read_sql()方法,将提取到的数据直接转化为DataFrame进行操作
import cx_Oracle import pandas as pd import os os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' db = cx_Oracle.connect("scott","a123456","192.168.2.1:1521/orcl") cursor = db.cursor() df1 = pd.read_sql("select * from emp where deptno=20",db) display(df1) df2 = pd.read_sql("select * from emp where deptno=30",db) display(df2)
结果如下:
注意:由于其他操作和python操作MySQL类似,这里就不再赘述,你们可以自行下去尝试。