Table of Content

There is not always database API available for python, we can use same way in unix command line, run isql query and return query result in python.

This run_isql_query is handy tool to for this purpose:

  • hide all details, only need query statement
  • return formatted query result with delim "|", this is clean table list, easy to do further process

#!/usr/bin/python
# run_isql_query
# dev@dreamcloud
# Latest update: Aug 5, 2015 

import os
import sys
import subprocess
import shlex
import time
import datetime

def run_shell(shell_cmd):
    """
    dev@dreamcloud
    run unix shell command, it is wrapper of subprocess
    return out,err
    """
    proc=subprocess.Popen(shlex.split(shell_cmd),stdout=subprocess.PIPE, bufsize=4096)
    out,err = proc.communicate()
    return out,err

def run_isql_query(dbname,query,user='xx',password='xxxx'):
    """
    dev@dreamcloud
    isql interface to run isql query in sybase ASE
    input: string dbname, isql_query
    return: List of formatted isql query result with delim "|"
    """

    isqlfile='myisqlquery.sql'
    with open(isqlfile, 'w') as p:
        p.write('set nocount on\n')
        p.write('go\n')
        p.write(query+'\n')
        p.write('go\n')

    run_isql_cmd='isql -U'+user+' -P'+password+' -D' + dbname +' -s "|" -w9999 -b -i ' + isqlfile
    out,err = run_shell(run_isql_cmd)
    out_list=[]
    for line in out.splitlines():
        line=line.strip('|\n')
        tokens=line.split('|')
        tokens=[t.strip() for t in tokens]
        line='|'.join(tokens)
        out_list.append(line)
    return out_list,err

if __name__ == '__main__':

    mydb='saledb'
    salename='Tom'
    myquery='select * from saletable where name="%s"' % salename
    out,err=run_isql_query(myquery)
    print out
    for line in out:
        print line