massively import product special

# ~*~ coding:utf-8 ~*~
#csv是一个读取csv文件的lib
import csv
#itertools生成组合和排列的lib
import itertools
#pyodbc是连接ms sql server的lib
import pyodbc
import pymysql
#cnxn是数据库连接  更改数据库连接方式，账号密码
cursor = cnxn.cursor()



#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("D:\\ski\\attribute\\attribute-nz-mn.csv")))
    header = reader[0]
    #遍历数据，并处理， tips：跳过第一行
    for i in reader[1:]:
        sku = i[0]
        product_id = getIdFromSku(sku)
        if product_id == 0:
            print ("error sku %s" % sku)
            continue
        optionname = i[1]
        test(optionname)

        optionid = getoptionIdFromoption(optionname)
        option_id=int(optionid)
        if exist(product_id,option_id):
            pass
        else:
            insert(product_id,option_id)


def exist(product_id,option_id):
    sql = "select * from dbo.Product_SpecificationAttribute_Mapping where ProductId = %d and SpecificationAttributeOptionId = %d" % (product_id,option_id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    if len(rows)==0:
        return False
    else:
        print (rows)


def getoptionIdFromoption(optionname):
    sql="SELECT Id FROM dbo.SpecificationAttributeOption where SpecificationAttributeId= %d and Name='%s'" % (7,optionname)
    cursor.execute(sql)
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0
    print (sql)

def insert(product_id,option_id):
    sql = "insert into dbo.Product_SpecificationAttribute_Mapping (ProductId,SpecificationAttributeOptionId,DisplayOrder,AllowFiltering,ShowOnProductPage) values(%d,%d,%d,%d,%d)" % (product_id,option_id,0,0,1)
    print (sql)
    cursor.execute(sql)
    cnxn.commit()

def getIdFromSku(a):
    sql = "select Id from dbo.Product where Sku='%s' and deleted=0" % a
    cursor.execute(sql)
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0
def test(optionname):
    if find(optionname)==0:
        sql="insert into dbo.SpecificationAttributeOption (Name,SpecificationAttributeId,DisplayOrder) values('%s','7',0)" % optionname
        print (sql)
        cursor.execute(sql)
        cnxn.commit()

def find(optionname):
    sql="select Name FROM dbo.SpecificationAttributeOption where SpecificationAttributeId='7' and Name='%s'" % optionname   
    cursor.execute(sql) 
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0

if __name__ == "__main__":
    process()