Simple methods and encapsulated class examples for operating Oracle database in Python

This article mainly introduces the simple methods and encapsulation classes of Python to operate the Oracle database. It analyzes the basic operations of Python's simple connection, query, and closing of the Oracle database in the form of examples, and provides a Python encapsulation for various Oracle operations. Class, friends who need it can refer to

The examples in this article describe simple methods and encapsulation classes for operating Oracle databases in Python. I would like to share it with you for your reference. The details are as follows:

I recently came into contact with Oracle at work and found that it would be much more convenient to use Python scripts in many places, so I wanted to learn the basic method of operating Oracle in Python first. .

Considering the use of Oracle and the existence of NetConfig of OracleClient, I think connecting it should not be a simple matter.

Sure enough, I searched for several connection methods on the Internet, and then I drew it for a long time, but I couldn't find a scoop.

Method 1: Username, password and monitoring are used as parameters respectively

conn=cx_Oracle.connect('用户名','密码','数据库地址:数据库端口/SID')

According to several articles I read As a reminder that I made an error in writing the code, I found that the configuration item for python to connect to the database should be related to the configuration file tnsnames.ora of the Oracle client. But my configuration items did not have a SID item, and I didn’t know what SID was at first. I just followed what was written on the Internet, so this method failed. Later, I figured out that I need to add a SID to the configuration item, and then I thought about whether my system would need to be restarted after this thing is configured. So, let’s look at other methods first….

Method 2: Username, password and listener are used as one parameter

conn=cx_Oracle.connect('用户名/密码@数据库地址:数据库端口/SID')

This method is basically the same as method one, changing the soup without changing the medicine...

Method 3: Use tns configuration information

conn=cx_Oracle.connect('用户名','密码',tns)

The code on the Internet uses a function to obtain tns, and it still uses SID, but... the configuration items that I can already use do not have SID, so I use

tns=cx_Oracle.makedsn('数据库地址','数据库端口', 'SID')

Still doesn’t work, but look at the generation method of this tns which is similar to the two methods above. But I found that the data generated after I randomly input a SID is like this.

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=XE)))

However, the configuration items of my client are probably like this,

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)( PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=KGDB)))

I guess they look the same and the types are all string types. Try putting them directly in my file. Try assigning the configuration items to tns.

tns = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=KGDB)))'
conn = cx_Oracle.connect('nicker', '123456', tns)

Hmm. Success~

Finally, post a complete code of the basic usage

#coding:utf-8
import cx_Oracle
# 创建数据库连接
# cx_Oracle.connect('username','pwd','ora的tns信息')
# oracle数据库的tns信息，从tnsnames.ora中找到plsql可用的配置项，将该配置项直接拷贝过来即可
ora_tns = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=KGDB)))'
conn = cx_Oracle.connect('nicker', '123456', ora_tns)
# 操作游标
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM inst_info")
# 获取返回信息
rs = cursor.fetchall()
# 输出信息
for v in rs:
  print v
#关闭连接，释放资源
cursor.close()
conn.close()

Observation and discovery summary is very important, understand You also need to

paste a class that encapsulates Oracle

#coding:utf-8
import cx_Oracle
# 封装的类
class cxOracle:
  '''
  tns的取值tnsnames.ora对应的配置项的值，如：
  tns = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.16.18.23)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=MYDB)))'
  '''
  def __init__(self ,uname, upwd,tns ):
    self ._uname = uname
    self ._upwd = upwd
    self ._tns = tns
    self ._conn = None
    self ._ReConnect()
  def _ReConnect(self ):
    if not self._conn :
      self ._conn = cx_Oracle.connect (self. _uname, self ._upwd, self._tns)
    else:
      pass
  def __del__(self ):
    if self. _conn:
      self ._conn. close()
      self ._conn = None
  def _NewCursor(self ):
    cur = self. _conn.cursor ()
    if cur:
      return cur
    else:
      print "#Error# Get New Cursor Failed."
      return None
  def _DelCursor(self , cur):
    if cur:
      cur .close()
  # 检查是否允许执行的sql语句
  def _PermitedUpdateSql(self ,sql):
    rt = True
    lrsql = sql. lower()
    sql_elems = [ lrsql.strip ().split()]
    # update和delete最少有四个单词项
    if len( sql_elems) < 4 :
      rt = False
    # 更新删除语句，判断首单词，不带where语句的sql不予执行
    elif sql_elems[0] in [ &#39;update&#39;, &#39;delete&#39;]:
      if &#39;where&#39; not in sql_elems :
        rt = False
    return rt
  # 导出结果为文件
  def Export(self , sql, file_name, colfg =&#39;||&#39;):
    rt = self. Query(sql )
    if rt:
      with open( file_name, &#39;a&#39;) as fd:
        for row in rt:
          ln_info = &#39;&#39;
          for col in row:
             ln_info += str( col) + colfg
          ln_info += &#39;\n&#39;
          fd .write( ln_info)
  # 查询
  def Query(self , sql, nStart=0 , nNum=- 1):
    rt = []
    # 获取cursor
    cur = self. _NewCursor()
    if not cur:
      return rt
    # 查询到列表
    cur .execute(sql)
    if ( nStart==0 ) and (nNum==1 ):
      rt .append( cur.fetchone ())
    else:
      rs = cur. fetchall()
      if nNum==- 1:
        rt .extend( rs[nStart:])
      else:
        rt .extend( rs[nStart:nStart +nNum])
    # 释放cursor
    self ._DelCursor(cur)
    return rt
  # 更新
  def Exec(self ,sql):
    # 获取cursor
    rt = None
    cur = self. _NewCursor()
    if not cur:
      return rt
    # 判断sql是否允许其执行
    if not _PermitedUpdateSql(sql ):
      return rt
    # 执行语句
    rt = cur. execute(sql )
    # 释放cursor
    self ._DelCursor(cur)
    return rt
# 类使用示例
tns = &#39;(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.16.17.46)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=MYDB)))&#39;
ora = cxOracle (&#39;nicker&#39;, &#39;123456&#39;, tns)
# 导出结果为文件
rs = ora .Export("SELECT * FROM org", &#39;1.txt&#39;)
# 查询结果到列表
rs = ora.Query("SELECT * FROM org")
print rs
# 更新数据
ora.Exec("update org set org_name=&#39;NewNameForUpdate&#39; where org_id=123456;")

Related recommendations:

Detailed explanation of Python using cx_Oracle module to operate Oracle database

The above is the detailed content of Simple methods and encapsulated class examples for operating Oracle database in Python. For more information, please follow other related articles on the PHP Chinese website!