During the weekend, I stumbled across an interesting project named pysql. The project aims to replace SQL*Plus with a sane shell written in Python with history, tab completion and many extensions. Being a veteran of using SQL*Plus, I know that some of the above can be actually achieved on Linux/Unix environments with SQL*Plus using a handy utility called rlwrap but I was still curios.

The installation is a breeze (as long as you already have Python and cx_Oracle installed correctly). Just unzip into a directory and run pysql.sh. First impression – this is really cool stuff. Out of the box you get coloring, correct terminal handling, help, implemented macros and even visual graphs. The only problem I had was the lack of any documentation (that I could find) about how to use it all. Source code browsing is good and all but I would have preferred an easy how-to document.

Oh, and I stumbled across a bug (that Sébastien Renard told me is already fixed in the next version) that would not allow me to connect “/ as sysdba”. I did a small fix in pysqlshell.py that allowed me to easily fix the issue (just replace these 2 functions):

def __connect(self, connectString, mode=""):
    """Calls the PysqlDb class to connect to Oracle"""
    sid = None
    count=connectString.count("@")
    if count==1:
        (connectString, sid)=connectString.split("@")
    elif count > 1:
        raise PysqlException(_("Invalid connection string"))
    count=connectString.count("/")
    if count==0:
        user=connectString
        try:
            passwd=getpass()
        except (Exception):
            raise PysqlException(_("Invalid connection string"))
    elif count == 1:
        (user, passwd)=connectString.split("/")
    else:
        raise PysqlException(_("Invalid connection string"))
    if sid==None:
        connectString = user + "/" + passwd
    else:
        connectString = user + "/" + passwd + "@" + sid
    self.db=PysqlDb(connectString, mode)
    self.__setPrompt()

def __setPrompt(self, blank=False, multiline=False, finishedQuery=False):
    """Sets the prompt according to the connexion state
    @param blank: if true, no prompt is issue (default is False)
    @param finishedQuery: if true mark prompt with a * to notify a query is finished
    @type blank: bool
    @type finishedQuery: bool"""
    #TODO: do not update title for every line
    codec=self.conf.getCodec()
    if blank or not self.showPrompt:
        prompt=""
    elif multiline:
        prompt="> "
    else:
        if self.db is None:
            prompt=self.notConnectedPrompt
            # Update the title (without color else it is a huge mess)
            setTitle(_("Pysql - Not connected"), codec)
        else:
            sid = self.db.getDSN() if self.db.getDSN() != "None" else os.environ["ORACLE_SID"]
            userName = self.db.getUsername() if self.db.getUsername() != "" else "/"
            prompt=userName+"@"+sid+" "
            if finishedQuery:
                prompt+="* "
            setTitle("Pysql - %s" % prompt, codec)
    self.prompt=prompt.encode(codec, "replace")

This project is definitely on my “keep-an-eye-on” list.
Is anybody else using this?