用Python模拟Oracle的SQL/PLUS工具的实现方法是本文我们主要要介绍的内容。我们知道,团队计划开发数据库服务平台,需要用到一些服务器的脚本开发,为了了解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:
- sqlstrsqlstr=sqlstr+sqlstrline+'\n';
- while sqlstrline[-1]!=";" :
- sqlstrline=raw_input().strip();
- sqlstrsqlstr=sqlstr+sqlstrline+'\n';
- sqlstrsqlstr=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用。
关于Python模拟Oracle的SQL/PLUS工具的实现方法就介绍到这里了,希望本次的介绍能够对您有所收获!
【编辑推荐】
- 适合初学者的MySQL学习笔记之管理员常用操作总结
- 适合初学者的MySQL学习笔记之SELECT语句使用详解
- MySQL数据库存储引擎之MyISAM和InnoDB的区别对比
- 适合初学者的MySQL学习笔记之ORDER BY子句使用详解
- 适合初学者的MySQL学习笔记之MySQL常用命令操作技巧