Pasted

import re
import json
import openpyxl
import requests
 
from lxml import html
 
 
def search_fauna_eu(genus=None, species=None):
    query = []
 
    if genus is not None:
        query.append(genus)
 
    if species is not None:
        query.append(species)
 
    page = requests.get("http://www.fauna-eu.org/cdm_dataportal/search/results/taxon",
        headers={
            'User-Agent': 'Species Finder/0.1 ([email protected])'
        },
        cookies={
            'has_js': '0'
        },
        params={
            'query': ' '.join(query),
            'ws': 'portal/taxon/find',
            'search[doTaxa]': '1',
            'search[doSynonyms]': '',
            'search[doTaxaByCommonNames]': ''
        }
    )
 
    tree = html.document_fromstring(page.text)
    tree.make_links_absolute("http://www.fauna-eu.org/cdm_dataportal/search/results/taxon")
    results = tree.xpath("//a[@class='nameAuthorPart']")
 
    if len(results) == 0:
        page = requests.get("http://www.fauna-eu.org/cdm_dataportal/search/results/taxon",
            headers={
                'User-Agent': 'Species Finder/0.1 ([email protected])'
            },
            cookies={
                'has_js': '0'
            },
            params={
                'query': " AND ".join(query),
                'ws': 'portal/taxon/search',
                'search[doTaxa]': '1',
                'search[doSynonyms]': '1',
                'search[doMisappliedNames]': '1',
                'search[doTaxaByCommonNames]': '1'
            }
        )
 
        tree = html.document_fromstring(page.text)
        tree.make_links_absolute("http://www.fauna-eu.org/cdm_dataportal/search/results/taxon")
 
        results.extend(tree.xpath("//a[@class='nameAuthorPart']"))
 
    for r in results:
        item = r.get('href')
 
        page = requests.get(item,
            headers={
                'User-Agent': 'Species Finder/0.1 ([email protected])'
            },
            cookies={
                'has_js': '0'
            }
        )
 
        tree = html.document_fromstring(page.text)
        order_results = tree.xpath("//ul[@id='classification-breadcrumbs']/li[7]/a")
 
        if len(order_results) > 0:
            return order_results[0].text_content()
 
    return None
 
 
def search_col(genus=None, species=None):
    url = "http://www.catalogueoflife.org/col/search/scientific/"
 
    if genus is not None:
        url += "genus/{}/".format(genus)
 
    if species is not None:
        url += "species/{}/".format(species)
 
    url += "fossil/1/match/1"
 
    page = requests.get(url,
        headers={
            'User-Agent': 'Species Finder/0.1 ([email protected])'
        },
        cookies={
            'aci_language=': 'en',
            'treeExtinct': '1',
            'has_js': '0'
        }
    )
 
    tree = html.document_fromstring(page.text)
    tree.make_links_absolute(url)
    results = tree.xpath("//td[@class='field_header_black']/a")
 
    for r in results:
        item = r.get('href')
 
        page = requests.get(item,
            headers={
                'User-Agent': 'Species Finder/0.1 ([email protected])'
            },
            cookies={
                'aci_language=': 'en',
                'treeExtinct': '1',
                'has_js': '0'
            }
        )
 
        tree = html.document_fromstring(page.text)
        order_results = tree.xpath("//td[preceding-sibling::td[text()='Order']]/a")
 
        if len(order_results) > 0:
            return order_results[0].text_content()
 
    return None
 
 
def search_wikipedia(query):
    search = requests.get("https://en.wikipedia.org/w/api.php",
        headers={
            'User-Agent': 'Species Finder/0.1 ([email protected])'
        },
        params={
            'action': 'query',
            'list': 'search',
            'format': 'json',
            'srinfo': '',
            'srsearch': query
        }
    )
 
    order_regex = re.compile(r'<span class="order"><a.+?>([\w ]+?)</a></span>')
 
    for r in json.loads(search.text)['query']['search']:
        url = "https://en.wikipedia.org/wiki/{}".format(r['title'].replace(' ', '_'))
        page = requests.get(url,
            headers={
                'User-Agent': 'Species Finder/0.1 ([email protected])'
            }
        )
 
        r_match = order_regex.search(page.text)
 
        if r_match is None:
            continue
 
        return r_match.group(1)
 
    return None
 
 
def main():
    temp = openpyxl.load_workbook("/Users/mac/Downloads/part2.xlsx").get_sheet_by_name('Tabelle1')
    coleoptera = [str(r[3].value) for i, r in enumerate(temp.rows) if i > 1]
 
    del temp
 
    wb = openpyxl.load_workbook("test.xlsx")
    ws = wb.get_sheet_by_name('OHNE Subspezies')
 
    print("Worksheets loaded.")
 
    for i, row in enumerate(ws.rows):
        if i == 0:
            continue
 
        wb.save('test.xlsx')
 
        order, match, status, genus, subgenus, species, subspecies, author_year = tuple(c.value for c in row)
 
        if order is None:
            print("{:40}".format(genus + " " + species), end="")
 
            if genus in coleoptera:
                ws['A' + str(i + 1)] = "Coleoptera"
                print("{:20}{}".format("Coleoptera", "Cross-Reference"))
                continue
 
            feu_guess = search_fauna_eu(genus, species)
 
            if feu_guess is not None:
                ws['A' + str(i + 1)] = feu_guess
                print("{:20}{}".format(feu_guess, "Fauna-EU"))
                continue
 
            col_guess = search_col(genus, species)
 
            if col_guess is not None:
                ws['A' + str(i + 1)] = col_guess
                print("{:20}{}".format(col_guess, "Catalogue of Life"))
                continue
 
            wiki_guess = search_wikipedia(genus)
 
            if wiki_guess is not None:
                ws['A' + str(i + 1)] = wiki_guess
                print("{:20}{}".format(wiki_guess, "Wikipedia"))
                continue
 
            print("Unknown - A{}".format(i + 1))
 
main()