python cx_oracle的链接及增删改查

cx_oracle基础使用

一，数据库连接

1、使用tns串连接

oracle_tns = cx_Oracle.makedsn('localhost', 端口,'库名')
connectObj = cx_Oracle.connect('oracleUserName', 'password', oracle_tns)

例：
db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE')
db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE')

2，数据库断开连接

connectObj.close()
建立游标


cursorObj = connectObj.cursor()
关闭游标


cursorObj.close()


增

1、单条插入：


sql = "INSERT INTO T_AUTOMONITOR_TMP(point_id) VALUES(:pointId)"
cursorObj.prepare(sql)
rown = cursorObj.execute(None, {'pointId' : pointId})
connectObj.commit()
2、多条插入：


sql = "INSERT INTO T_AUTOMONITOR_TMP(point_id) VALUES(:pointId)"
cursorObj.prepare(sql)
rown = cursorObj.executemany(None, recordList)
connectObj.commit()
删


sql = "DELETE FROM T_AUTOMONITOR_TMP t WHERE t.point_id = :pointId "
cursorObj.prepare(sql)
rown = cursorObj.execute(None, {'pointId' : pointId})
connectObj.commit()
改


sql = "UPDATE t_automonitor_other t
  SET t.active = '2'
  WHERE t.active = '1'
  AND t.point_id = :pointId
  "
cursorObj.prepare(sql)
cursorObj.execute(None, {'pointId' : pointId})
connectObj.commit()
查


sql = "SELECT t.describ FROM t_automonitor_tmp t WHERE t.point_id = :pointId"
cursorObj.prepare(sql)
cursorObj.execute(None, {'pointId' : pointId})
Tips

注意：增、删、改操作都需要当前连接进行commit()