Scraper e importatore per OpenCart: versioni, soluzioni pronte e conversione di valuta

pip install requests beautifulsoup4 pymysql lxml

import requests
import xml.etree.ElementTree as ET
from datetime import date

class CurrencyConverter:
    """Ottiene il tasso di riferimento della BCE e converte il prezzo del fornitore nella valuta del negozio."""

    ECB_URL = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"

    def __init__(self, source_currency="USD", markup=1.20):
        # markup=1.20 — ricarico del negozio: +20% sul tasso di cambio
        self.source_currency = source_currency
        self.markup = markup
        self._rate = None
        self._rate_date = None

    def _fetch_rate(self):
        """Cerca nell'XML della BCE il tasso della valuta rispetto all'euro."""
        resp = requests.get(self.ECB_URL, timeout=15)
        tree = ET.fromstring(resp.text)

        for cube in tree.iter():
            if cube.get("currency") == self.source_currency:
                return float(cube.get("rate"))  # unità di valuta per 1 EUR

        raise ValueError(f"Valuta {self.source_currency} non trovata nella risposta della BCE")

    @property
    def rate(self):
        # mettiamo in cache il tasso per la data corrente
        if self._rate is None or self._rate_date != date.today():
            self._rate = self._fetch_rate()
            self._rate_date = date.today()
        return self._rate

    def convert(self, amount):
        """Prezzo del fornitore -> prezzo del negozio (in euro) con il ricarico."""
        if amount is None:
            return None
        price = float(amount) / self.rate * self.markup
        return round(price, 2)

import time
import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin

class SupplierParser:
    def __init__(self, base_url, converter, delay=1.0):
        self.base_url = base_url
        self.converter = converter
        self.delay = delay  # pausa tra le richieste per non sovraccaricare il sito di origine
        self.session = requests.Session()
        self.session.headers.update({
            "User-Agent": "Mozilla/5.0 (compatible; CatalogImporter/1.0)"
        })

    def _get_soup(self, url):
        resp = self.session.get(url, timeout=20)
        resp.raise_for_status()
        return BeautifulSoup(resp.text, "lxml")

    def parse_catalog(self, catalog_path, max_pages=5):
        """Raccoglie i link ai prodotti dalle pagine del catalogo con la paginazione."""
        product_urls = []
        for page in range(1, max_pages + 1):
            url = urljoin(self.base_url, f"{catalog_path}?page={page}")
            soup = self._get_soup(url)
            cards = soup.select(".product-card a.product-link")
            if not cards:
                break  # non ci sono altre pagine
            for a in cards:
                product_urls.append(urljoin(self.base_url, a["href"]))
            time.sleep(self.delay)
        return product_urls

    def parse_product(self, url):
        """Elabora una singola scheda prodotto."""
        soup = self._get_soup(url)

        name = soup.select_one("h1.product-title")
        price_raw = soup.select_one(".price .value")
        description = soup.select_one(".product-description")
        sku = soup.select_one(".sku")
        image = soup.select_one(".product-gallery img")

        # puliamo il prezzo da spazi e simboli di valuta
        price_source = None
        if price_raw:
            digits = "".join(ch for ch in price_raw.text if ch.isdigit() or ch == ".")
            price_source = float(digits) if digits else None

        time.sleep(self.delay)

        return {
            "name": name.text.strip() if name else "Senza nome",
            "sku": sku.text.strip() if sku else "",
            "description": description.decode_contents().strip() if description else "",
            "image_url": urljoin(self.base_url, image["src"]) if image else "",
            "price_source": price_source,
            # è qui che avviene la conversione di valuta:
            "price": self.converter.convert(price_source),
            "source_url": url,
        }

import pymysql
from datetime import datetime
from slugify import slugify  # pip install python-slugify

class OpenCartImporter:
    def __init__(self, db_config, prefix="oc_", store_id=0, language_id=1):
        self.conn = pymysql.connect(**db_config, charset="utf8mb4",
                                    cursorclass=pymysql.cursors.DictCursor)
        self.prefix = prefix
        self.store_id = store_id
        self.language_id = language_id

    def _seo_keyword(self, name):
        return slugify(name) or "product"

    def import_product(self, p, category_id=None):
        cur = self.conn.cursor()
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        px = self.prefix

        # 1) tabella principale del prodotto
        cur.execute(f"""
            INSERT INTO {px}product
                (model, sku, quantity, price, image, status,
                 date_added, date_modified, date_available)
            VALUES (%s, %s, %s, %s, %s, 1, %s, %s, %s)
        """, (p["sku"] or p["name"][:64], p["sku"], 100,
              p["price"], "", now, now, now))
        product_id = cur.lastrowid

        # 2) descrizione (nome, testo, meta) — per ogni lingua
        cur.execute(f"""
            INSERT INTO {px}product_description
                (product_id, language_id, name, description,
                 meta_title, meta_description)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (product_id, self.language_id, p["name"],
              p["description"], p["name"], p["name"]))

        # 3) collegamento al negozio
        cur.execute(f"""
            INSERT INTO {px}product_to_store (product_id, store_id)
            VALUES (%s, %s)
        """, (product_id, self.store_id))

        # 4) collegamento alla categoria (se indicata)
        if category_id:
            cur.execute(f"""
                INSERT INTO {px}product_to_category (product_id, category_id)
                VALUES (%s, %s)
            """, (product_id, category_id))

        # 5) SEO URL (URL parlante)
        keyword = f"{self._seo_keyword(p['name'])}-{product_id}"
        # --- OpenCart 3.x: tabella url_alias ---
        cur.execute(f"""
            INSERT INTO {px}url_alias (query, keyword)
            VALUES (%s, %s)
        """, (f"product_id={product_id}", keyword))
        # --- In OpenCart 4.x sarebbe così (tabella seo_url):
        # cur.execute(f'''INSERT INTO {px}seo_url
        #   (store_id, language_id, key, value, keyword)
        #   VALUES (%s, %s, "product_id", %s, %s)''',
        #   (self.store_id, self.language_id, product_id, keyword))

        self.conn.commit()
        return product_id

    def close(self):
        self.conn.close()

def main():
    converter = CurrencyConverter(source_currency="USD", markup=1.25)
    parser = SupplierParser("https://supplier-example.com", converter, delay=1.0)

    importer = OpenCartImporter(
        db_config={
            "host": "localhost",
            "user": "db_user",
            "password": "db_password",
            "database": "opencart_db",
        },
        prefix="oc_",
        store_id=0,
        language_id=1,
    )

    product_urls = parser.parse_catalog("/catalog/category-1", max_pages=3)
    print(f"Prodotti trovati: {len(product_urls)}")

    imported = 0
    for url in product_urls:
        try:
            product = parser.parse_product(url)
            if product["price"] is None:
                print(f"Saltato (senza prezzo): {url}")
                continue
            pid = importer.import_product(product, category_id=20)
            imported += 1
            print(f"[{imported}] {product['name']} — "
                  f"{product['price_source']} {converter.source_currency} "
                  f"-> {product['price']} €  (ID {pid})")
        except Exception as e:
            print(f"Errore su {url}: {e}")

    importer.close()
    print(f"Fatto. Importati: {imported}")

if __name__ == "__main__":
    main()

<?php
class CurrencyConverter
{
    private const ECB_URL = 'https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml';

    private ?float $rate = null;
    private ?string $rateDate = null;

    public function __construct(
        private string $sourceCurrency = 'USD',
        private float $markup = 1.20  // ricarico del negozio: +20% sul tasso di cambio
    ) {}

    private function fetchRate(): float
    {
        $xmlRaw = file_get_contents(self::ECB_URL);

        $xml = new SimpleXMLElement($xmlRaw);
        $xml->registerXPathNamespace('e', 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref');

        foreach ($xml->xpath('//e:Cube[@currency]') as $cube) {
            if ((string)$cube['currency'] === $this->sourceCurrency) {
                return (float)$cube['rate']; // unità di valuta per 1 EUR
            }
        }
        throw new RuntimeException("Valuta {$this->sourceCurrency} non trovata nella risposta della BCE");
    }

    public function getRate(): float
    {
        $today = date('Y-m-d');
        if ($this->rate === null || $this->rateDate !== $today) {
            $this->rate = $this->fetchRate();
            $this->rateDate = $today;
        }
        return $this->rate;
    }

    public function convert(?float $amount): ?float
    {
        if ($amount === null) {
            return null;
        }
        return round($amount / $this->getRate() * $this->markup, 2);
    }
}

<?php
class SupplierParser
{
    public function __construct(
        private string $baseUrl,
        private CurrencyConverter $converter,
        private float $delay = 1.0  // pausa tra le richieste, in secondi
    ) {}

    private function getHtml(string $url): string
    {
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_TIMEOUT        => 20,
            CURLOPT_USERAGENT      => 'Mozilla/5.0 (compatible; CatalogImporter/1.0)',
        ]);
        $html = curl_exec($ch);
        if ($html === false) {
            throw new RuntimeException('cURL: ' . curl_error($ch));
        }
        curl_close($ch);
        return $html;
    }

    private function makeXPath(string $html): DOMXPath
    {
        $doc = new DOMDocument();
        libxml_use_internal_errors(true); // silenziamo gli errori dell'HTML «sporco»
        $doc->loadHTML('<?xml encoding="UTF-8">' . $html);
        libxml_clear_errors();
        return new DOMXPath($doc);
    }

    private function absolute(string $href): string
    {
        return str_starts_with($href, 'http') ? $href : rtrim($this->baseUrl, '/') . '/' . ltrim($href, '/');
    }

    /** Raccoglie i link ai prodotti dalle pagine del catalogo con la paginazione. */
    public function parseCatalog(string $catalogPath, int $maxPages = 5): array
    {
        $urls = [];
        for ($page = 1; $page <= $maxPages; $page++) {
            $html = $this->getHtml($this->absolute("{$catalogPath}?page={$page}"));
            $xpath = $this->makeXPath($html);
            $links = $xpath->query("//div[contains(@class,'product-card')]//a[contains(@class,'product-link')]");

            if ($links->length === 0) {
                break; // non ci sono altre pagine
            }
            foreach ($links as $a) {
                $urls[] = $this->absolute($a->getAttribute('href'));
            }
            usleep((int)($this->delay * 1_000_000));
        }
        return $urls;
    }

    /** Elabora una singola scheda prodotto. */
    public function parseProduct(string $url): array
    {
        $xpath = $this->makeXPath($this->getHtml($url));

        $text = fn(string $q) => trim($xpath->query($q)->item(0)?->textContent ?? '');

        $name        = $text("//h1[contains(@class,'product-title')]") ?: 'Senza nome';
        $sku         = $text("//*[contains(@class,'sku')]");
        $description = $text("//*[contains(@class,'product-description')]");
        $priceRaw    = $text("//*[contains(@class,'price')]//*[contains(@class,'value')]");

        $imgNode = $xpath->query("//*[contains(@class,'product-gallery')]//img")->item(0);
        $imageUrl = $imgNode ? $this->absolute($imgNode->getAttribute('src')) : '';

        // puliamo il prezzo da spazi e simboli di valuta
        $priceSource = null;
        if ($priceRaw !== '') {
            $digits = preg_replace('/[^0-9.]/', '', str_replace(',', '.', $priceRaw));
            $priceSource = $digits !== '' ? (float)$digits : null;
        }

        usleep((int)($this->delay * 1_000_000));

        return [
            'name'         => $name,
            'sku'          => $sku,
            'description'  => $description,
            'image_url'    => $imageUrl,
            'price_source' => $priceSource,
            // conversione di valuta:
            'price'        => $this->converter->convert($priceSource),
            'source_url'   => $url,
        ];
    }
}

<?php
class OpenCartImporter
{
    private PDO $pdo;

    public function __construct(
        array $dbConfig,
        private string $prefix = 'oc_',
        private int $storeId = 0,
        private int $languageId = 1
    ) {
        $dsn = "mysql:host={$dbConfig['host']};dbname={$dbConfig['database']};charset=utf8mb4";
        $this->pdo = new PDO($dsn, $dbConfig['user'], $dbConfig['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]);
    }

    private function seoKeyword(string $name): string
    {
        // generazione semplificata dell'URL parlante (senza accenti né caratteri speciali)
        $slug = mb_strtolower(trim($name));
        $slug = strtr($slug, ['à' => 'a', 'è' => 'e', 'é' => 'e', 'ì' => 'i', 'ò' => 'o', 'ù' => 'u']);
        $slug = preg_replace('/[^a-z0-9\s-]/u', '', $slug);
        $slug = preg_replace('/\s+/u', '-', $slug);
        return $slug !== '' ? $slug : 'product';
    }

    public function importProduct(array $p, ?int $categoryId = null): int
    {
        $px = $this->prefix;
        $now = date('Y-m-d H:i:s');

        // 1) tabella principale del prodotto
        $stmt = $this->pdo->prepare("
            INSERT INTO {$px}product
                (model, sku, quantity, price, image, status,
                 date_added, date_modified, date_available)
            VALUES (:model, :sku, 100, :price, '', 1, :added, :modified, :available)
        ");
        $stmt->execute([
            ':model'     => $p['sku'] ?: mb_substr($p['name'], 0, 64),
            ':sku'       => $p['sku'],
            ':price'     => $p['price'],
            ':added'     => $now,
            ':modified'  => $now,
            ':available' => $now,
        ]);
        $productId = (int)$this->pdo->lastInsertId();

        // 2) descrizione (nome, testo, meta)
        $this->pdo->prepare("
            INSERT INTO {$px}product_description
                (product_id, language_id, name, description, meta_title, meta_description)
            VALUES (?, ?, ?, ?, ?, ?)
        ")->execute([
            $productId, $this->languageId, $p['name'],
            $p['description'], $p['name'], $p['name'],
        ]);

        // 3) collegamento al negozio
        $this->pdo->prepare("
            INSERT INTO {$px}product_to_store (product_id, store_id) VALUES (?, ?)
        ")->execute([$productId, $this->storeId]);

        // 4) collegamento alla categoria
        if ($categoryId !== null) {
            $this->pdo->prepare("
                INSERT INTO {$px}product_to_category (product_id, category_id) VALUES (?, ?)
            ")->execute([$productId, $categoryId]);
        }

        // 5) SEO URL (URL parlante)
        $keyword = $this->seoKeyword($p['name']) . '-' . $productId;
        // --- OpenCart 3.x: tabella url_alias ---
        $this->pdo->prepare("
            INSERT INTO {$px}url_alias (query, keyword) VALUES (?, ?)
        ")->execute(["product_id={$productId}", $keyword]);
        // --- In OpenCart 4.x sarebbe così (tabella seo_url):
        // INSERT INTO {$px}seo_url (store_id, language_id, `key`, `value`, keyword)
        //   VALUES (:store, :lang, 'product_id', :pid, :keyword)

        return $productId;
    }
}

<?php
require 'CurrencyConverter.php';
require 'SupplierParser.php';
require 'OpenCartImporter.php';

$converter = new CurrencyConverter('USD', 1.25);
$parser    = new SupplierParser('https://supplier-example.com', $converter, 1.0);

// le credenziali possono stare in un config a parte o essere prese dal config.php di OpenCart
$importer = new OpenCartImporter([
    'host'     => 'localhost',
    'user'     => 'db_user',
    'password' => 'db_password',
    'database' => 'opencart_db',
], prefix: 'oc_', storeId: 0, languageId: 1);

$urls = $parser->parseCatalog('/catalog/category-1', maxPages: 3);
echo 'Prodotti trovati: ' . count($urls) . PHP_EOL;

$imported = 0;
foreach ($urls as $url) {
    try {
        $product = $parser->parseProduct($url);
        if ($product['price'] === null) {
            echo "Saltato (senza prezzo): {$url}" . PHP_EOL;
            continue;
        }
        $pid = $importer->importProduct($product, categoryId: 20);
        $imported++;
        printf("[%d] %s — %s USD -> %s €  (ID %d)%s",
            $imported, $product['name'], $product['price_source'], $product['price'], $pid, PHP_EOL);
    } catch (Throwable $e) {
        echo "Errore su {$url}: {$e->getMessage()}" . PHP_EOL;
    }
}

echo "Fatto. Importati: {$imported}" . PHP_EOL;

# ogni giorno alle 4:00 sincronizziamo il catalogo con il fornitore
0 4 * * * /usr/bin/php /var/www/opencart/parser/run.php >> /var/www/opencart/parser/parser.log 2>&1