spider_baidutongji/db.py

import mysql.connector
import hashlib

db = mysql.connector.connect(
    host="10.8.230.16",
    user="root",
    passwd="kfzs123",
    port=3306,
    database="aws_dev"
)
cursor = db.cursor()


def update_spider_data(goods):
    sql = "update spider_data set attr_leading=%s,attr_sku=%s,img_header=%s,img_desc=%s,status=1 where id=%s"
    val = (goods['attr_leading'], goods['attr_sku'], goods['img_header'], goods['img_desc'], goods['id'])
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, " 条记录被修改")
    return


def update_spider_data_other(goods):
    sql = "update spider_data_other set img_desc=%s,status=4 where id=%s"
    val = (goods['img_desc'], goods['id'])
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, " 条记录被修改")
    return


def delete_spider_data(spider_id):
    sql = "delete from spider_data where id=%s"
    val = (spider_id,)
    cursor.execute(sql, val)
    db.commit()
    print("spider_data_delete")


def get_spider_task():
    sql = "select id,supplier,sku,title,url,category_first,category_second from spider_data where status<>1"
    cursor.execute(sql)
    result = cursor.fetchall()
    return result


def get_update_spider_task():
    sql = "select id,supplier,sku,title,url from spider_data_other where status=5"
    cursor.execute(sql)
    result = cursor.fetchall()
    return result


# todo:这里跟类型有关系 ，注意
def add_shop_goods(goods, desc_list):
    goods_desc = ""
    for img in desc_list:
        goods_desc += "<img src='" + img + "' /><br/>"
    sql = "INSERT INTO shop_goods (goods_no, goods_name,supplier,goods_desc,spider_id) VALUES (%s, %s,%s,%s,%s)"
    val = (goods['goods_no'], goods['goods_name'], goods['supplier'], goods_desc, goods['spider_id'])
    cursor.execute(sql, val)
    db.commit()
    print(goods['goods_name'], "记录插入成功", cursor.lastrowid)
    return cursor.lastrowid


def add_goods_gallery(goods_id, thumb_list):
    val = []
    if len(thumb_list) == 0:
        print("thumb_list_is_null")
        return
    for img_url in thumb_list:
        v = (goods_id, img_url)
        val.append(v)
    sql = "insert into shop_goods_gallery(goods_id,thumb_url)values (%s,%s)"
    cursor.executemany(sql, val)
    db.commit()
    print(goods_id, "记录插入成功", cursor.lastrowid)
    return cursor.lastrowid


# todo:这里跟类别有关系，注意
def add_shop_attribute(attr_name):
    sql = "select  id from shop_attribute where attr_name=%s limit 1"
    val = (attr_name,)
    cursor.execute(sql, val)
    r = cursor.fetchone()
    attr_id = 0
    if r is None:
        sql = "insert into shop_attribute(attr_name)values ('" + attr_name + "')"
        cursor.execute(sql)
        db.commit()
        print(attr_name, "记录插入成功", cursor.lastrowid)
        attr_id = cursor.lastrowid
    else:
        attr_id = r[0]
    return attr_id


def add_shop_goods_attr(goods_id, attr_id, attr_value_list):
    val = []
    if len(attr_value_list) == 0:
        return
    for value in attr_value_list:
        v = (goods_id, attr_id, value)
        val.append(v)

    sql = "insert into shop_goods_attr(goods_id,attr_id,attr_value)values (%s,%s,%s)"
    cursor.executemany(sql, val)
    db.commit()
    print("记录插入成功", cursor.lastrowid)
    return cursor.lastrowid


def add_shop_goods_sku(goods_id, supplier, cat_name):
    sql = "select attr_id,attr_value,id from shop_goods_attr where goods_id=" + str(goods_id)
    cursor.execute(sql)
    result = cursor.fetchall()

    attr_ids_dic = {}
    attr_leading = []
    attr_sku = []
    attr_dic = {}
    for v in result:
        attr_ids_dic[v[0]] = v[1]
        k = str(v[0]) + v[1]
        attr_dic[k] = v[2]
        # goods_attr += str(v[0]) + "|"
        # names += v[1] + "|"
    attr_ids = list(attr_ids_dic.keys())
    supplier_code = get_supplier_code(supplier)
    cat_code = get_cat_code(cat_name)
    parent_sku = str(supplier_code) + str(cat_code) + "00"
    max_sku = get_max_sku(parent_sku)
    if max_sku == 0:
        max_sku = parent_sku + "00000"
    val = []
    sku_no = int(max_sku) + 1

    if len(attr_ids) == 1:  # 只有一种属性
        for v in result:
            d = (goods_id, v[2], v[1], sku_no)
            val.append(d)
            sku_no += 1
    elif len(attr_ids) == 2:  # 两种属性
        attr_leading_id = attr_ids[0]
        attr_sku_id = attr_ids[1]
        for v in result:
            attr_id = v[0]
            attr_name = v[1]

            if attr_id == attr_leading_id:
                attr_leading.append(attr_name)
            if attr_id == attr_sku_id:
                attr_sku.append(attr_name)

        for lead in attr_leading:
            k = str(attr_leading_id) + lead
            id_att_leading = attr_dic[k]
            for j in attr_sku:
                k = str(attr_sku_id) + j
                id_att_sku = attr_dic[k]
                goods_attr = str(id_att_leading) + "|" + str(id_att_sku)
                names = lead + "|" + j
                d = (goods_id, goods_attr, names, sku_no)
                val.append(d)
                sku_no += 1
    elif len(attr_ids) == 0:  # 没有属性
        d = (goods_id, "", "", sku_no)
        val.append(d)

    sql = "insert into shop_goods_sku(goods_id,goods_attr,names,sku_no)values (%s,%s,%s,%s)"
    # val = (goods_id, goods_attr, names, sku_no)
    # print(val)
    cursor.executemany(sql, val)
    db.commit()
    print("记录插入成功", cursor.lastrowid)
    return cursor.lastrowid


def get_supplier_code(supplier_name):
    sql = "select supplier_code from shop_supplier where supplier_name=%s limit 1"
    val = (supplier_name,)
    cursor.execute(sql, val)
    result = cursor.fetchone()
    code = ""
    if result is None:
        sql = "select max(supplier_code) from shop_supplier"
        cursor.execute(sql)
        result = cursor.fetchone()
        max_value = result[0]
        sql = "insert into shop_supplier (supplier_name,supplier_code)value(%s,%s)"
        code = int(max_value) + 1
        val = (supplier_name, code)
        cursor.execute(sql, val)
    else:
        code = result[0]
    return code


def get_cat_code(cat_name):
    if cat_name == "":
        return 0
    cat_id = 0
    sql = "select cat_code from shop_category where cat_name=%s limit 1"
    val = (cat_name,)
    cursor.execute(sql, val)
    result = cursor.fetchone()
    if result is None:
        sql = "select max(cat_code) from shop_category"
        cursor.execute(sql)
        result = cursor.fetchone()
        max_value = result[0]
        cat_id = int(max_value) + 1
        sql = "insert into shop_category(cat_code,cat_name)value(%s,%s)"
        val = (cat_id, cat_name)
        cursor.execute(sql, val)
    else:
        cat_id = result[0]
    return cat_id


def get_max_sku(parent_sku):
    sql = "select sku_no from shop_goods_sku where sku_no like '" + parent_sku + "%'  order by sku_no desc limit 1"
    cursor.execute(sql)
    r = cursor.fetchone()
    sku = 0
    if r is not None:
        sku = r[0]
    return sku


def get_max_spu(supplier, cat_name):
    supplier_code = get_supplier_code(supplier)
    cat_code = get_cat_code(cat_name)
    # parent_sku = str(supplier_code) + str(cat_code) + "0000"
    parent_sku = str(supplier_code) + str(cat_code)
    sql = "select goods_no from shop_goods where goods_no like '" + parent_sku + "%'  order by goods_no desc limit 1"
    cursor.execute(sql)
    r = cursor.fetchone()
    sku = 0
    if r is not None:
        sku = int(r[0])
        sku += 1
    else:
        sku = str(supplier_code) + str(cat_code) + "0000"
        sku = int(sku) + 1
    return sku


def add_spider_other():
    sql = "select sku,url,title,supplier from spider_data_other where sku not in(select goods_no from shop_goods) "
    cursor.execute(sql)
    result = cursor.fetchall()
    # result = cursor.fetchone()
    for r in result:
        print(r)
        sku_no = str(r[0])
        url = r[1]
        goods_name = r[2]
        try:
            s2 = "select sku from spider_data where url='" + url + "'"
            print(s2)
            # return
            cursor.execute(s2)
            r2 = cursor.fetchone()
            sku_old = r2[0]

            s3 = "select id from shop_goods where goods_no='" + sku_old + "'"
            cursor.execute(s3)
            r3 = cursor.fetchone()
            print(r3[0])
            goods_id_old = str(r3[0])

            sql_insert = "insert into shop_goods(goods_no,goods_name,supplier,goods_desc)select " + sku_no + ",'" + goods_name + "',supplier,goods_desc from shop_goods where goods_no=" + sku_old
            print(sql_insert)
            cursor.execute(sql_insert)
            db.commit()
            goods_id = cursor.lastrowid
            # goods_id = 1

            sql_insert = "insert into shop_goods_gallery(goods_id,thumb_url) select " + str(
                goods_id) + ", thumb_url from  shop_goods_gallery where goods_id=" + goods_id_old
            cursor.execute(sql_insert)
            db.commit()
            print(sql_insert)

            sql_insert = "insert into shop_goods_sku(goods_id,goods_attr,names) select " + str(
                goods_id) + ",goods_attr,names from   shop_goods_sku where goods_id=" + goods_id_old
            cursor.execute(sql_insert)
            db.commit()
            print(sql_insert)
        except:
            print("error")
            continue


def update_shop_goods(goods, desc_list):
    goods_desc = ""
    for img in desc_list:
        goods_desc += "<img src='" + img + "' /><br/>"
    sql = "update shop_goods set goods_desc=%s  where goods_no=%s limit 1"
    val = (goods_desc, goods['goods_no'])
    cursor.execute(sql, val)
    db.commit()
    print("更新成功", goods['goods_no'])
    return


def insert_tongji_data(array, type):
    column = "ID,createdate, `XH`,`FWSJ`,`DY`,`LY`,`RKYM`,`SSC`,`FWIP`,`FKBSM`,`FWSC`,`FWYS`,`ZHTL`,`SYCFWSJ`,`FWLX`,`FWPC`,"
    column += "`CZXT`,`WLFWS`,`LLQ`,`YYHJ`,`PMFBL`,`PMYS`,`FBB`,`SJZCC`,`SFZCJA`,`DKSJ`,`TLSC`,`YMDZ`,`type`"

    main_key = str(array[1]) + str(array[2]) + str(array[6]) + str(array[7]) + str(array[23]) + str(array[25])
    id = hashlib.md5(main_key.encode('utf-8')).hexdigest()

    sql = "insert into BO_EU_PTSYQ_TONGJI(" + column + ")values"

    val = "("
    val += "'" + id + "',now(),"
    val_b = ""
    num = range(0, 26)
    for n in num:
        val_b += "'" + str(array[n]) + "',"
    if len(val_b) == 0:
        return
    # val += val_b.strip(",")
    val += val_b
    val += "'" + type + "'"
    val += ")"
    sql += val
    print(sql)

    try:
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        print("出错")