view commons/tools/srptExportTable.py @ 31:0ab839023fe4

Uploaded

#!/usr/bin/env python

import user, os, sys, getopt, exceptions, ConfigParser

#-----------------------------------------------------------------------------

def help():

    """
    Give the list of the command-line options.
    """

    print
    print "usage:",sys.argv[0]," [ options ]"
    print "options:"
    print "     -h: this help"
    print "     -i: name of the table to export"
    print "     -o: name of the output file (default=inTable)"
    print "     -p: extra parameters to add to the SQL query (e.g. 'ORDER BY path')"
    print "     -k: keep the first line"
    print "     -C: configuration file from TEdenovo or TEannot pipeline"
    print "     -H: MySQL host (if no configuration file)"
    print "     -U: MySQL user (if no configuration file)"
    print "     -P: MySQL password (if no configuration file)"
    print "     -D: MySQL database (if no configuration file)"
    print "     -v: verbose (default=0/1)"
    print

#-----------------------------------------------------------------------------

def main():

    """
    This program exports all the data contained in a MySQL table into a flat file in the current directory.
    """

    inTable = ""
    outFileName = ""
    param = ""
    keepFirstLine = False
    configFileName = ""
    host = ""
    user = ""
    passwd = ""
    dbname = ""
    verbose = 0

    try:
        opts, args = getopt.getopt(sys.argv[1:],"hi:o:p:kC:H:U:P:D:v:")
    except getopt.GetoptError, err:
        print str(err)
        help()
        sys.exit(1)
    for o,a in opts:
        if o == "-h":
            help()
            sys.exit(0)
        elif o == "-i":
            inTable = a
        elif o == "-o":
            outFileName = a
        elif o == "-p":
            param = a
        elif o == "-k":
            keepFirstLine = True
        elif o == "-C":
            configFileName = a
        elif o == "-H":
            host = a
        elif o == "-U":
            user = a 
        elif o == "-P":
            passwd = a
        elif o == "-D":
            dbname = a
        elif o == "-v":
            verbose = int(a)

    if inTable == "":
        print "*** Error: missing input table name"
        help()
        sys.exit(1)

    if configFileName != "":
        config = ConfigParser.ConfigParser()
        config.readfp( open(configFileName) )
        host = config.get("repet_env","repet_host")
        user = config.get("repet_env","repet_user")
        passwd = config.get("repet_env","repet_pw")
        dbname = config.get("repet_env","repet_db")
    if host == "" or user == "" or passwd == "" or dbname == "":
        if os.environ.get( "REPET_HOST" ) not in [ "", None ]:
            host = os.environ.get( "REPET_HOST" )
        if os.environ.get( "REPET_USER" ) not in [ "", None ]:
            user = os.environ.get( "REPET_USER" )
        if os.environ.get( "REPET_PW" ) not in [ "", None ]:
            passwd = os.environ.get( "REPET_PW" )
        if os.environ.get( "REPET_DB" ) not in [ "", None ]:
            dbname = os.environ.get( "REPET_DB" )
    if host == "" or user == "" or passwd == "" or dbname == "":
        print "*** Error: missing information about MySQL connection"
        sys.exit(1)

    if outFileName == "":
        outFileName = inTable

    prg = "mysql"
    cmd = prg
    cmd += " -h %s" % ( host )
    cmd += " -u %s" % ( user )
    cmd += " -p\"%s\"" % ( passwd )
    cmd += " --database=%s" % ( dbname )
    cmd += " -e\"SELECT * FROM %s" % ( inTable )
    if param != "":
        cmd += " %s" % ( param )
    cmd += ";\""
    cmd += " > "
    if keepFirstLine == False:
        cmd += "%s.tmp" % ( outFileName )
    else:
        cmd += "%s" % ( outFileName )
    if verbose > 0: print cmd; sys.stdout.flush()
    log = os.system( cmd )
    if log != 0:
        print "*** Error: %s returned %i" % ( prg, log )
        sys.exit(1)

    if keepFirstLine == False:
        tmpFileName = "%s.tmp" % ( outFileName )
        tmpFile = open( tmpFileName, "r" )
        outFile = open( outFileName, "w" )
        i = 0
        for line in tmpFile:
            if i > 0:
                outFile.write( line )
            i += 1
        tmpFile.close()
        outFile.close()
        os.remove( tmpFileName )

    return 0

#----------------------------------------------------------------------------

if __name__ == '__main__':
    main()