def dump_all(self): print 'backup...' conn_info = db.get_con_info(self.CONNECTION_ID) if conn_info is None: print "cannot find connection for " + self.CONNECTION_ID exit() tables = [ 'annotation', 'annotation_type', 'gid2source_id', 'gid2terms', 'homologene', 'id_type', 'interaction', 'interaction_type', 'taxid2name', 'term', 'term_category', 'term2gids', 'term2term', 'statistics', ] cmd = [ 'mysqldump', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '-p' + conn_info["PWD"], self.DATABASE, ' '.join(tables), '>' + self.OUTPUT ] print cmd util.unix(cmd)
def restore(self): print 'restoring...' if len(self.RESTORE_DB) == 0: print "You need to specify a database name to be restored using -r option." exit() if len(self.INPUT) == 0: print "You need to specify a .sql file name -i option." exit() conn_info = db.get_con_info(self.CONNECTION_ID) if conn_info is None: print "cannot find connection for " + self.CONNECTION_ID exit() cmd = [ 'mysql', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '-p' + conn_info["PWD"], '-e', '"create database {0}"'.format(self.RESTORE_DB), ] print ' '.join(cmd) util.unix(cmd) cmd = [ 'mysql', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '-p' + conn_info["PWD"], self.RESTORE_DB, '<' + self.INPUT ] print ' '.join(cmd) util.unix(cmd)
def dump_schema(self): tables_with_data = [ 'annotation', 'annotation_type', 'gid2source_id', 'gid2terms', 'homologene', 'id_type', 'interaction', 'interaction_type', 'server_status', 'session', 'statistics', 'taxid2name', 'term', 'term2gids', 'term2term', 'term_category' ] conn_info = db.get_con_info(self.CONNECTION_ID) if conn_info is None: print "cannot find connection for " + self.CONNECTION_ID exit() metascape_db_in_xxx = 'gp_metascape' #todo uncomment next line if len(tables_with_data): cmd = [ 'mysqldump', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '-p' + conn_info["PWD"], metascape_db_in_xxx, ' '.join(tables_with_data), '>' + self.OUTPUT + "_P2" ] print " ".join(cmd) util.unix(cmd) util.unix("cat " + self.OUTPUT + "_P2 >> " + self.OUTPUT) util.unix("rm " + self.OUTPUT + "_P2")
def sync_category_tables(self, dest_db): print 'synchronizing category tables of the gp_devel with the production gp.' con_prod = db.get_con_info("MYSQL01_RW") cmd = [ 'mysqldump', '--host ' + con_prod["HOST"], '-u ' + con_prod["USR"], '-p' + con_prod["PWD"], 'gp', ' '.join(['annotation_type', 'id_type', 'term_category']), '>category_tables.sql' ] print ' '.join(cmd) util.unix(cmd)
def do_update(self): dir = os.path.join(SyncDB.UPLOAD_DIR(),self.dest) cmd = "find %s -name '*.csv' -print0 |xargs -0 tail -n +2 -q > %s" % (dir, self.fn_dest) print cmd s = util.unix(cmd) conn_info = db.get_con_info(SyncDB.CONNECTION_ID) if conn_info is None: print "cannot find connection for " + SyncDB.CONNECTION_ID exit() #Tracer()() print s cols = self.cols.split(',') if 'tax_id' in cols: df = util.read_csv(self.fn_dest, names=self.cols.split(',')) df['tax_id'] = df['tax_id'].map(str) f = lambda x: SyncDB.PARENT_SPECIE_MAP[x] if x in SyncDB.PARENT_SPECIE_MAP else x df['tax_id'] = df['tax_id'].map(f) df.to_csv(self.fn_dest, index=False, header=False) #for interaction table. if 'tax_id_A' in cols: df = util.read_csv(self.fn_dest, names=self.cols.split(',')) df['tax_id_A'] = df['tax_id_A'].map(str) df['tax_id_B'] = df['tax_id_B'].map(str) f = lambda x: SyncDB.PARENT_SPECIE_MAP[x] if x in SyncDB.PARENT_SPECIE_MAP else x df['tax_id_A'] = df['tax_id_A'].map(f) df['tax_id_B'] = df['tax_id_B'].map(f) df.to_csv(self.fn_dest, index=False, header=False) mysql_cmd = [ 'mysqlimport', '--local', '--delete', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '--password='******'--fields-terminated-by=,', '--fields-optionally-enclosed-by="\\""', '--ignore-lines=0', '--columns=%s' % self.cols, SyncDB.DATABASE, self.fn_dest] c = " ".join(mysql_cmd) print c s = util.unix(mysql_cmd)
def dump_data_tables(self): conn_info = db.get_con_info(self.CONNECTION_ID) if conn_info is None: print "cannot find connection for " + self.CONNECTION_ID exit() tables = [ 'annotation', 'gid2source_id', 'gid2terms', 'homologene', 'term', 'term2gids', 'term2term' ] cmd = [ 'mysqldump', '--host ' + conn_info["HOST"], '-u ' + conn_info["USR"], '-p' + conn_info["PWD"], self.DATABASE, ' '.join(tables), '>' + self.OUTPUT ] util.unix(cmd) print " ".join(cmd)