EAN Code für große Anzahl an Artikel ermitteln

import pandas as pd
import openpyxl
import requests
import time
from bs4 import BeautifulSoup
import urllib3
import json
import axios

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# create a Workbook object for the input file
wb = openpyxl.load_workbook('/import_XLS/product_names.xlsx')  

# get the first worksheet
ws = wb.active

# create a Pandas DataFrame from the worksheet
df = pd.DataFrame(ws.values)

# add column headers (assuming they're in the first row) 
df.columns = df.iloc[0]

# remove the first row (the headers)
df = df[1:]

link_list = []

for index, row in df.iterrows():

    # get the product name from the 'Product Name' column of the row 
    name = row['Product Name']  

    try:
        query = f"{name} site:amazon.com"  
        options = {
            method: 'GET',  
            url: 'https://amazon-web-scraping-api.p.rapidapi.com/products/search',  
            params: {criteria: query, page: '1', countryCode: 'US', languageCode: 'EN'},  
            headers: {
                'X-RapidAPI-Key': '9ea1bc4msh778ba1515dfp1fd33djsn6e142bb10213',  
                'X-RapidAPI-Host': 'amazon-web-scraping-api.p.rapidapi.com'  
            }
        }

        response = axios.request(options)
        data = response.data
        soup = BeautifulSoup(data, 'html.parser')  
        link = soup.find_all('a')[0]['href']  
        link_list.append(link)
    except:
        link_list.append('')  

    time.sleep(10.5)  # to avoid hitting the rate limit of the API

df['Link'] = link_list  

# write DataFrame to a new Excel file
writer = pd.ExcelWriter('/import_XLS/product_names_link.xlsx',  
                        engine='xlsxwriter')  
df.to_excel(writer, index=False)
writer._save()

import pandas as pd
import openpyxl
import time
import re
import requests
from bs4 import BeautifulSoup

# create a Workbook object for the input file
wb = openpyxl.load_workbook('/import_XLS/product_names.xlsx')  

# get the first worksheet
ws = wb.active

# create a Pandas DataFrame from the worksheet
df = pd.DataFrame(ws.values)

# add column headers (assuming they're in the first row) 
df.columns = df.iloc[0]

# remove the first row (the headers)
df = df[1:]

ean_list = []
link_list = []

for index, row in df.iterrows():

    # get the product name from the 'Product Name' column of the row 
    name = row['Product Name']  

    # search with ean-search.org
    url = 'https://www.ean-search.org/perl/ean_search.cgi?q=' + name  
    headers = {'User-Agent': 'Mozilla/5.0'}  
    res = requests.get(url, headers=headers)
    soup = BeautifulSoup(res.text, 'html.parser')  
    ean = soup.find('span', {'class': 'resultEan'})  
    link = soup.find('a', {'class': 'resultUrl'})  

    if ean:
        ean_list.append(ean.text.strip())
    else:
        ean_list.append(None)

    if link:
        link_list.append(link['href'].strip())  
    else:
        link_list.append(None)

    time.sleep(10)  # to avoid hitting the rate limit of the API

# ensure that the ean_list and link_list have the same length as the DataFrame index
for i in range(len(df) - len(ean_list)):
    ean_list.append(None)
    link_list.append(None)

df['EAN'] = ean_list  
df['Link'] = link_list  

# write DataFrame to a new Excel file
writer = pd.ExcelWriter('/import_XLS/product_names_ean.xlsx',  
                        engine='xlsxwriter')  
df.to_excel(writer, index=False)
writer._save()