Fix Prestashop error You have no access to this product – missing default category

PrestaShop Product Category Fixer: Automating Missing Category Links

PrestaShop users often encounter errors like:

“You are not allowed to see this product”

This typically occurs when products are imported using tools like SimpleImport but lack proper category associations. Specifically, the product’s id_category_default is not recorded in the ps_category_product table.

To address this, the ps_category_product_fix.py script, along with the PrestaShopDB helper class, provides a robust solution to identify and resolve such database inconsistencies. Here’s an explanation of the provided scripts and how to use them.

---

How the Scripts Work

1. PrestaShopDB Class:
   • Extracts database credentials from PrestaShop’s PHP configuration file (parameters.php).
   • Provides a method to test the database connection.
2. Fix Script (ps_category_product_fix.py):
   • Connects to the database using PrestaShopDB.
   • Identifies products missing entries in the ps_category_product table.
   • For each missing product, adds the appropriate id_category_default entry into ps_category_product.

---

Key Features of the Scripts

• Dynamic Credential Parsing: Automatically reads PrestaShop’s database credentials from the PHP configuration file, avoiding hardcoding sensitive information.
• Batch Processing: Handles large numbers of products in batches to avoid overwhelming the database or running into performance issues.
• Logging: Logs details about the script’s operations, including the number of records fixed and any issues encountered.
• Progress Display: Uses a progress bar to visualize the script’s execution status.

---

Script Usage

1. Setup

1. Place the Files:
   Save ps_category_product_fix.py and ps_sql.py in the same directory.

2. Edit the Credential Path:
   Update the credentials_file_path in ps_category_product_fix.py with the path to your PrestaShop parameters.php file.

2. Run the Script

From the terminal, execute:

python3 ps_category_product_fix.py

3. Monitor Progress

The script will:
– Display a list of products missing category entries.
– Show a progress bar for the fixing process.
– Log all actions in the logs folder for future reference.

---

Example Logs

Here’s an example log entry for a successful fix operation:

2024-11-29 12:34:56 - ps_category_product_fix - INFO - Found 120 products without category entries.
2024-11-29 12:35:10 - ps_category_product_fix - INFO - Added 120 missing category entries for products.

---

Best Practices

1. Backup Your Database: Always back up your database before running any script that modifies data.
2. Test in a Staging Environment: Verify the script’s behavior on a staging site to ensure it performs as expected.
3. Optimize Imports: Include all necessary categories in your CSV during imports to prevent future issues.

---

Future Enhancements

• Dry Run Option: Add a mode to preview changes without modifying the database.
• Interactive Fixing: Allow users to select specific products for fixing during execution.
• Error Handling: Improve error reporting for scenarios like duplicate keys or database connection failures.

---

With this script, PrestaShop users can quickly resolve category-related inconsistencies and ensure their products display correctly on the frontend. Automating such fixes saves time and reduces the risk of manual errors.

ps_category_product_fix.py

<code>
"""
PrestaShop Category Product Fix Module

This module helps fix the "you have not access to this product" error on PrestaShop.
The error occurs when products are created using import tools and miss related categories,
such as the root category. This script identifies products without category entries and
adds the missing entries to the `ps_category_product` table.

Usage:
    python ps_category_product_fix.py

The script will:
1. Connect to the PrestaShop database using credentials from a PHP configuration file.
2. Identify products in the `ps_product` table that have no entries in the `ps_category_product` table.
3. For each identified product, read the value of `id_category_default` from the `ps_product` table.
4. Create an entry in the `ps_category_product` table with `id_product` and `id_category_default`.
5. Display a progress bar and log the number of insertions made.
"""

import logging
import sys
from datetime import datetime
from pathlib import Path
from typing import Dict, Optional, Tuple

import mysql.connector
from ps_sql import PrestaShopDB
from tqdm import tqdm

# Set up logging
log_dir = Path("logs")
log_dir.mkdir(exist_ok=True)
script_name = Path(__file__).stem

logging.basicConfig(
    level=logging.DEBUG,
    format="%(asctime)s - %(name)s - %(levelname)s - %(message)s",
    handlers=[
        logging.FileHandler(
            log_dir / f'{script_name}_{datetime.now().strftime("%Y%m%d")}.log'
        ),
        logging.StreamHandler(),
    ],
)
logger = logging.getLogger(__name__)

credentials_file_path = r"/var/www/html/www.domain.it/app/config/parameters.php"
ps_db = PrestaShopDB(credentials_file_path)


def find_products_without_category(ps_db: PrestaShopDB):
    query = """
    SELECT p.id_product
    FROM ps_product p
    WHERE p.id_product NOT IN (SELECT cp.id_product FROM ps_category_product cp)
    """
    connection = ps_db.test_connection()
    if connection:
        cursor = connection.cursor()
        cursor.execute(query)
        products_without_category = [row[0] for row in cursor.fetchall()]
        cursor.close()
        connection.close()
        return products_without_category
    else:
        logger.error("Failed to connect to the database.")
        return []


def add_missing_category_entries(
    ps_db: PrestaShopDB, products_without_category, batch_size=5000
):
    connection = ps_db.test_connection()
    if connection:
        cursor = connection.cursor()
        insertion_count = 0
        for i in tqdm(
            range(0, len(products_without_category), batch_size),
            desc="Processing batches",
        ):
            batch = products_without_category[i : i + batch_size]
            for id_product in batch:
                cursor.execute(
                    "SELECT id_category_default FROM ps_product WHERE id_product = %s",
                    (id_product,),
                )
                id_category_default = cursor.fetchone()[0]
                cursor.execute(
                    "INSERT INTO ps_category_product (id_category, id_product) VALUES (%s, %s)",
                    (id_category_default, id_product),
                )
                insertion_count += 1
            connection.commit()
        cursor.close()
        connection.close()
        logger.info(f"Added {insertion_count} missing category entries for products.")
    else:
        logger.error("Failed to connect to the database.")


if __name__ == "__main__":
    products_without_category = find_products_without_category(ps_db)
    if products_without_category:
        print("Products without category:", products_without_category)
        add_missing_category_entries(ps_db, products_without_category)
    else:
        print("All products have categories.")

</code>

ps_sql.py

<code>
"""
PrestaShopDB Module

This module provides a class to handle database connections for PrestaShop using credentials from a PHP configuration file.

Usage:
    from ps_sql import PrestaShopDB

    credentials_file_path = r"/var/www/html/www.domain.it/app/config/parameters.php"
    or
    credentials_file_path = r"D:\FTP\local\www.domain.local\app\config\parameters.php"
    ps_db = PrestaShopDB(credentials_file_path)
    connection = ps_db.test_connection()

    if connection:
        # Use the connection
        pass
"""

import logging
import os
import re
import socket

import mysql.connector

# Create a logger for this module
logger = logging.getLogger(__name__)


class PrestaShopDB:
    def __init__(self, credentials_file_path):
        self.credentials_file_path = credentials_file_path
        self.config = self.get_prestashop_db_parameters()

    def get_prestashop_db_parameters(self):
        config = {}
        with open(self.credentials_file_path, "r") as file:
            content = file.read().replace(" ", "")
            matches = re.findall(r"'(.*?)'=>(.*?)(,|\))", content)
            for key, value, _ in matches:
                if value.startswith("'") and value.endswith("'"):
                    config[key] = value.strip("'")
                elif value == "NULL":
                    config[key] = None
                elif value == "true":
                    config[key] = True
                elif value == "false":
                    config[key] = False
                else:
                    try:
                        config[key] = float(value) if "." in value else int(value)
                    except ValueError:
                        config[key] = value
        return config

    def get_computer_name(self):
        computer_name = os.environ.get("COMPUTERNAME")
        if not computer_name:
            computer_name = os.environ.get("HOSTNAME")
        if not computer_name:
            computer_name = socket.gethostname()
        return computer_name

    def test_connection(self):
        user = self.config.get("database_user")
        pw = self.config.get("database_password")
        host = self.config.get("database_host")
        db = self.config.get("database_name")
        try:
            conn = mysql.connector.connect(
                user=user, password=pw, host=host, database=db
            )
            print("Connection successful")
            return conn
        except Exception as e:
            print("Connection failed")
            print(e)
            return None


def main():
    import argparse

    parser = argparse.ArgumentParser(description="Test PrestaShop database connection.")
    parser.add_argument(
        "credentials_file_path",
        help="Path to the PHP configuration file with database credentials.",
    )
    args = parser.parse_args()

    ps_db = PrestaShopDB(args.credentials_file_path)
    connection = ps_db.test_connection()

    if connection:
        print("Connection successful")
    else:
        print("Connection failed")


if __name__ == "__main__":
    main()

</code>