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_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用。
相关推荐
IT之家 2020-03-11
graseed 2020-10-28
zbkyumlei 2020-10-12
SXIAOYI 2020-09-16
jinhao 2020-09-07
impress 2020-08-26
liuqipao 2020-07-07
淡风wisdon大大 2020-06-06
yoohsummer 2020-06-01
chenjia00 2020-05-29
baike 2020-05-19
扭来不叫牛奶 2020-05-08
hxmilyy 2020-05-11
黎豆子 2020-05-07
xiongweiwei00 2020-04-29
Cypress 2020-04-25
冰蝶 2020-04-20