Esempio n. 1
0
def _parse_results(file):
    db = lib.db.db()
    db.params['dbname'] = 'databases/d2bfs.db'
    db.params['table'] = 'results'
    db.connect()
    lines = open(file, 'r').readlines()
    for line in lines:
        if '[ssh]' in line and 'host:' in line:
            line = line.split()
            host = line[2]
            user = line[4]
            passwd = line[6]
            db.select(
                'SELECT * FROM ' + db.params['table'] +
                ' WHERE host=? AND user=? AND pass=?', (
                    host,
                    user,
                    passwd,
                ))
            if db.params['selects']:
                continue
            else:
                db.params['cursor'].execute(
                    'INSERT INTO ' + db.params['table'] +
                    ' VALUES  (NULL, ?, ?, ?)', (
                        host,
                        user,
                        passwd,
                    ))
                db.params['connex'].commit()
    db.close()
Esempio n. 2
0
 def test_connect(self):
     db = self.db(connect='host:server | user:username | password:secret')
     db.connect()
     args = db._dbapi.connect_kwargs
     self.assertTrue('host' in args)
     self.assertEqual('server', args['host'])
     self.assertTrue('user' in args)
     self.assertEqual('username', args['user'])
     self.assertTrue('password' in args)
     self.assertEqual('secret', args['password'])
Esempio n. 3
0
 def test_connect(self):
     db = self.db(connect='host:server | user:username | password:secret')
     db.connect()
     args = db._dbapi.connect_kwargs
     self.assertTrue('host' in args)
     self.assertEqual('server', args['host'])
     self.assertTrue('user' in args)
     self.assertEqual('username', args['user'])
     self.assertTrue('password' in args)
     self.assertEqual('secret', args['password'])
def insert_parasites(raw_data, to_taxon_id):
    """Insert parasites."""
    log(f'Inserting {DATASET_ID} parasites')

    parasite_records = []
    for _, row in raw_data.iterrows():

        parasite_records.append(parasite_record(
            row, to_taxon_id['lice'], 'lice_total',
            ['Ecomorph Notes', 'lice ages']))

        parasite_records.append(parasite_record(
            row, to_taxon_id['feather_mites'], 'feather_mites_total'))

        parasite_records.append(parasite_record(
            row, to_taxon_id['ticks'], 'ticks_total'))

        parasite_records.append(parasite_record(
            row, to_taxon_id['flies'], 'flies_total'))

        parasite_records.append(
            parasite_record(row, to_taxon_id['fleas'], 'fleas_total'))

        parasite_records.append(
            parasite_record(row, to_taxon_id['others'], 'others'))

    parasites = pd.DataFrame(parasite_records)
    parasites['parasite_id'] = db.get_ids(parasites, 'parasites')

    parasites.loc[:, db.PARASITE_COLUMNS].to_sql(
        'parasites', db.connect(), if_exists='append', index=False)
def insert_counts(counts, to_event_id, to_taxon_id):
    """Insert counts."""
    in_species = counts['SCIENTIFIC_NAME'].isin(to_taxon_id)
    has_event = counts['SAMPLING_EVENT_IDENTIFIER'].isin(to_event_id)
    counts = counts.loc[in_species & has_event, :].copy()

    if counts.shape[0] == 0:
        return

    counts['count_id'] = db.create_ids(counts, 'counts')
    counts['event_id'] = counts.SAMPLING_EVENT_IDENTIFIER.map(to_event_id)
    counts['taxon_id'] = counts.SCIENTIFIC_NAME.map(to_taxon_id)
    counts['dataset_id'] = DATASET_ID

    counts = counts.rename(columns={'OBSERVATION_COUNT': 'count'})
    counts.loc[counts['count'] == 'X', 'count'] = '-1'
    counts['count'] = pd.to_numeric(counts['count'], errors='coerce')

    fields = """SCIENTIFIC_NAME GLOBAL_UNIQUE_IDENTIFIER LAST_EDITED_DATE
        TAXONOMIC_ORDER CATEGORY SUBSPECIES_SCIENTIFIC_NAME
        BREEDING_BIRD_ATLAS_CODE BREEDING_BIRD_ATLAS_CATEGORY AGE_SEX
        OBSERVER_ID HAS_MEDIA SPECIES_COMMENTS""".split()
    counts['count_json'] = util.json_object(counts, fields)

    print(counts.shape)

    counts.loc[:, db.COUNT_FIELDS].to_sql('counts',
                                          db.connect(),
                                          if_exists='append',
                                          index=False)
Esempio n. 6
0
def ingest_werner_data():
    """Ingest the Werner Excel sheet stored on Google drive."""
    cxn = db.connect()

    werner = read_werner_data()
    taxonomy = read_taxon_data(cxn)

    sci_names = taxonomy.sci_name.tolist()
    is_sci_name = werner.sci_name.isin(sci_names)

    synonyms = get_synonyms(taxonomy)
    is_synonym = werner.sci_name.isin(synonyms)

    update_it = ~is_sci_name & is_synonym

    werner.loc[update_it, 'sci_name'] = \
        werner.loc[update_it, 'sci_name'].apply(lambda x: synonyms[x])

    dups = werner.sci_name.duplicated()
    werner = werner.loc[~dups, :]

    werner['genus'] = werner.sci_name.str.split().str[0]
    werner['genus'] = werner['genus'].map(GENUS_REPLACE)

    create_werner_data_table(cxn, werner)
Esempio n. 7
0
def find_requests_and_send_mail(config):
    os.chdir(os.path.dirname(os.path.realpath(__file__)))
    db = DB.connect(db_file='../db/main.db')
    now = time.time()
    what_we_be_done_with = db['c'].execute(
        'select * from reminders where end_time < %d' % now).fetchall()

    for row in DB.map(what_we_be_done_with, 'reminders', db=db):
        row['link'] = "http://indycast.net/%s/slices/%s_%d" % (
            row['station'],
            time.strftime("%Y%m%d%H%M",
                          time.gmtime(row['start_time'] - row['offset'] * 60)),
            (row['end_time'] - row['start_time']) / 60)

        if len(row['notes']):
            row['link'] += '/%s_on_%s' % (re.sub(
                '[^\w]', '_', row['notes']).strip('_'), row['station'])

        row['link'] += '.mp3'

        email = do_template(template_file='email_reminder_template.txt',
                            settings=row)
        res = misc.send_email(config=config,
                              who=row['email'],
                              subject=email['subject'],
                              body=email['body'])
        db['c'].execute('delete from reminders where id = %d' % row['id'])
        db['conn'].commit()

    return None
def main(cfg):
    global api_path
    api_path = cfg.api_base + "/executes"
    logger.log("Testing Executes API, api path: %s", api_path)
    conn = db.connect(cfg)

    test_asset_name = "script-test-sp-echo"

    _delete(conn, test_asset_name)

    try:
        asset_id = create_asset(cfg, test_asset_name)
        add_role(cfg, asset_id, "script", json.dumps(script_role_value))
        add_role(cfg, asset_id, "script-executor-amc", {"os": "linux"})

        rsp_msg = execute(cfg, asset_id)
        rsp_msg_json = json.loads(rsp_msg)
        if rsp_msg_json["code"] != 200:
            return logger.failed("unexpected error: " + rsp_msg)

        actual_output = str(rsp_msg_json["message"]).rstrip()
        if actual_output != expected_output:
            return logger.failed("expected: '" + expected_output +
                                 "', actual: '" + actual_output + "'")

    except Exception, x:
        #traceback.print_exc()
        return logger.failed(x.message)
Esempio n. 9
0
File: server.py Progetto: EQ4/DRR
    def stats():
        """ 
    Reports various statistical metrics on a particular server.  
    Use this with the graph.py tool to see station coverage.
    """
        misc.am_i_official()
        db = DB.connect()

        stats = misc.base_stats()

        stats.update({
            'intents':
            DB.all('intents'),
            'hits':
            db['c'].execute('select sum(read_count) from intents').fetchone()
            [0],
            'kv':
            DB.all('kv'),
            'uptime':
            TS.uptime(),
            'pwd':
            os.getcwd(),
            'free':
            os.popen("df -h / | tail -1").read().strip(),
            # Reporting the list as fractional GB is more useful.
            'streams':
            DB.all('streams', sort_by='start_unix'),
            'config':
            misc.public_config()
        })

        return jsonify(stats), 200
Esempio n. 10
0
def blast_query_against_one_shard(args, state, shard):
    """
    Blast the query against one blast DB shard.

    Then write the results to the database.
    """
    temp_dir = util.iter_dir(args['temp_dir'], state['blast_db'],
                             state['query_target'], state['iteration'])

    output_file = blast.output_file_name(temp_dir, shard)

    blast.against_sra(args, state, output_file, shard)

    with db.connect(state['blast_db']) as db_conn:
        db.aux_db(db_conn, args['temp_dir'], state['blast_db'],
                  state['query_target'])

        shard = basename(shard)

        batch = []

        hits = blast.hits(output_file)
        for hit in hits:
            match = blast.PARSE_RESULTS.match(hit['title'])
            if match:
                seq_name = match.group(1)
                seq_end = match.group(2)
            else:
                seq_name = hit['title']
                seq_end = ''
            batch.append((state['iteration'], seq_end, seq_name, shard))
        db.insert_blast_hit_batch(db_conn, batch)
Esempio n. 11
0
def ingest_sprent_data():
    """Read the Sprent nodulataion."""
    csv_path = util.SPRENT_DATA_CSV
    cxn = db.connect()

    data = pd.read_csv(csv_path,
                       header=0,
                       names=[
                           'genus', 'species_count', 'nodulation_status',
                           'nodulating_species', 'clade', 'legume_subfamily'
                       ])

    # Split this record into two
    idx = data['genus'] == 'Fillaeopsis_and_Lemurodendron'
    data.loc[idx, 'genus'] = 'Fillaeopsis'
    data.loc[idx, 'species_count'] = 1

    row = data.loc[idx, :].copy()
    row['genus'] = 'Lemurodendron'
    data = data.append(row, ignore_index=True)

    # Remove stray records
    data = data[data.genus != 'Piptadenia viridiflora']
    data = data[data.genus != 'Otion']

    # Cleanup genus
    data['genus'] = data.genus.str.split().str[0]

    create_sprent_table(cxn, data)
Esempio n. 12
0
def save(df, sheet):
    """Convert the dataframe/Excel sheet into a CSV file."""
    name = util.NOD_CSV + sheet + '.csv'
    df.to_csv(name, index=False)

    table = 'nodulation_' + sheet
    df.to_sql(table, db.connect(), if_exists='replace', index=False)
Esempio n. 13
0
def assemble(args):
    """Loop thru every blast/query pair and run an assembly for each one."""
    with util.make_temp_dir(
            where=args['temp_dir'],
            prefix='atram_',
            keep=args['keep_temp_dir']) as temp_dir:
        util.update_temp_dir(temp_dir, args)

        queries = split_queries(args)

        for blast_db in args['blast_db']:

            with db.connect(blast_db, check_version=True) as cxn:
                for query in queries:
                    db.aux_db(cxn, args['temp_dir'], blast_db, query)
                    clean_database(cxn)

                    log.setup(args['log_file'], blast_db, query)

                    assembler = assembly.factory(args, cxn)

                    try:
                        assembly_loop(args, assembler, blast_db, query)
                    except (TimeoutError, RuntimeError):
                        pass
                    except Exception as err:
                        log.error('Exception: {}'.format(err))
                    finally:
                        assembler.write_final_output(blast_db, query)

                    db.aux_detach(cxn)
Esempio n. 14
0
def get_albums():
    """Get all images from the database."""
    step = 2000  # Basic set size
    photos = []

    with db.connect() as cxn:
        cursor = cxn.execute('select * from images')
        for image in cursor:
            path = util.PHOTOS / image[0]
            if path.exists():
                photos.append({'image_file': image[0], 'sample_id': image[1]})

    shuffle(photos)

    *albums, residual = [
        photos[i:i + step] for i in range(0, len(photos), step)
    ]
    albums[-1] += residual

    for i, album in enumerate(albums, 1):
        out_dir = f'album_{i}'
        for photo in album:
            photo['album'] = out_dir
            photo['photo'] = photo['image_file'].replace('/', '_')

    return albums
Esempio n. 15
0
def assemble(args):
    """Loop thru every blast/query pair and run an assembly for each one."""

    with util.make_temp_dir(where=args['temp_dir'],
                            prefix='atram_',
                            keep=args['keep_temp_dir']) as temp_dir:
        util.update_temp_dir(temp_dir, args)

        queries = split_queries(args)

        for blast_db in args['blast_db']:

            with db.connect(blast_db, check_version=True) as cxn:
                for query in queries:
                    db.aux_db(cxn, args['temp_dir'], blast_db, query)
                    clean_database(cxn)

                    log.setup(args['log_file'], blast_db, query)

                    assembler = assembly.factory(args, cxn)

                    try:
                        assembly_loop(args, assembler, blast_db, query)
                    except (TimeoutError, RuntimeError):
                        pass
                    except Exception as err:  # pylint: disable=broad-except
                        log.error('Exception: {}'.format(err))
                    finally:
                        assembler.write_final_output(blast_db, query)

                    db.aux_detach(cxn)
Esempio n. 16
0
def blast_query_against_one_shard(args, state, shard):
    """
    Blast the query against one blast DB shard.

    Then write the results to the database.
    """
    output_file = blast.output_file_name(state['iter_dir'], shard)

    blast.against_sra(args, state, output_file, shard)

    with db.connect(state['blast_db']) as cxn:
        db.aux_db(
            cxn,
            args['temp_dir'],
            state['blast_db'],
            state['query_target'])

        shard = basename(shard)

        batch = []

        hits = blast.hits(output_file)
        for hit in hits:
            seq_name, seq_end = blast.parse_blast_title(hit['title'])
            batch.append((state['iteration'], seq_end, seq_name, shard))
        db.insert_blast_hit_batch(cxn, batch)
Esempio n. 17
0
    def test_setup(self):
        db = self.db()
        db.connect()
        db.setup({
          1 : ['ROLLOUT 1', 'ROLLBACK 1'],
          2 : ['ROLLOUT 2', 'ROLLBACK 2']
        })

        # Statement 0: SELECT version - ignore
        # Statement 1: Rollout statment 1 - check:
        self.assertEqual("ROLLOUT 1", db._conn.cursor_return.execute_kwargs[1][0])
        # Statement 2: INSERT version - ignore
        # Statement 3: Rollout statment 2 - check:
        self.assertEqual("ROLLOUT 2", db._conn.cursor_return.execute_kwargs[3][0])
        # Statement 4: INSERT version - check
        self.assertEqual("INSERT INTO test_version", db._conn.cursor_return.execute_kwargs[4][0][0:24])
        self.assertEqual(2, db._conn.cursor_return.execute_kwargs[4][1][0])
Esempio n. 18
0
def clean_taxonomy():
    """Audit problem taxonomy records in the database."""
    cxn = db.connect()
    taxonomy_ids = pd.read_sql('SELECT * FROM taxonomy_ids;', cxn)
    errors = get_duplicate_sample_ids(taxonomy_ids)
    create_taxonomy_errors_table(cxn, errors)

    drop_bad_genera(cxn)
    drop_errors(cxn)
Esempio n. 19
0
def ingest_taxonomy(google_sheet):
    """Ingest data related to the taxonomy."""
    cxn = db.connect()

    taxonomy = get_taxonomy(google_sheet)
    taxonomy = split_sample_ids(taxonomy)

    taxonomy.to_sql(google_sheet, cxn, if_exists='replace', index=False)
    create_taxon_ids_table(cxn, google_sheet, taxonomy)
def get_taxa():
    """Build a dictionary of scientific names and taxon_ids."""
    log(f'Getting {DATASET_ID} taxa')
    sql = """SELECT taxon_id, sci_name
               FROM taxa
              WHERE sci_name IN ({})"""
    sql = sql.format(','.join([f"'{x}'" for x in TARGETS]))
    taxa = pd.read_sql(sql, db.connect())
    return taxa.set_index('sci_name').taxon_id.to_dict()
Esempio n. 21
0
def ingest_nfn_data():
    """Ingest data related to the taxonomy."""
    cxn = db.connect()

    exps = [get_expedition(e) for e in util.EXPEDITIONS]
    nfn = pd.concat(exps, ignore_index=True).fillna('')
    nfn = fixup_data(nfn)
    nfn = update_collector_data(nfn)

    create_nfn_table(cxn, nfn)
Esempio n. 22
0
def write_to_db(sample_wells):
    """Output the sample wells to the database."""
    with db.connect() as cxn:
        sample_wells.to_sql(
            'sample_wells', cxn, if_exists='replace', index=False)

        cxn.execute("""
            CREATE UNIQUE INDEX IF NOT EXISTS
                sample_wells_plate_id_well ON sample_wells (plate_id, well);
            """)
Esempio n. 23
0
    def test_setup(self):
        db = self.db()
        db.connect()
        db.setup({
            1: ['ROLLOUT 1', 'ROLLBACK 1'],
            2: ['ROLLOUT 2', 'ROLLBACK 2']
        })

        # Statement 0: SELECT version - ignore
        # Statement 1: Rollout statment 1 - check:
        self.assertEqual("ROLLOUT 1",
                         db._conn.cursor_return.execute_kwargs[1][0])
        # Statement 2: INSERT version - ignore
        # Statement 3: Rollout statment 2 - check:
        self.assertEqual("ROLLOUT 2",
                         db._conn.cursor_return.execute_kwargs[3][0])
        # Statement 4: INSERT version - check
        self.assertEqual("INSERT INTO test_version",
                         db._conn.cursor_return.execute_kwargs[4][0][0:24])
        self.assertEqual(2, db._conn.cursor_return.execute_kwargs[4][1][0])
Esempio n. 24
0
def generate_reports():
    """Generate all of the reports."""
    cxn = db.connect()
    now = datetime.now()

    sample_wells = get_wells(cxn)
    plates = get_plates(sample_wells)
    genera = get_genus_coverage(cxn)

    # generate_html_report(now, sample_wells, plates, genera)
    generate_excel_report(cxn, sample_wells, plates, genera)
Esempio n. 25
0
def ingest_non_fabales_data():
    """Read the non-Fabales nodulataion."""
    csv_path = util.NON_FABALES_CSV
    cxn = db.connect()

    data = pd.read_csv(csv_path,
                       header=0,
                       names=['genus', 'nodulation_status', 'nodulation_type'])

    data.genus = data.genus.str.strip()

    create_genbank_loci_table(cxn, data)
Esempio n. 26
0
def main(cfg):
    global api_path
    api_path = cfg.api_base + "/catalog/roles"
    logger.log("Testing API on roles, api path: %s", api_path)
    conn = db.connect(cfg)

    # cleanup test data
    for x in xrange(test_recs_count):
        _delete(conn, _role_name(x + 1))

#db.sql(conn, "delete from roles where class = 'test'")
    db.sql(conn, "commit")

    # ---------------------------------------- Create
    id_recs = _testCreateNew(conn, test_recs_count)
    if not id_recs:
        return False

    if not _testCreateDupName(conn):
        return False

    # ---------------------------------------- Read
    #print "++", id_recs
    db_roles = [Role(_read(conn, id_rec["name"])[0]) for id_rec in id_recs]

    logger.log("running Read")
    for db_role in db_roles:
        if not _testRead(db_role):
            return False

    # ---------------------------------------- Update
    time.sleep(1)  # ensure modified_ts has been promoted
    logger.log("running Update")
    if not _testUpdate(conn, 2, db_roles[1]):
        return False

    if not _testUpdateNonExisted(conn, db_roles[0]):
        return False

    # ---------------------------------------- Delete
    logger.log("running Delete")
    for db_role in db_roles:
        if not _testDelete(conn, db_role):
            return False

    if not _testDeleteNonExisted(conn, db_roles[0]):
        return False

    #rows = db.sql(conn, "select 1+%s", 44)
    #logger.log("rows: %s", rows)
    #db.sql(conn, "commit")
    db.disconnect(conn)
    return True
Esempio n. 27
0
def fill_blast_fasta(blast_db, fasta_path, shard_params):
    """
    Fill the fasta file used as input into blast.

    Use sequences from the sqlite3 DB. We use the shard partitions passed in to
    determine which sequences to get for this shard.
    """
    with db.connect(blast_db) as cxn:
        limit, offset = shard_params

        with open(fasta_path, 'w') as fasta_file:
            for row in db.get_sequences_in_shard(cxn, limit, offset):
                util.write_fasta_record(fasta_file, row[0], row[2], row[1])
Esempio n. 28
0
def ingest_corrales_data():
    """Process the Corrales data."""
    csv_path = util.TEMP_DATA / 'corrales.csv'
    cxn = db.connect()

    google.sheet_to_csv(util.CORRALES_SHEET, csv_path)
    corrales = pd.read_csv(csv_path)
    corrales.corrales_id = corrales.corrales_id.str.lower()
    corrales.image_file = corrales.image_file.apply(util.normalize_file_name)

    create_corrales_data_table(cxn, corrales)

    merge_into_images(cxn)
Esempio n. 29
0
def attach_families():
    """Get the family from the sci_name."""
    df = pd.read_csv(GREENNESS_IN_CSV)
    sql = """ select sci_name, family from taxonomy;"""

    with db.connect() as cxn:
        map_df = pd.read_sql(sql, cxn)

    name_map = map_df.set_index('sci_name')['family'].to_dict()

    df['family'] = df['sci_name'].map(name_map)

    df.to_csv(GREENNESS_OUT_CSV, index=False)
Esempio n. 30
0
def merge_sequencing_sheets():
    """Create rapid sample sheet data table table."""
    cxn = db.connect()

    merged = None
    for sheet in SEQ_METADATA_SHEETS:
        sheet = pd.read_sql(f'SELECT * from {sheet};', cxn)
        if merged is None:
            merged = sheet
        else:
            merged = merged.append(sheet, ignore_index=True)

    merged.to_sql('sequencing_metadata', cxn, if_exists='replace', index=False)
Esempio n. 31
0
def fill_blast_fasta(blast_db, fasta_path, shard_params):
    """
    Fill the fasta file used as input into blast.

    Use sequences from the sqlite3 DB. We use the shard partitions passed in to
    determine which sequences to get for this shard.
    """
    with db.connect(blast_db) as cxn:
        limit, offset = shard_params

        with open(fasta_path, 'w') as fasta_file:
            for row in db.get_sequences_in_shard(cxn, limit, offset):
                util.write_fasta_record(fasta_file, row[0], row[2], row[1])
Esempio n. 32
0
def main(cfg):
    global api_path
    api_path = cfg.api_base + "/admin/data/script_stores"
    logger.log("Testing CRUD operations on script_stores, API path: %s", api_path)
    conn = db.connect(cfg)

    # cleanup test data
    for x in xrange(test_recs_count):
        _delete(conn, "store "+str(x+1))
    db.sql(conn, "commit")

    # ---------------------------------------- Create
    apiStoreIds = _testCreateNew(conn, test_recs_count)
    if not apiStoreIds:
        return False

    if not _testCreateDupName(conn):
        return False

    # ---------------------------------------- Read
    dbStores = [ ScriptStore(_read(conn, apiStoreId["uuid_pk"])[0]) for apiStoreId in apiStoreIds]

    logger.log("running Read")
    for dbStore in dbStores:
        if not _testRead(conn, dbStore):
            return False

    # ---------------------------------------- Update
    time.sleep(1)  # ensure modified_ts is greater
    logger.log("running Update")
    for dbStore in dbStores:
        if not _testUpdate(conn, dbStore):
            return False

    if not _testUpdateNonExisted(conn, dbStores[0]):
        return False

    # ---------------------------------------- Delete
    logger.log("running Delete")
    for dbStore in dbStores:
        if not _testDelete(conn, dbStore):
            return False

    if not _testDeleteNonExisted(conn, dbStores[0]):
        return False

    #rows = db.sql(conn, "select 1+%s", 44)
    #logger.log("rows: %s", rows)
    #db.sql(conn, "commit")
    db.disconnect(conn)
    return True
Esempio n. 33
0
def main(cfg):
    global api_path
    api_path = cfg.api_base + "/catalog/assets"
    logger.log("Testing API on assets, api path: %s", api_path)
    conn = db.connect(cfg)

    # cleanup test data
    for x in xrange(test_rec_count):
        _delete(conn, _asset_name(x + 1))
    db.sql(conn, "commit")

    # ---------------------------------------- Create Asset
    asset_recs = _testCreateAsset(conn, test_rec_count)
    if not asset_recs:
        return False

    if not _testCreateDupAsset(conn):
        return False

    # ---------------------------------------- Read
    #print "++", id_recs
    #asset_recs = [ Role(_read(conn, id_rec["name"])[0]) for id_rec in id_recs ]

    logger.log("running Read")
    for asset_rec in asset_recs:
        if not _testRead(asset_rec):
            return False

    # ---------------------------------------- Update
    time.sleep(1)  # ensure modified_ts has been promoted
    logger.log("running Update")
    if not _testUpdate(conn, 2, asset_recs[1]):
        return False

    if not _testUpdateNonExisted(conn, asset_recs[0]):
        return False

    # ---------------------------------------- Delete
    logger.log("running Delete")
    for asset_rec in asset_recs:
        if not _testDelete(conn, asset_recs):
            return False

    if not _testDeleteNonExisted(conn, asset_recs[0]):
        return False

    #rows = db.sql(conn, "select 1+%s", 44)
    #logger.log("rows: %s", rows)
    #db.sql(conn, "commit")
    db.disconnect(conn)
    return True
Esempio n. 34
0
def ingest_priority_taxa_list():
    """Read the priority taxa list Google sheet."""
    csv_path = util.TEMP_DATA / 'priority_taxa.csv'
    cxn = db.connect()

    google.sheet_to_csv(util.PRIORITY_TAXA_SHEET, csv_path)

    taxa = pd.read_csv(
        csv_path,
        header=0,
        na_filter=False,
        names=['family', 'subclade', 'genus', 'position', 'priority'])

    create_priority_taxa_table(cxn, taxa)
Esempio n. 35
0
def create_fasta_files(args):
    """Convert data in an atram sqlite database into fasta files."""
    if not exists(db.get_db_name(args['blast_db'])):
        sys.exit('Could not find the database.')

    with db.connect(args['blast_db'], check_version=True) as cxn:
        try:
            files = open_fasta_files(args, cxn)

            for rec in db.get_all_sequences(cxn):
                util.write_fasta_record(files[rec[1]], rec[0], rec[2], rec[1])

        finally:
            close_fasta_files(files)
Esempio n. 36
0
    def __init__(self):
        settings = dict(
            template_path=os.path.join(os.path.dirname(__file__), "templates"),
            static_path=os.path.join(os.path.dirname(__file__), "static"),
            xsrf_cookies=True,
            cookie_secret=config.secret,
            autoescape=None,
            jinja2=Environment(loader=FileSystemLoader(
                os.path.join(os.path.dirname(__file__), "templates")), trim_blocks=True),
            login_url="/signin"
        )

        handlers = [
            (r"/", handler.index.IndexHandler),
            (r"/index/(\d+)", handler.index.IndexHandler),
            (r"/weibo/signin", handler.weibo.SigninHandler),
            (r"/weibo/bind", handler.weibo.BindHandler),
            (r"/weibo/callback", handler.weibo.CallbackHandler),
            (r"/weibo/sync", handler.weibo.SyncHandler),
            (r"/twitter/signin", handler.twitter.SigninHandler),
            (r"/twitter/bind", handler.twitter.BindHandler),
            (r"/twitter/callback", handler.twitter.CallbackHandler),
            (r"/twitter/sync", handler.twitter.SyncHandler),
            (r"/feed", handler.rss.FeedHandler),
            (r"/rss/sync", handler.rss.SyncHandler),
            (r"/past", handler.post.PastHandler),
            (r"/past/([0-9]{4}-[0-9]{2}-[0-9]{2})", handler.post.PastHandler),
            (r"/chart", handler.chart.ChartHandler),
            (r"/chart/([1,2][0-9]{3})", handler.chart.ChartHandler),
            (r"/ping", handler.index.PingHandler),
            (r"/post/delete/([1-9]\d+)", handler.admin.DeleteHandler),
            (r"/admin", handler.admin.AdminHandler),
            (r"/signin", handler.admin.SigninHandler),
            (r"/signout", handler.admin.SignoutHandler),
            (r"/admin/user", handler.admin.UserHandler),
            (r"/admin/compose", handler.admin.ComposeHandler),
            (r"/admin/visible", handler.admin.VisibleHandler),
            (r"/admin/account", handler.admin.AccountHandler),
            (r"/([^/]+)", handler.index.SourceHandler),
            (r"/([^/]+)/(\d+)", handler.index.SourceHandler),
            (r".*", handler.index.NotFoundHandler)
        ]

        tornado.web.Application.__init__(self, handlers, **settings)

        self.db = connect(config.mysql)
        self.cmd = Commander(self.db)
        self.data = {}
Esempio n. 37
0
def find_requests_and_send_mail(config):
  os.chdir(os.path.dirname(os.path.realpath(__file__)))
  db = DB.connect(db_file='../db/main.db')
  now = time.time()
  what_we_be_done_with = db['c'].execute('select * from reminders where end_time < %d' % now).fetchall()

  for row in DB.map(what_we_be_done_with, 'reminders', db=db):
    row['link'] = "http://indycast.net/%s/slices/%s_%d" % ( row['station'], time.strftime("%Y%m%d%H%M", time.gmtime(row['start_time'] - row['offset'] * 60)), (row['end_time'] - row['start_time']) / 60)

    if len(row['notes']):
      row['link'] += '/%s_on_%s' % (re.sub('[^\w]', '_', row['notes']).strip('_'), row['station'])

    row['link'] += '.mp3'

    email = do_template(template_file='email_reminder_template.txt', settings=row)
    res = misc.send_email(config=config, who=row['email'], subject=email['subject'], body=email['body'])
    db['c'].execute('delete from reminders where id = %d' % row['id'])
    db['conn'].commit()

  return None
Esempio n. 38
0
def preprocess(args):
    """Build the databases required by atram."""
    log.setup(args['log_file'], args['blast_db'])

    with util.make_temp_dir(
            where=args['temp_dir'],
            prefix='atram_preprocessor_',
            keep=args['keep_temp_dir']) as temp_dir:
        util.update_temp_dir(temp_dir, args)

        with db.connect(args['blast_db'], clean=True) as cxn:
            db.create_metadata_table(cxn)

            db.create_sequences_table(cxn)
            load_seqs(args, cxn)

            log.info('Creating an index for the sequence table')
            db.create_sequences_index(cxn)

            shard_list = assign_seqs_to_shards(cxn, args['shard_count'])

        create_all_blast_shards(args, shard_list)
Esempio n. 39
0
 def test_execute_custom_cursor(self):
     db = self.db()
     db.connect()
     cur = db.cursor()
     db.execute("select 1", cur=cur)
     self.assertEqual("select 1", cur.execute_kwargs[0][0])
Esempio n. 40
0
 def test_verify(self):
     db = self.db()
     db.connect()
     db.verify()
     self.assertEqual("SELECT 1", db._conn.cursor_return.execute_kwargs[0][0])
Esempio n. 41
0
 def test_fetchall(self):
     db = self.db()
     db.connect()
     db.fetchall("SELECT 1")
Esempio n. 42
0
 def test_close(self):
     db = self.db()
     db.connect()
     conn = db._conn
     db.close()
     self.assertTrue(conn.close_kwargs is not None)
Esempio n. 43
0
 def test_close_set_connected(self):
     db = self.db()
     db.connect()
     db.close()
     self.assertFalse(db.connected())
Esempio n. 44
0
File: cloud.py Progetto: EQ4/DRR
def prune_process(lockMap, reindex=False, force=False):
  # This is internal, call prune() directly. This is a normally blocking
  # process that is prepared by prune(), making it easily callable asynchronously 
  # If another prune is running then we just bail
  if not lockMap['prune'].acquire(False) and not force:
    logging.warn("Tried to run another prune whilst one is running. Aborting")
    return True

  # If we are the first process then we need to make sure that the webserver is up before
  # we do this to check to see if we are official
  time.sleep(2)

  pid = misc.change_proc_name("%s-cleanup" % misc.config['callsign'])

  # We want to run the am_i_official here since it could block on a DNS lookup
  misc.am_i_official()

  try:
    register_stream_list(reindex)

  except:
    lockMap['prune'].release()
    return None

  db = DB.connect()

  archive_duration = misc.config['archivedays'] * TS.ONE_DAY_SECOND
  cutoff = TS.unixtime('prune') - archive_duration

  # Remove all slices older than 4 hours.
  slice_cutoff = TS.unixtime('prune') - 0.1667 * TS.ONE_DAY_SECOND

  cloud_cutoff = None
  if misc.config['cloud']:
    cloud_cutoff = TS.unixtime('prune') - misc.config['cloudarchive'] * TS.ONE_DAY_SECOND

  # Put thingies into the cloud.
  count = 0
  for file_name in glob('*/*.mp3'):
    #
    # Depending on many factors this could be running for hours
    # or even days.  We want to make sure this isn't a blarrrghhh
    # zombie process or worse yet, still running and competing with
    # other instances of itself.
    #
    if not misc.manager_is_running():
      lockMap['prune'].release()
      return None

    ctime = os.path.getctime(file_name)

    # print "Looking at ", file_name, ctime, cutoff, archive_duration,  misc.config['archivedays'], misc.am_i_official()
    # We observe the rules set up in the config.
    if file_name.startswith('slices') and ctime < slice_cutoff or ctime < cutoff:
      logging.debug("Prune[remove]: %s (ctime)" % file_name)
      os.unlink(file_name)
      count += 1 

    # We want to make sure we aren't archiving the slices
    elif cloud_cutoff and ctime < cloud_cutoff and not file_name.startswith('slice') and misc.am_i_official():
      logging.debug("Prune[cloud]: %s" % file_name)

      # Only unlink the file if I can successfully put it into the cloud.
      if put(file_name):
        try:
          os.unlink(file_name)

        except:
          logging.debug("Prune[cloud]: Couldn't remove %s" % file_name)

  for file_name in glob('%s/*.gz' % misc.DIR_BACKUPS):
    ctime = os.path.getctime(file_name)

    # We observe the rules set up in the config.
    if ctime < cutoff:
      logging.debug("Prune: %s" % file_name)
      os.unlink(file_name)
      count += 1 

  # The map names are different since there may or may not be a corresponding
  # cloud thingie associated with it.
  db = DB.connect()

  # Don't do this f*****g shit at all because f**k this so hard.
  #logging.info('select name, id from streams where end_unix < date("now", "-%d seconds") or (end_minute - start_minute < 0.05 and start_unix < date("now", "%d seconds"))' % (archive_duration, TS.get_offset() * 60 - 1200))

  unlink_list = db['c'].execute('select name, id from streams where end_unix < date("now", "-%d seconds")' % (archive_duration)).fetchall()

  for file_name_tuple in unlink_list:
    file_name = str(file_name_tuple[0])
    id = file_name_tuple[1]

    logging.debug("Prune[remove]: %s (unlink list)" % file_name)
    # If there's a cloud account at all then we need to unlink the 
    # equivalent mp3 file
    if cloud_cutoff and misc.am_i_official():
      "cloud.";unlink(file_name)

      # After we remove these streams then we delete them from the db.
      db['c'].execute('delete from streams where id = %d' % id)
      db['conn'].commit()

    # now only after we've deleted from the cloud can we delete the local file
    if os.path.exists(file_name):
      os.unlink(file_name)
      count += 1


  logging.info("Found %d files older than %s days." % (count, misc.config['archivedays']))
  lockMap['prune'].release()
Esempio n. 45
0
File: cloud.py Progetto: EQ4/DRR
def find_streams(start_list, duration_min):
  # Given a start week minute this looks for streams in the storage 
  # directory that match it - regardless of duration ... so it may return
  # partial shows results.
  stream_list = []

  if type(start_list) is int:
    start_list = [start_list]

  # Sort nominally - since we have unix time in the name, this should come out
  # as sorted by time for us for free.
  stitch_list = []
  episode_list = []
  db = DB.connect()

  # So we have a start list, we are about to query our database using the start_minute
  # and end_minute field ... to get end_minue we need to make use of our duration.
  #
  # timeline ->
  #
  #          ###################           << Region we want
  # start_sea#ch    end_search #           << Search
  #          V                 V
  # |     |     |     |     |     |     |  << Minute
  #          a     b     b     c
  #
  # so we want 
  #     (a) start_minute < start_search and end_minute >= start_search  ||
  #     (b) start_minute > start_search and end_minute <= end_search  ||
  #     (c) start_minute < end_search and end_minute >= end_search
  #     
  condition_list = []
  for start in start_list:
    end_search = (start + duration_min) % TS.MINUTES_PER_WEEK
    # print start, duration_min, end_search
    condition_list.append('start_minute < %d and end_minute >= %d' % (start, start))
    condition_list.append('start_minute > %d and end_minute >= %d and end_minute <= %d' % (start, start, end_search))
    condition_list.append('start_minute < %d and end_minute >= %d' % (end_search, end_search))

  condition_query = "((%s))" % ') or ('.join(condition_list)

  # see https://github.com/kristopolous/DRR/issues/50 - nah this shit is buggy
  condition_query += " and start_unix < datetime(%d, 'unixepoch', 'localtime')" % (TS.sec_now() - misc.config['cascadetime'] + 3)

  full_query = "select * from streams where %s order by week_number * 10080 + start_minute asc" % condition_query

  entry_list = DB.map(db['c'].execute(full_query).fetchall(), 'streams')

  #logging.info(full_query)
  #logging.info(entry_list)
  # print full_query, len(entry_list)
  # We want to make sure that we break down the stream_list into days.  We can't JUST look at the week
  # number since we permit feed requests for shows which may have multiple days.  Since this is leaky
  # data that we don't keep via our separation of concerns, we use a little hack to figure this out.
  by_episode = []
  episode = []
  cutoff_minute = 0
  current_week = 0

  for entry in entry_list:
    # look at start minute, if it's > 12 * cascade time (by default 3 hours), then we presume this is a new episode.
    if entry['start_minute'] > cutoff_minute or entry['week_number'] != current_week:
      if len(episode):
        by_episode.append(episode)

      episode = []

    cutoff_minute = entry['start_minute'] + (12 * misc.config['cascadetime']) % TS.MINUTES_PER_WEEK
    current_week = entry['week_number']

    # We know by definition that every entry in our stream_list is a valid thing we need
    # to look at.  We just need to make sure we break them down by episode
    episode.append(entry)

  if len(episode):
    by_episode.append(episode)

  #print len(by_episode), condition_query
  # Start the creation of the audio files.
  for episode in by_episode:

    # We blur the test start to a bigger window
    test_start = (episode[0]['start_minute'] / (60 * 4))

    for week_start in start_list:
      # Blur the query start to the same window
      query_start = week_start / (60 * 4)

      # This shouldn't be necessary but let's do it anyway
      if abs(query_start - test_start) <= 1:
        # Under these conditions we can say that this episode
        # can be associated with this particular start time

        # The start_minute is based on the week
        offset_start = week_start - episode[0]['start_minute']
        fname = audio.stream_name(episode, week_start, duration_min)
        print '--name',episode[0]['name'], fname

        # We get the name that it will be and then append that
        stream_list.append(stream_info(fname))

        # print offset_start, duration_min, episode
        episode_list.append((episode, offset_start, duration_min))
        break

  # print stream_list, "\nbreak\n", episode_list, "\nasfdasdf\n"
  return stream_list, episode_list
Esempio n. 46
0
 def test_commit(self):
     db = self.db()
     db.connect()
     db.commit()
     self.assertTrue(db._conn.commit_kwargs is not None)
Esempio n. 47
0
 def test_cursor(self):
     db = self.db()
     db.connect()
     self.assertTrue(db.cursor() is not None)
     self.assertTrue(db._conn.cursor_kwargs is not None)
Esempio n. 48
0
 def test_execute_internal_cursor(self):
     db = self.db()
     db.connect()
     db.execute("select 1")
     self.assertEqual("select 1", db._conn.cursor_return.execute_kwargs[0][0])
Esempio n. 49
0
    else:
      # Don't want any scripts to read this and harvest my email.
      email_to_use = 'kri%s@%soo.com' % ("stopolous", "yah")

      res = misc.send_email(config=mail_config, who=email_to_use, subject=subject, body='<br>'.join(report), sender='Indycast Admin <*****@*****.**>')
      print("Issues found. Sending email to %s." % email_to_use)
      #print('\n'.join(report))
  """

CALLSIGN = 'callsign'
os.chdir(os.path.dirname(os.path.realpath(__file__)))

stderr(OFF)
try:
  db = DB.connect(db_file='../db/main.db')

except:
  db = None

parser = argparse.ArgumentParser()
parser.add_argument("-q", "--query", default=None, help="query to send to the servers (site-map gives all end points)")
parser.add_argument("-s", "--station", default="all", help="station to query (default all)")
parser.add_argument('-l', '--list', action='store_true', help='show stations')
parser.add_argument('-k', '--key', default=None, help='Get a specific key in a json formatted result')
parser.add_argument('-n', '--notrandom', action='store_true', help='do not reandomize order')

mail_config = misc.mail_config(parser)
stderr(ON)

args = parser.parse_args()
Esempio n. 50
0
    def test_connect_set_connected(self):
        db = self.db()
        self.assertFalse(db.connected())

        db.connect()
        self.assertTrue(db.connected())
Esempio n. 51
0
 def test_rollback(self):
     db = self.db()
     db.connect()
     db.rollback()
     self.assertTrue(db._conn.rollback_kwargs is not None)
Esempio n. 52
0
 def execute(self, sql, args, format_input='qmark', paramstyle='pyformat'):
     db = self.db(paramstyle=paramstyle, format_input=format_input)
     db.connect()
     db.execute(sql, args)
     return db._conn.cursor_return.execute_kwargs[0]