Python模拟实现oracle的sqlplus工具源码

àì夳堔傛蜴生んèń 2022-06-18 00:18 207阅读 0赞

**Python模拟实现oracle的sqlplus工具**

团队计划开发数据库服务平台，需要用到一些服务器的脚本开发，为了了解python，给自己定了一个模拟sqlplus的小需求，然后去实现。

个人体会：python开发快捷，集成很多常用的公共包，对常用数据结构使用很方便，最大的缺点是版本较多，新版本不向前兼容，对AIX及HP-UNIX不太常用的OS也缺少直接支持。

以下是工具演示：

![0_1313504233XY9w.gif][]

以下是源代码：

以下代码用的python版本是python2.7 [http://www.python.org/][http_www.python.org]  
需要安装cx\_Oracle开发包(python访问ORACLE用的) [http://cx-oracle.sourceforge.net/][http_cx-oracle.sourceforge.net]

import cx_Oracle
    import os
    import sys
    os.environ['NLS_LANG'] ='AMERICAN_AMERICA.ZHS16GBK';
    
    connectresult=0;
    promptstr="";
    fetchsize=50;
    #conn = cx_Oracle.connect('yzs/yzs@mydb');
    print("------------Welcome To Python Sqlplus ----------------------");
    print("|  Version     : 0.1");
    print("|  Author      : MKing");
    print("|  Blog        : http://blog.csdn.net/yzsind");
    print("|  Sina weibo  : http://weibo.com/yzsind");
    print("|  Release Date: 2011-08-08");
    print("|  Login Example1:username/password@tnsname");
    print("|  Login Example2:username/password@host:port/dbname");
    print("|  Input exit to Quit");
    print("-----------------------------------------------------------");
    print("");
    
    def getConnect(loginstr):
      global connectresult
      global promptstr
      try:
        connectresult=0;
        promptstr="";
        conn= cx_Oracle.connect(loginstr);
        promptstr=conn.username+"@"+conn.dsn;
        print("Database version:",conn.version);
        print("Connected.");
        connectresult=1;
        return conn
      except cx_Oracle.InterfaceError as exc:
        error, = exc.args
        print(exc);
      except cx_Oracle.DatabaseError as exc:
        error, = exc.args
        print(error.message);
    
    def getcolformatstr(coldef):
      if coldef[1]==cx_Oracle.NUMBER:
        formatstr='%12s';
      else:
        if coldef[2]<=32:
          formatstr='%-'+str(coldef[2])+'s';
        else:
          formatstr='%-32s';
      return formatstr
      
    #########################################################################
    while 1:
      try:
        loginstr=raw_input("login>").strip();
        if loginstr=="" :
          continue;
        elif loginstr in ["exit","exit;"]:
          print("...bye...");
          exit();    
        conn = getConnect(loginstr);
        if connectresult==1:
          break;
      except KeyboardInterrupt:
        print("^C");
        continue;  
    while 1:
      sqlstr="";
      try:
        sqlstrline=raw_input(promptstr+">").strip();
        if sqlstrline=="" :
          continue;
        elif sqlstrline.lower() in ["exit","exit;"]:
          print("...bye...");
          exit();
        elif sqlstrline[0:7].lower()=="connect" :
          conn = getConnect(sqlstrline[8:]);
        elif sqlstrline.lower() in ["disconnect","disconnect;"] :
          conn.close();
          print("Connection closed.");
        elif sqlstrline[0:4].lower()=="host" :
          os.system(sqlstrline[4:])
        else:
          sqlstr=sqlstr+sqlstrline+'\n';
          while sqlstrline[-1]!=";" :
            sqlstrline=raw_input().strip();
            sqlstr=sqlstr+sqlstrline+'\n';
          sqlstr=sqlstr[0:len(sqlstr)-2]
          try:
            cursor = conn.cursor();
            cursor.execute(sqlstr);
            if sqlstr[0:6].lower()=="select" :
              cols=[]
              for col in cursor.description:
                print(getcolformatstr(col) % (col[0])),
              print('');
              for col in cursor.description:
                if col[1]==cx_Oracle.NUMBER:
                  print('-'*12),;
                else:
                  if col[2]<=32:
                    print('-'*col[2]),;
                  else:
                    print('-'*32),;
              print('');
              recs = cursor.fetchmany(fetchsize);
              while len(recs)>0:
                for row in recs:
                  for i in range(len(row)):
                    if row[i]!=None:
                      print(getcolformatstr(cursor.description[i]) % row[i]),;
                    else:
                      print(getcolformatstr(cursor.description[i]) % ''),;  
                  print('')
                recs = cursor.fetchmany(fetchsize);
              print(str(cursor.rowcount)+" rows selected.");
            elif sqlstr[0:6].lower()=="insert" :
              print(str(cursor.rowcount)+" rows inserted.");
            elif sqlstr[0:6].lower()=="update" :
              print(str(cursor.rowcount)+" rows updated.");
            elif sqlstr[0:6].lower()=="delete" :
              print(str(cursor.rowcount)+" rows deleted.");
            elif sqlstr[0:5].lower()=="merge" :
              print(str(cursor.rowcount)+" rows merged.");
            elif sqlstr[0:6].lower()=="commit" :
              print("Commit complete.");
            elif sqlstr[0:6].lower()=="rollback" :
              print("Rollback complete.");
            else :
              print("sql execute complete.");
          except cx_Oracle.InterfaceError as exc:
            error, = exc.args
            print(exc);
          except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            print(error.message);
      except KeyboardInterrupt:
        print("^C");
        continue;

特别说明：纯属个人学习python的代码，未考虑扩展性与性能，未经过专业测试，不建议拿到实际工作中当SQLPLUS用。

[0_1313504233XY9w.gif]: /images/20220618/4a357b43d59a49b4ad37a69830a51a11.png
[http_www.python.org]: http://www.python.org/
[http_cx-oracle.sourceforge.net]: http://cx-oracle.sourceforge.net/