|
目录:
Python连接Oracle实验
下载对应模块(cx_Oracle)
1.到python根目录,根目录找到scripts文件夹
2.进入scripts文件夹,在资源管理器中,光标移动到路径并双击,敲cmd然后回车
3.安装,在cmd中输入以下命令并回车:
pip install cx_Oracle
如果提示版本不是最新的不用管(变相的告诉我们已经安装成功)
查看Oracle服务器端口
其实默认的都是127.0.0.1/1521,可以省略这一步骤,如果不放心的话,可以试着操作一下:
1.电脑上按win+r 写入cmd运行
2.在cmd中运行如下命令:
lsnrctl status
3.查看cmd中显示的消息,在中间的部分你会发现会有以下字段:
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
可以看到我们的端口号就是:127.0.0.1/1521
开始实践
任务要求:
-
建立python函数，对teacher表中数据进行修改，要求SQL语句中，通过参数传递值，构建修改值和查询条件，进行数据修改。 -
建立python函数，删除course表中的数据，要求SQL语句中，通过参数传递值，构建查询条件，进行数据删除。 -
建立python函数，查询对teacher和course表进行数据更新操作前后的结果。
使用plsql图形界面工具,其中创建teacher表等一系列数据的sql命令如下:
create table teacherCard
(
tcid int primary key,
tcdesc varchar2(20)
);
create table teacher
(
tid int primary key,
tname varchar2(20),
tcid int references teacherCard(tcid)
);
create table course
(
cid int primary key,
cname varchar2(20),
tid int references teacher(tid)
);
insert into teacherCard values(1,'tzdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
commit;
在开始前首先创建好Oracle类:
class Oracle(object):
def __init__(self,userName,password,host,instance):
self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName,password,host,instance))
self.cursor = self._conn.cursor()
def queryAll(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchall()
def queryOne(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchone()
def queryBy(self,sql,nameParams={}):
if len(nameParams) > 0 :
self.cursor.execute(sql,nameParams)
else:
self.cursor.execute(sql)
return self.cursor.fetchall()
def insertBatch(self,sql,nameParams=[]):
"""batch insert much rows one time,use location parameter"""
self.cursor.prepare(sql)
self.cursor.executemany(None, nameParams)
self.commit()
def commit(self):
self._conn.commit()
def __del__(self):
if hasattr(self,'cursor'):
self.cursor.close()
if hasattr(self,'_conn'):
self._conn.close()
对表进行修改
注意:使用部分操作需要commit,否则程序会卡住不动
def update_teacher(self, newVal, condition):
sql = "UPDATE teacher SET TCID = " + newVal + "WHERE " + condition
self.cursor.execute(sql)
self.commit()
print("成功修改teacher表")
对表中数据进行删除
注意:使用部分操作需要commit,否则程序会卡住不动
def delete_course(self, condition):
sql = "DELETE course WHERE " + condition
self.cursor.execute(sql)
self.commit()
print("成功删除course表中的数据")
修改操作后的查询验证
def queryResult:
sql1 = "SELECT * FROM teacher"
sql2 = "SELECT * FROM course"
oraDb = Oracle('system','Sl123456','127.0.0.1:1521','orcl')
fields1 = oraDb.queryAll(sql1)
fields2 = oraDb.queryAll(sql2)
print(fields1)
print(fields2)
完整的python代码
import cx_Oracle
class Oracle(object):
def __init__(self,userName,password,host,instance):
self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName,password,host,instance))
self.cursor = self._conn.cursor()
def queryAll(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchall()
def queryOne(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchone()
def queryBy(self,sql,nameParams={}):
if len(nameParams) > 0 :
self.cursor.execute(sql,nameParams)
else:
self.cursor.execute(sql)
return self.cursor.fetchall()
def insertBatch(self,sql,nameParams=[]):
"""batch insert much rows one time,use location parameter"""
self.cursor.prepare(sql)
self.cursor.executemany(None, nameParams)
self.commit()
def update_teacher(self, newVal, condition):
sql = "UPDATE teacher SET TNAME = " + newVal + " WHERE " + condition
self.cursor.execute(sql)
print("成功修改teacher表")
self.commit()
def delete_course(self, condition):
sql = "DELETE course WHERE " + condition
self.cursor.execute(sql)
self.commit()
print("成功删除course表中的数据")
def commit(self):
self._conn.commit()
def __del__(self):
if hasattr(self,'cursor'):
self.cursor.close()
if hasattr(self,'_conn'):
self._conn.close()
def queryResult():
sql1 = "SELECT * FROM teacher"
sql2 = "SELECT * FROM course"
oraDb = Oracle('system','Cfy123456','127.0.0.1:1521','orcl')
fields1 = oraDb.queryAll(sql1)
fields2 = oraDb.queryAll(sql2)
print(fields1)
print(fields2)
def test1():
oraDb = Oracle('system','Cfy123456','127.0.0.1:1521','orcl')
newVal = "'cfy'"
condition = "TID = 1"
oraDb.update_teacher(newVal, condition)
def test2():
oraDb = Oracle('system','Cfy123456','127.0.0.1:1521','orcl')
condition = "CID = 1"
oraDb.delete_course(condition)
print("修改之前的表:")
queryResult()
test1()
test2()
print("修改之后的表:")
queryResult()
运行结果如下:
修改之前的表:
[(1, ‘tz’, 1), (2, ‘tw’, 2), (3, ‘tl’, 3)]
[(1, ‘java’, 1), (2, ‘html’, 1), (3, ‘sql’, 2), (4, ‘web’, 3)]
成功修改teacher表
成功删除course表中的数据
修改之后的表:
[(1, ‘cfy’, 1), (2, ‘tw’, 2), (3, ‘tl’, 3)]
[(2, ‘html’, 1), (3, ‘sql’, 2), (4, ‘web’, 3)]
实验过程中遇到的问题
在实验过程中遇到了一些比较棘手的问题,甚至有难以觉查的bug改的我甚至有点想杀人了
- 操作的字段如果是字符串,必须要加单引号,不然会报错
- sql语句中的增删改查,在用python操作的时候,有些需要commit,不然会一直卡住
深受其害 - 运行在Python的IDLE中,也会被卡住,不知道原因是什么,但是在jupyter notebook中是可以运行的
拓展
这是第二种操作模板,感觉会比较舒服些,已经实现了增删改查功能,可以直接用
import cx_Oracle
import os
import json
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
class TestOracle(object):
def __init__(self,user,pwd,ip,port,sid):
self.connect=cx_Oracle.connect(user+"/"+pwd+"@"+ip+":"+port+"/"+sid)
self.cursor=self.connect.cursor()
"""处理数据二维数组，转换为json数据返回"""
def select(self,sql):
list=[]
self.cursor.execute(sql)
result=self.cursor.fetchall()
col_name=self.cursor.description
for row in result:
dict={}
for col in range(len(col_name)):
key=col_name[col][0]
value=row[col]
dict[key]=value
list.append(dict)
js=json.dumps(list,ensure_ascii=False,indent=2,separators=(',',':'))
return js
def disconnect(self):
self.cursor.close()
self.connect.close()
def insert(self,sql,list_param):
try:
self.cursor.executemany(sql,list_param)
self.connect.commit()
print("插入ok")
except Exception as e:
print(e)
finally:
self.disconnect()
def update(self,sql):
try:
self.cursor.execute(sql)
self.connect.commit()
except Exception as e:
print(e)
finally:
self.disconnect()
def delete(self,sql):
try:
self.cursor.execute(sql)
self.connect.commit()
print("delete ok")
except Exception as e:
print(e)
finally:
self.disconnect()
def update_teacher(newVal, condition):
sql = "UPDATE teacher SET TNAME = " + newVal + " WHERE " + condition
test_oracle = TestOracle('system','Cfy123456','127.0.0.1','1521','orcl')
test_oracle.update(sql)
print("成功修改teacher表")
def delete_course(condition):
sql = "DELETE course WHERE " + condition
test_oracle = TestOracle('system','Cfy123456','127.0.0.1','1521','orcl')
test_oracle.delete(sql)
print("成功删除course表中的数据")
def queryResult():
sql1 = "SELECT * FROM teacher"
sql2 = "SELECT * FROM course"
test_oracle = TestOracle('system','Cfy123456','127.0.0.1','1521','orcl')
fields1 = test_oracle.select(sql1)
fields2 = test_oracle.select(sql2)
print(fields1)
print(fields2)
if __name__ =="__main__":
print("修改之前的表:")
queryResult()
update_teacher("'cfy'","TID = 1")
delete_course("CID = 1")
print("修改之后的表:")
queryResult()
运行结果如下:
修改之前的表:
[
{
“TID”:1,
“TNAME”:“tz”,
“TCID”:1
},
{
“TID”:2,
“TNAME”:“tw”,
“TCID”:2
},
{
“TID”:3,
“TNAME”:“tl”,
“TCID”:3
}
]
[
{
“CID”:2,
“CNAME”:“html”,
“TID”:1
},
{
“CID”:3,
“CNAME”:“sql”,
“TID”:2
},
{
“CID”:4,
“CNAME”:“web”,
“TID”:3
}
]
成功修改teacher表
delete ok
成功删除course表中的数据
修改之后的表:
[
{
“TID”:1,
“TNAME”:“cfy”,
“TCID”:1
},
{
“TID”:2,
“TNAME”:“tw”,
“TCID”:2
},
{
“TID”:3,
“TNAME”:“tl”,
“TCID”:3
}
]
[
{
“CID”:2,
“CNAME”:“html”,
“TID”:1
},
{
“CID”:3,
“CNAME”:“sql”,
“TID”:2
},
{
“CID”:4,
“CNAME”:“web”,
“TID”:3
}
]
e ok
成功删除course表中的数据
修改之后的表:
[
{
“TID”:1,
“TNAME”:“cfy”,
“TCID”:1
},
{
“TID”:2,
“TNAME”:“tw”,
“TCID”:2
},
{
“TID”:3,
“TNAME”:“tl”,
“TCID”:3
}
]
[
{
“CID”:2,
“CNAME”:“html”,
“TID”:1
},
{
“CID”:3,
“CNAME”:“sql”,
“TID”:2
},
{
“CID”:4,
“CNAME”:“web”,
“TID”:3
}
]
参考1
参考2
|