<commit_before># Copyright [2018-2020] EMBL-European Bioinformatics Institute
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import mysql.connector
from mysql.connector import Error
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint
import unicodedata
import time
import datetime
import argparse
#import traceback

def fetch_db_data(query,database,host,port,user,password):
  try:
    conn = mysql.connector.connect(database=database,
                                   host=host,
                                   port=port,
                                   user=user,
                                   password=password)

    cursor = conn.cursor()
    cursor.execute(query)
 
    rows = cursor.fetchall()
 
  except Error as e:
      print(e)
 
  finally:
    cursor.close()
    conn.close()

  return rows


def update_assembly_sheet(assembly_db_data,meta_db_data,existing_sheet_records,assembly_sheet,gettime,worksheet_name):
  # This method creates a dictionary for both the lists from the db and sheets and makes both
  # dicts key on the versioned GCA (which is unique). Once the dicts are generated keys in the
  # assembly db dict are compared to the keys in the sheets dict. If a key is not in the sheets
  # dict then it is a new entry and gets made into a new row and added to the sheet. If the key
  # is present then some tests are done to see if anything needs updating. Some of these tests
  # could be made generic, but there are some complex cases like when the filters need updating
  # The filters are basically tags for the assemblies that are then used to create the filter
  # views in sheets
  min_contig_n50_filter = 100000
  assembly_db_dict = {}
  existing_sheet_dict = {}
  max_version_dict = {}
  existing_annotations_dict = {}

  # This ordering needs to match the ordering of the query on the assembly db 
  assembly_db_columns = ['subspecies_name','common_name','chain','version','clade','contig_N50','assembly_level','assembly_date','refseq_accession','assembly_name','genome_rep','rnaseq_data', 'genebuilder','progress_status','assembly_group']

  # This ordering needs to match the ordering of the columns on the sheet
  assembly_sheet_columns = ['GCA','Clade','Species name','Common name','Contig N50','Assembly level','Assembly date','Assembly name','RNAseq data','RefSeq accession','Genebuilder','Status','Assembly group','Expected release','Grant','Notes','Filter: Max version','Filter: Genome rep','Filter: N50','Filter: Non-human']
   
  # This makes a dict for the db on the versioned GCA and also makes a dict to track the highest
  # version for a particular GCA (used in filtering later)
  # Note the db has entries that are in unicode in some cases and need to be converted
  for row in assembly_db_data:
    chain = row[assembly_db_columns.index('chain')]
    version = row[assembly_db_columns.index('version')]
    chain.encode('ascii','ignore')
    gca = make_gca(chain,version)

    assembly_db_dict[gca] = row
    if chain in max_version_dict:
      current_max_version = max_version_dict[chain]
      if version > current_max_version:
        max_version_dict[chain] = version
    else:
      max_version_dict[chain] = version

  # This makes an existing annotations dict based on the meta data db. Note that this db only
  # goes back to e80, so there is a small chance that assemblies that were once annotated are not marked
  # as handed over in the filters, but this shouldn't be a problem
  for row in meta_db_data:
    gca = row[0]
    gca.encode('ascii','ignore')
    existing_annotations_dict[gca] = 1
  # This just makes a dict for the sheet based on the versioned GCA
  for row in existing_sheet_records:
    gca = row[0]
    gca.encode('ascii','ignore')

    if(gca == 'GCA'):
      next
    else:
      existing_sheet_dict[gca] = row
  
  # This is where the majority of the work occurs. All assembly GCAs are examined to determined what
  # should be added/updated
  # Note that currently a three second sleep is needed to avoid exhausting the Sheets REST API quota
  for gca in assembly_db_dict:
    # Check that time since last authentication is < 1hr
    # If greater than 1 hr, then re-authenticate
    if(time.time() - gettime > 60* 59):
      print("Re-authenticating API's connection ")
      # use creds to create a client to interact with the Google Drive API
      scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
      creds = ServiceAccountCredentials.from_json_keyfile_name(credentials_path, scope)
      client = gspread.authorize(creds)
      gettime = time.time()

    # Find a workbook by name and open the first sheet
    # Make sure you use the right name here.
    assembly_sheet = client.open(worksheet_name).worksheet("EnsemblAssemblyRegistry")
    assembly_row = assembly_db_dict[gca]
    species_name = assembly_row[assembly_db_columns.index('subspecies_name')]
    common_name = assembly_row[assembly_db_columns.index('common_name')]
    chain = assembly_row[assembly_db_columns.index('chain')]
    chain.encode('ascii','ignore')
    version = assembly_row[assembly_db_columns.index('version')]
    clade = assembly_row[assembly_db_columns.index('clade')]
    contig_N50 = assembly_row[assembly_db_columns.index('contig_N50')]
    assembly_level = assembly_row[assembly_db_columns.index('assembly_level')]
    assembly_date = assembly_row[assembly_db_columns.index('assembly_date')]
    refseq_accession = assembly_row[assembly_db_columns.index('refseq_accession')]<commit_msg>Updated sheets api open method with correct indentation<commit_after># Copyright [2018-2020] EMBL-European Bioinformatics Institute
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import mysql.connector
from mysql.connector import Error
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint
import unicodedata
import time
import datetime
import argparse
#import traceback

def fetch_db_data(query,database,host,port,user,password):
  try:
    conn = mysql.connector.connect(database=database,
                                   host=host,
                                   port=port,
                                   user=user,
                                   password=password)

    cursor = conn.cursor()
    cursor.execute(query)
 
    rows = cursor.fetchall()
 
  except Error as e:
      print(e)
 
  finally:
    cursor.close()
    conn.close()

  return rows


def update_assembly_sheet(assembly_db_data,meta_db_data,existing_sheet_records,assembly_sheet,gettime,worksheet_name):
  # This method creates a dictionary for both the lists from the db and sheets and makes both
  # dicts key on the versioned GCA (which is unique). Once the dicts are generated keys in the
  # assembly db dict are compared to the keys in the sheets dict. If a key is not in the sheets
  # dict then it is a new entry and gets made into a new row and added to the sheet. If the key
  # is present then some tests are done to see if anything needs updating. Some of these tests
  # could be made generic, but there are some complex cases like when the filters need updating
  # The filters are basically tags for the assemblies that are then used to create the filter
  # views in sheets
  min_contig_n50_filter = 100000
  assembly_db_dict = {}
  existing_sheet_dict = {}
  max_version_dict = {}
  existing_annotations_dict = {}

  # This ordering needs to match the ordering of the query on the assembly db 
  assembly_db_columns = ['subspecies_name','common_name','chain','version','clade','contig_N50','assembly_level','assembly_date','refseq_accession','assembly_name','genome_rep','rnaseq_data', 'genebuilder','progress_status','assembly_group']

  # This ordering needs to match the ordering of the columns on the sheet
  assembly_sheet_columns = ['GCA','Clade','Species name','Common name','Contig N50','Assembly level','Assembly date','Assembly name','RNAseq data','RefSeq accession','Genebuilder','Status','Assembly group','Expected release','Grant','Notes','Filter: Max version','Filter: Genome rep','Filter: N50','Filter: Non-human']
   
  # This makes a dict for the db on the versioned GCA and also makes a dict to track the highest
  # version for a particular GCA (used in filtering later)
  # Note the db has entries that are in unicode in some cases and need to be converted
  for row in assembly_db_data:
    chain = row[assembly_db_columns.index('chain')]
    version = row[assembly_db_columns.index('version')]
    chain.encode('ascii','ignore')
    gca = make_gca(chain,version)

    assembly_db_dict[gca] = row
    if chain in max_version_dict:
      current_max_version = max_version_dict[chain]
      if version > current_max_version:
        max_version_dict[chain] = version
    else:
      max_version_dict[chain] = version

  # This makes an existing annotations dict based on the meta data db. Note that this db only
  # goes back to e80, so there is a small chance that assemblies that were once annotated are not marked
  # as handed over in the filters, but this shouldn't be a problem
  for row in meta_db_data:
    gca = row[0]
    gca.encode('ascii','ignore')
    existing_annotations_dict[gca] = 1
  # This just makes a dict for the sheet based on the versioned GCA
  for row in existing_sheet_records:
    gca = row[0]
    gca.encode('ascii','ignore')

    if(gca == 'GCA'):
      next
    else:
      existing_sheet_dict[gca] = row
  
  # This is where the majority of the work occurs. All assembly GCAs are examined to determined what
  # should be added/updated
  # Note that currently a three second sleep is needed to avoid exhausting the Sheets REST API quota
  for gca in assembly_db_dict:
    # Check that time since last authentication is < 1hr
    # If greater than 1 hr, then re-authenticate
    if(time.time() - gettime > 60* 59):
      print("Re-authenticating API's connection ")
      # use creds to create a client to interact with the Google Drive API
      scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
      creds = ServiceAccountCredentials.from_json_keyfile_name(credentials_path, scope)
      client = gspread.authorize(creds)
      gettime = time.time()
      # Find a workbook by name and open the first sheet
      # Make sure you use the right name here.
      assembly_sheet = client.open(worksheet_name).worksheet("EnsemblAssemblyRegistry")
    assembly_row = assembly_db_dict[gca]
    species_name = assembly_row[assembly_db_columns.index('subspecies_name')]
    common_name = assembly_row[assembly_db_columns.index('common_name')]
    chain = assembly_row[assembly_db_columns.index('chain')]
    chain.encode('ascii','ignore')
    version = assembly_row[assembly_db_columns.index('version')]
    clade = assembly_row[assembly_db_columns.index('clade')]
    contig_N50 = assembly_row[assembly_db_columns.index('contig_N50')]
    assembly_level = assembly_row[assembly_db_columns.index('assembly_level')]
    assembly_date = assembly_row[assembly_db_columns.index('assembly_date')]
    refseq_accession = assembly_row[assembly_db_columns.index('refseq_accession')]
{'fraction_non_alphanumeric': 0.08154541718043568, 'fraction_numerical': 0.0069050554870530205, 'mean_word_length': 3.971802206783817, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 4, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3102443', 'n_tokens_mistral': 3493, 'n_tokens_neox': 3333, 'n_words': 1202}