<commit_before>'''
 Get the Plot IDs harvested!

'''
import util
import sys
import gdata.docs.client
import ConfigParser
import psycopg2

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

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

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

query = gdata.docs.client.DocsQuery(show_collections='false', 
                                    title='Plot Identifiers')
feed = docs_client.GetAllResources(query=query)

translate = {'column': 'col'}

pcursor.execute("""DELETE from plotids""")
removed = pcursor.rowcount
added = 0
for entry in feed:
    if entry.get_resource_type() != 'spreadsheet':
        continue
    spreadsheet = util.Spreadsheet(docs_client, spr_client, entry)
    spreadsheet.get_worksheets()
    worksheet = spreadsheet.worksheets['Sheet 1']
    for entry2 in worksheet.get_list_feed().entry:
        d = entry2.to_dict()
        cols = []
        vals = []
        for key in d.keys():
            vals.append( d[key] )
            cols.append( translate.get(key,key) )
        sql = """INSERT into plotids(%s) VALUES (%s)""" % (",".join(cols),
                                                        ','.join(["%s"]*len(cols)))
        try:
            pcursor.execute(sql, vals)
        except Exception,exp:
            print exp
            print spreadsheet.title
            print cols
            sys.exit()
        added += 1
        
if removed > (added + 10):
    print 'Aborting harvest_plotids due to sz'
    sys.exit()
print "   harvest_plotids %s removed, %s added" % (removed,
                                                             added)
pcursor.close()
pgconn.commit()
pgconn.close()<commit_msg>some changes to database logic<commit_after>'''
 Get the Plot IDs harvested!

'''
import util
import sys
import gdata.docs.client
import ConfigParser
import psycopg2

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

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

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

query = gdata.docs.client.DocsQuery(show_collections='false', 
                                    title='Plot Identifiers')
feed = docs_client.GetAllResources(query=query)

translate = {'column': 'col'}

lookup = {'tillage': 'TIL',
          'rotation': 'ROT',
          'drainage': 'DWM',
          'nitrogen': 'NIT',
          'landscape': 'LND'}

pcursor.execute("""DELETE from plotids""")
removed = pcursor.rowcount
added = 0
for entry in feed:
    if entry.get_resource_type() != 'spreadsheet':
        continue
    spreadsheet = util.Spreadsheet(docs_client, spr_client, entry)
    spreadsheet.get_worksheets()
    worksheet = spreadsheet.worksheets['Sheet 1']
    for entry2 in worksheet.get_list_feed().entry:
        d = entry2.to_dict()
        cols = []
        vals = []
        for key in d.keys():
            v = None
            if d[key] is not None:                
                v = d[key].strip().replace("[", "").replace("]", "").split()[0]
                v = "%s%s" % (lookup.get(key, ''), v)
            if key == 'uniqueid':
                v = v.upper()
            vals.append( v )
            cols.append( translate.get(key,key) )
        sql = """INSERT into plotids(%s) VALUES (%s)""" % (",".join(cols),
                                                        ','.join(["%s"]*len(cols)))
        try:
            pcursor.execute(sql, vals)
        except Exception,exp:
            print exp
            print spreadsheet.title
            print cols
            sys.exit()
        added += 1
        
if removed > (added + 10):
    print 'Aborting harvest_plotids due to sz'
    sys.exit()
print "   harvest_plotids %s removed, %s added" % (removed,
                                                             added)
pcursor.close()
pgconn.commit()
pgconn.close()
{'fraction_non_alphanumeric': 0.12872954764196343, 'fraction_numerical': 0.004571703561116458, 'mean_word_length': 2.1091997008227374, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5194821', 'n_tokens_mistral': 1245, 'n_tokens_neox': 1201, 'n_words': 286}