<commit_before># Imports nexson files from the OpenTree API into a postgres database
#   uses the phylesystem API via the peyotl library
# Assumes database already set up. This happends either in initializedb.py
#   if running pyramid app or setup_db.py for testing (both in
#   this same directory)

import datetime as dt
import argparse
import psycopg2 as psy
import csv
import yaml

# other database functions
import setup_db

# peyotl functions for handling the taxonomy
import peyotl.ott as ott

def load_taxonomy(connection,cursor,otttable,syntable,path):
    taxonomy = ott.OTT(ott_loc)
    # get dictionary of ottids:ottnames, noting that the names can be strings
    # or tuples, e.g. (canonical name,synonym,synonym)
    ott_names = taxonomy.ott_id_to_names
    # dictionary of ottid:parent_ottid
    ott_parents = taxonomy.ott_id2par_ott_id

    ott_filename = "ott.csv"
    synonym_filename = "synonyms.csv"

    with open(ott_filename,'w') as of, open(synonym_filename,'w') as sf:
        # header row for ott file
        ottwriter = csv.writer(of)
        ottwriter.writerow(('ott_id','name','parent_id'))
        # header row for synonym file
        synwriter = csv.writer(sf)
        synwriter.writerow(('ott_id','synonym'))

        for ott_id in ott_names:
            name = ott_names[ott_id]
            synonyms=[]
            # if names is a tuple, then the first element is the unique name
            # and the others are synonyms
            if (isinstance(name,tuple)):
                synonyms = name[1:]
                name = name[0]
            parent_id = ott_parents[ott_id]

            # insert into taxonomy table
            sqlstring = ("INSERT INTO {t} "
                "(ott_id,ott_name,parent) "
                "VALUES (%s,%s,%s);"
                .format(t=otttable)
                )
            data = (ott_id,name,parent_id)
            ottwriter.writerow((ott_id,name,parent_id))
            #print '  SQL: ',cursor.mogrify(sqlstring,data)
            cursor.execute(sqlstring,data)

            # insert into synonym table
            for s in synonyms:
                sqlstring = ("INSERT INTO {t} "
                    "(ott_id,synonym) "
                    "VALUES (%s,%s);"
                    .format(t=syntable)
                    )
                data = (ott_id,s)
                synwriter.writerow((ott_id,s))
                #print '  SQL: ',cursor.mogrify(sqlstring,data)
                cursor.execute(sqlstring,data)
    ottwriter.close()
    synwriter.close()
    connection.commit()

if __name__ == "__main__":
    # get command line argument (nstudies to import)
    parser = argparse.ArgumentParser(description='load ott into postgres')
    parser.add_argument('configfile',
        help='path to the config file'
        )
    args = parser.parse_args()

    # read config variables
    config_dict={}
    with open(args.configfile,'r') as f:
        config_dict = yaml.safe_load(f)

    connection, cursor = setup_db.connect(config_dict)

    # test that table exists
    # and clear data
    try:
        TAXONOMYTABLE = config_dict['tables']['otttable']
        SYNONYMTABLE = config_dict['tables']['synonymtable']
        if not setup_db.table_exists(cursor,TAXONOMYTABLE):
            raise psy.ProgrammingError("Table {t} does not exist".format(t=TAXONOMYTABLE))
        if not setup_db.table_exists(cursor,SYNONYMTABLE):
            raise psy.ProgrammingError("Table {t} does not exist".format(t=SYNONYMTABLE))
        setup_db.clear_single_table(connection,cursor,TAXONOMYTABLE)
        setup_db.clear_single_table(connection,cursor,SYNONYMTABLE)
        print "done clearing tables"
    except psy.Error as e:
        print e.pgerror

    try:
        ott_loc = config_dict['taxonomy']
        if ott_loc == 'None':
            print 'No taxonomy'
        else:
            # data import
            starttime = dt.datetime.now()
            print "Loading taxonomy"
            load_taxonomy(connection,cursor,TAXONOMYTABLE,SYNONYMTABLE,ott_loc)
            endtime = dt.datetime.now()
            print "OTT load time: ",endtime - starttime
    except psy.Error as e:
        print e.pgerror
    connection.close()<commit_msg>replace inserts with copy<commit_after># Imports nexson files from the OpenTree API into a postgres database
#   uses the phylesystem API via the peyotl library
# Assumes database already set up. This happends either in initializedb.py
#   if running pyramid app or setup_db.py for testing (both in
#   this same directory)

import datetime as dt
import argparse
import psycopg2 as psy
import csv
import yaml
import io

# other database functions
import setup_db

# peyotl functions for handling the taxonomy
import peyotl.ott as ott

def load_taxonomy_using_copy(connection,cursor,otttable,syntable,path):
    taxonomy = ott.OTT(ott_loc)
    # get dictionary of ottids:ottnames, noting that the names can be strings
    # or tuples, e.g. (canonical name,synonym,synonym)
    ott_names = taxonomy.ott_id_to_names
    # dictionary of ottid:parent_ottid
    ott_parents = taxonomy.ott_id2par_ott_id

    ott_filename = "ott.csv"
    synonym_filename = "synonyms.csv"

    with io.open(ott_filename,'w',encoding='utf-8') as of, io.open(synonym_filename,'w',encoding='utf-8') as sf:
        # header row for ott file
        of.write(u'ott_id,name,parent_id')
        # header row for synonym file
        sf.write(u'ott_id,synonym')

        for ott_id in ott_names:
            name = ott_names[ott_id]
            synonyms=[]
            # if names is a tuple, then the first element is the unique name
            # and the others are synonyms
            if (isinstance(name,tuple)):
                synonyms = name[1:]
                name = name[0]
            parent_id = ott_parents[ott_id]

            of.write(u'{},{},{}'.format(ott_id,name,parent_id))

            # insert into synonym table
            for s in synonyms:
                sf.write(u'{},{}'.format(ott_id,s))
    of.close()
    sf.close()

    # import the csv files into the tables
    setup_db.import_csv_file(connection,cursor,otttable,ott_filename)
    setup_db.import_csv_file(connection,cursor,syntable,synonym_filename)

if __name__ == "__main__":
    # get command line argument (nstudies to import)
    parser = argparse.ArgumentParser(description='load ott into postgres')
    parser.add_argument('configfile',
        help='path to the config file'
        )
    args = parser.parse_args()

    # read config variables
    config_dict={}
    with open(args.configfile,'r') as f:
        config_dict = yaml.safe_load(f)

    connection, cursor = setup_db.connect(config_dict)

    # test that table exists
    # and clear data
    try:
        TAXONOMYTABLE = config_dict['tables']['otttable']
        SYNONYMTABLE = config_dict['tables']['synonymtable']
        if not setup_db.table_exists(cursor,TAXONOMYTABLE):
            raise psy.ProgrammingError("Table {t} does not exist".format(t=TAXONOMYTABLE))
        if not setup_db.table_exists(cursor,SYNONYMTABLE):
            raise psy.ProgrammingError("Table {t} does not exist".format(t=SYNONYMTABLE))
        setup_db.clear_single_table(connection,cursor,TAXONOMYTABLE)
        setup_db.clear_single_table(connection,cursor,SYNONYMTABLE)
        print "done clearing tables"
    except psy.Error as e:
        print e.pgerror

    try:
        ott_loc = config_dict['taxonomy']
        if ott_loc == 'None':
            print 'No taxonomy'
        else:
            # data import
            starttime = dt.datetime.now()
            print "Loading taxonomy"
            load_taxonomy_using_copy(connection,cursor,TAXONOMYTABLE,SYNONYMTABLE,ott_loc)
            endtime = dt.datetime.now()
            print "OTT load time: ",endtime - starttime
    except psy.Error as e:
        print e.pgerror
    connection.close()
{'fraction_non_alphanumeric': 0.0920183019827148, 'fraction_numerical': 0.0012709710218607015, 'mean_word_length': 2.5735694822888284, 'pattern_counts': {'":': 2, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2747481', 'n_tokens_mistral': 2430, 'n_tokens_neox': 2382, 'n_words': 606}