Script to bcp out all the tables/or individual table from a sybase server using python multiprocessing module

bcp_out_all_tables.py

#
# $Id: bcp_out_all_tables.py 22322 2013-04-01 18:05:06Z neerav1985 $
#

import sys
import argparse
import textwrap
import workerpool
import commands 
import logging
import os
import random
#from  multiprocessing import Queue, Process, Pool

logging.basicConfig(format='%(asctime)s %(levelname)s %(message)s', level=logging.DEBUG)


from time import sleep 
import Sybase
import json
from string import Template



bcp_pool = workerpool.WorkerPool(size=15)


class bcp_job(workerpool.Job):    

    def __init__(self, tablename, db_dict):
        self.tablename = tablename
        self.bigun     = False
        self.db_dict   = db_dict

    def run(self):
        if self.bigun:
            logging.info( "### BIGUN START %s  ###" % self.tablename)

        logging.info( "bcp out %s" % self.tablename)

        cmd = Template("""bcp $DB.dbo.$tab out /output/$ServerName/$DB/$tab.dat -c -t "<EOFD>" -r "<EORD>" -U $UserName -S $ServerName -o /migration_disk/logs/$DB.$tab.log -P $Password""" )
        real_cmd = cmd.substitute(DB=self.db_dict["database"],tab=self.tablename,ServerName=self.db_dict["serverName"],UserName=self.db_dict["login"], Password = self.db_dict["password"] )

        logging.info( "--> bcp = %s " % real_cmd )
        logging.debug( commands.getstatusoutput(real_cmd))

if __name__=="__main__":

   parser = argparse.ArgumentParser()
   parser.add_argument("-e","--env",help="specify environment")
   parser.add_argument("-t","--table",help="specify a table for bcp out")
   args = parser.parse_args()
   if not args.env:
      raise RuntimeError("ENV missing")
   env    = EnvironmentHandler(args.env)

   db_dict = env.get_va_db_properties()

   db = Sybase.connect(db_dict["serverName"],db_dict["login"],db_dict["password"],database=db_dict["database"])

   if not os.path.exists('/output/' + db_dict["serverName"] + '/' + db_dict["database"]):
	  os.makedirs('/output/' + db_dict["serverName"] + '/' + db_dict["database"])

   logging.info( db)

   if args.table:
      logging.info("Adding %s to the primary queue" % (args.table ))
      job = bcp_job(args.table, db_dict)
      bcp_pool.put(job)

      bcp_pool.shutdown()
      bcp_pool.wait() 
     
      logging.info( "Exiting...")
      sys.exit(0)

   sql = textwrap.dedent("""\
		SELECT o.name, rowcnt(i.doampg) nrows
		FROM sysobjects o, sysindexes i
		WHERE o.type = 'U'
		AND o.id = i.id
		AND i.indid < 2
		ORDER BY o.name""")

   c = db.cursor()
    
   tables = []
    
   c.execute (sql)
   for r in c.fetchall():
      table=dict()
      table={"tablename":r[0],"rows":str(r[1])}
      tables.append(table)

   for tbl in tables:
      logging.info("Adding %s to the primary queue" % ( tbl["tablename"]))
      job = bcp_job(tbl["tablename"], db_dict)
      bcp_pool.put(job)
   
   bcp_pool.shutdown()
   bcp_pool.wait()
   
   logging.info( "Exiting...")
   sys.exit(0)