diff commons/tools/srptExportTable.py @ 18:94ab73e8a190

Uploaded
author m-zytnicki
date Mon, 29 Apr 2013 03:20:15 -0400
parents
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/commons/tools/srptExportTable.py	Mon Apr 29 03:20:15 2013 -0400
@@ -0,0 +1,146 @@
+#!/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()