Python類比實現oracle的sqlplus工具源碼

Python類比實現oracle的sqlplus工具

     團隊計劃開發資料庫服務平台，需要用到一些伺服器的指令碼開發，為了瞭解python，給自己定了一個類比sqlplus的小需求，然後去實現。

個人體會：python開發快捷，整合很多常用的公用包，對常用資料結構使用很方便，最大的缺點是版本較多，新版本不向前相容，對AIX及HP-UNIX不太常用的OS也缺少直接支援。

以下是工具示範：

以下是原始碼：

以下代碼用的python版本是python2.7 http://www.python.org/
需要安裝cx_Oracle開發包(python訪問ORACLE用的) http://cx-oracle.sourceforge.net/

import cx_Oracleimport osimport sysos.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用。