from selenium import webdriver
import time 
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import re
import pymysql
from sqlalchemy.exc import IntegrityError#捕获重复主键的异常

def login(username, password, driver=None):
    driver.get('https://login.aliexpress.com/')
    driver.maximize_window()
    name = driver.find_element_by_id('fm-login-id')
    name.send_keys(username)
    name1 = driver.find_element_by_id('fm-login-password')
    name1.send_keys(password)
    submit = driver.find_element_by_class_name('fm-submit')
    time.sleep(1)
    submit.click()
    return driver

def close_win(browser):
    time.sleep(5)
    try:
        closewindow = browser.find_element_by_class_name('next-dialog-close')
        closewindow.click()
    except Exception as e:
        print(f"searchKey: there is no suspond Page1. e = {e}")
    return browser

def get_products(browser):
    wait = WebDriverWait(browser, 1)
    for i in range(30):
        browser.execute_script('window.scrollBy(0,230)')
        time.sleep(1)
        products = wait.until(EC.presence_of_all_elements_located((By.CLASS_NAME,"product-info")))
        if len(products) >= 60:
            break
        else:
            continue
    products = browser.find_elements_by_class_name('product-info')
    return products

def get_pro_info(product, driver):
    url = product.find_element_by_class_name('item-title').get_attribute('href')
    driver.get(url)
    time.sleep(0.5)
    page = driver.page_source
    material=re.findall(r'"skuAttr":".*?#(.*?);',page)
    color=re.findall(r'"skuAttr":".*?#.*?#(.*?)"',page)
    stock=re.findall(r'"skuAttr":".*?"availQuantity":(.*?),',page)
    price=re.findall(r'"skuAttr":".*?"skuCalPrice":"(.*?)"',page)
    pics = re.findall(r'<div class="sku-property-image"><img class="" src="(.*?)"', page)
    titles = re.findall(r'<img class="" src=".*?" title="(.*?)">', page)
    video = re.findall(r'id="item-video" src="(.*?)"', page)
    skuID = re.findall(r'"skuId":(.*?),',page)
    pro_name = re.findall(r'"product-title-text">(.*?)</h1>', page)
    rating = re.findall(r'itemprop="ratingValue">(.*?)</span>', page)
    shipping = re.findall(r'<span class="bold">(.*?)&nbsp;', page)
    review = re.findall(r'"reviewCount">(.*?) Reviews</span>', page)
    #当商品没有颜色可选时，网页源码结构变化，需要重新匹配
    if titles:
        pass
    else:
        material = re.findall(r'"skuAttr":".*?#(.*?)"', page)
        color=[]
        pics = re.findall(r'"imagePathList":["(.*?)",', page)
    return url, pro_name, skuID, material, color, stock, price, pics, titles, video,rating,shipping, review 

def save_data_to_sql(url,pro_name, skuID, material, color, stock, price, pics, titles, video,rating,shipping,review):
    url = re.findall('/item/(.*?).html',url)
#    try:
    conn = pymysql.connect(host='localhost', user='root', password='ab226690',db='crawl')
    mycursor = conn.cursor()
    #写入sku表
    sql = "INSERT INTO SKU(skuID,material,color,stock,price, url) VALUES (%s,%s,%s,%s,%s,%s)"
    for i in range(len(skuID)):
        if titles:
            params = (skuID[i], material[i], color[i], stock[i], price[i],url)
        else:
            params = (skuID[i], material[i], ' ', stock[i], price[i],url)
#        mycursor.execute(sql,params)
#        conn.commit()
        try:
            mycursor.execute(sql,params)
            conn.commit()
        except IntegrityError:
            conn.rollback()
            continue
    #写入img表
    sql = "INSERT INTO image(url, color, img) VALUES (%s,%s,%s)"
    i = 0
    if titles:
        for i in range(len(titles)):
            params = (url, titles[i], pics[i])
#            mycursor.execute(sql,params)
#            conn.commit()
            try:
                mycursor.execute(sql,params)
                conn.commit()
            except IntegrityError:
                conn.rollback()
                continue
    else:
        params = (url, ' ', pics)
#        mycursor.execute(sql,params)
#        conn.commit()
        try:
            mycursor.execute(sql,params)
            conn.commit()
        except IntegrityError:
            conn.rollback()
    #写入product表
    sql = "INSERT INTO product(url, product_name, rating, reviews, video, shipping) VALUES (%s,%s,%s,%s,%s,%s)"

    if rating:
        pass
    else:
        rating = '0.0'

    if review:
        pass
    else:
        review = '0'
    
    if video:
        pass
    else:
        video = ' '
    
    if shipping:
        pass
    else:
        shipping = '0.0'
        
    params = (url, pro_name, rating,review, video, shipping)
    mycursor.execute(sql,params)
    conn.commit()
#    try:
#        mycursor.execute(sql,params)
#        conn.commit()
#    except Exception:
#        conn.rollback()
    conn.close()
#    except Exception as e:
#    conn.rollback()
#    print(e)
        
def scratch_page(source_url):
    browser = webdriver.Chrome()
    browser.get(source_url)
    browser.maximize_window()
    browser = close_win(browser)
    pros = get_products(browser)
    #商品内页的浏览器
    browser2 = webdriver.Chrome()
    error_file = open('ERROR.txt','a+',encoding='utf8')
    for pro in pros:
        url, pro_name, skuID, material, color, stock, price, pics, titles, video,rating,shipping, review = get_pro_info(pro, browser2)
        if len(skuID)!=len(color):
            error_file.write('url:'+url+'n')
            continue
        save_data_to_sql(url,pro_name, skuID, material, color, stock, price, pics, titles, video,rating,shipping,review)
    error_file.close()
    browser.close()
    browser2.close()
    
url = 'https://www.aliexpress.com/wholesale?trafficChannel=main&d=y&SearchText=cartoon+case&ltype=wholesale&SortType=default&page='
for p in range(1,11):
    url_ = url + str(p)
    start_time = time.time()
    scratch_page(url_)
    end_time = time.time()
    print('成功爬取' + str(p) + '页')
    print('第' + str(p) + '页耗时： '+str(start_time-end_time)+'s')