#amazon web data scraping store in mysql
!pip install requests
!pip install beautifulsoup4
!pip install pymysql
import requests
from bs4 import BeautifulSoup
import pymysql
import time

# Replace with your FreeSQLDatabase.com details
db_config = {
    'host': 'sql12.freesqldatabase.com',     
    'user': 'sql12789519',                     
    'password': 'jFphKt1xiK',             
    'database': 'sql12789519',               
    'port': 3306,
    'charset': 'utf8mb4'
}

def scrape_amazon_product(url):
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/117.0",
        "Accept-Language": "en-US,en;q=0.5"
    }

    response = requests.get(url, headers=headers)

    if response.status_code != 200:
        print("Request failed. Status code:", response.status_code)
        return None

    soup = BeautifulSoup(response.text, 'html.parser')

    title = soup.find(id="productTitle")
    price = soup.find("span", class_="a-price-whole")
    rating = soup.find("span", class_="a-icon-alt")

    product_data = {
        "title": title.get_text(strip=True) if title else "N/A",
        "price": price.get_text(strip=True) if price else "N/A",
        "rating": rating.get_text(strip=True) if rating else "N/A"
    }

    return product_data

def save_to_mysql(data, db_config):
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            create_table_query = """
            CREATE TABLE IF NOT EXISTS amazon_products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255),
                price VARCHAR(20),
                rating VARCHAR(20)
            );
            """
            cursor.execute(create_table_query)

            insert_query = """
            INSERT INTO amazon_products (title, price, rating)
            VALUES (%s, %s, %s);
            """
            cursor.execute(insert_query, (data['title'], data['price'], data['rating']))
            conn.commit()
            print("Data inserted successfully into MySQL!")
    finally:
        conn.close()

# Use any working Amazon product URL (static)
product_url = 'https://amzn.in/d/52GDLCY'  # Boat Airdopes example

product = scrape_amazon_product(product_url)
if product:
    print("Scraped Data:", product)
    save_to_mysql(product, db_config)
else:
    print("Failed to scrape product.")