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

c87099d055

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 connectresultglobal promptstrtry:connectresult=0;promptstr="";conn= cx_Oracle.connect(loginstr);promptstr=conn.username+"@"+conn.dsn;print("Database version:",conn.version);print("Connected.");connectresult=1;return connexcept cx_Oracle.InterfaceError as exc:error, = exc.argsprint(exc);except cx_Oracle.DatabaseError as exc:error, = exc.argsprint(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!=None:print(getcolformatstr(cursor.description) % row),;else:print(getcolformatstr(cursor.description) % ''),;  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.argsprint(exc);except cx_Oracle.DatabaseError as exc:error, = exc.argsprint(error.message);except KeyboardInterrupt:print("^C");continue;  　　
　　

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