<commit_before>'''
 Harvest the data in the data management store!
'''
import util
import ConfigParser
import psycopg2

config = ConfigParser.ConfigParser()
config.read('mytokens.cfg')

pgconn = psycopg2.connect(database='sustainablecorn', user='mesonet',
                          host=config.get('database', 'host'))
pcursor = pgconn.cursor()

# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)

spread = util.Spreadsheet(spr_client,
                          config.get('cscap', 'manstore'))

translate = {'date': 'valid'}


TABS = ['Field Operations', 'Management', 'Pesticides']
for sheetkey in TABS:
    current = {}
    found = {}
    if sheetkey == TABS[0]:
        pcursor.execute("""
            SELECT valid, uniqueid, updated, operation, cropyear, oid
            from operations
        """)
    elif sheetkey == 'Management':
        pcursor.execute("""
            SELECT '', uniqueid,    updated, '',       cropyear, oid
            from management
        """)
    else:
        pcursor.execute("""
            SELECT valid, uniqueid, updated, crop, cropyear, oid
            from pesticides
        """)
    for row in pcursor:
        v = row[0]
        if v is not None and not isinstance(v, str):
            v = v.strftime("%-m/%-d/%Y")
        key = "%s,%s,%s,%s,%s" % (v, row[1], row[2], row[3], row[4])
        current[key] = row[5]
    sheet = spread.worksheets[sheetkey]
    table = sheetkey if sheetkey != TABS[0] else 'operations'

    added = 0
    dups = 0
    entries = 0
    for entry in sheet.get_list_feed().entry:
        entries += 1
        d = entry.to_dict()
        # Units row has n/a as the date, so skip it
        if d.get('date') == 'mm/dd/yyyy':
            continue
        key = ('%s,%s,%s,%s,%s'
               ) % (d.get('date', ''), d.get('uniqueid'), d.get('updated'),
                    d.get('operation' if sheetkey == TABS[0] else 'crop',
                          ''), d.get('cropyear'))
        if key in current:
            del(current[key])
            found[key] = True
            dups += 1
            continue
        if key in found:
            print 'DUP: ', key
            continue
        cols = []
        vals = []
        for key in d.keys():
            if key.startswith('gio'):
                continue
            vals.append(d[key])
            cols.append(translate.get(key, key))

        sql = """
            INSERT into %s(%s) VALUES (%s)
            """ % (table, ",".join(cols), ','.join(["%s"]*len(cols)))
        pcursor.execute(sql, vals)
        added += 1

    for key in current.keys():
        pcursor.execute("""DELETE from """+table+""" WHERE oid = %s""",
                        (current[key],))

    print "   harvest_management %16s %4s rows %4s dups %4s add %4s del" % (
        sheetkey, entries, dups, added, len(current))

pcursor.close()
pgconn.commit()
pgconn.close()<commit_msg>[cscap] improve debug printout of harvest script<commit_after>"""Harvest the data in the data management store!"""
import util  # @UnresolvedImport
import ConfigParser
import psycopg2

config = ConfigParser.ConfigParser()
config.read('mytokens.cfg')

pgconn = psycopg2.connect(database='sustainablecorn', user='mesonet',
                          host=config.get('database', 'host'))
pcursor = pgconn.cursor()

# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)

spread = util.Spreadsheet(spr_client,
                          config.get('cscap', 'manstore'))

translate = {'date': 'valid'}


TABS = ['Field Operations', 'Management', 'Pesticides']
for sheetkey in TABS:
    current = {}
    found = {}
    if sheetkey == TABS[0]:
        pcursor.execute("""
            SELECT valid, uniqueid, updated, operation, cropyear, oid
            from operations
        """)
    elif sheetkey == 'Management':
        pcursor.execute("""
            SELECT '', uniqueid,    updated, '',       cropyear, oid
            from management
        """)
    else:
        pcursor.execute("""
            SELECT valid, uniqueid, updated, crop, cropyear, oid
            from pesticides
        """)
    for row in pcursor:
        v = row[0]
        if v is not None and not isinstance(v, str):
            v = v.strftime("%-m/%-d/%Y")
        key = "%s,%s,%s,%s,%s" % (v, row[1], row[2], row[3], row[4])
        current[key] = row[5]
    sheet = spread.worksheets[sheetkey]
    table = sheetkey if sheetkey != TABS[0] else 'operations'

    added = 0
    dups = 0
    entries = 0
    for entry in sheet.get_list_feed().entry:
        entries += 1
        d = entry.to_dict()
        # Units row has n/a as the date, so skip it
        if d.get('date') == 'mm/dd/yyyy':
            continue
        key = ('%s,%s,%s,%s,%s'
               ) % (d.get('date', ''), d.get('uniqueid'), d.get('updated'),
                    d.get('operation' if sheetkey == TABS[0] else 'crop',
                          ''), d.get('cropyear'))
        if key in current:
            del(current[key])
            found[key] = True
            dups += 1
            continue
        if key in found:
            _t = key.split(",")
            print(("harvest_management DUP: %s on %s for %s[%s]"
                   ) % (_t[3], _t[0], _t[1], _t[4]))
            continue
        cols = []
        vals = []
        for key in d.keys():
            if key.startswith('gio'):
                continue
            vals.append(d[key])
            cols.append(translate.get(key, key))

        sql = """
            INSERT into %s(%s) VALUES (%s)
            """ % (table, ",".join(cols), ','.join(["%s"]*len(cols)))
        pcursor.execute(sql, vals)
        added += 1

    for key in current.keys():
        pcursor.execute("""DELETE from """+table+""" WHERE oid = %s""",
                        (current[key],))

    print(("harvest_management %16s rows:%4s dups:%4s add:%4s del:%4s"
           ) % (sheetkey, entries, dups, added, len(current)))

pcursor.close()
pgconn.commit()
pgconn.close()
{'fraction_non_alphanumeric': 0.15400033405712377, 'fraction_numerical': 0.008351428094204109, 'mean_word_length': 1.9468503937007875, 'pattern_counts': {'":': 0, '<': 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': '5507676', 'n_tokens_mistral': 1933, 'n_tokens_neox': 1839, 'n_words': 476}