add special attribute massive

注意事项，%d 占位符是整数型， %s占位符是字符串

#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("D:\\ski\\attribute\\attribute-gc.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]
        print optionname
 #测试看是否存在这样的optionname
        test(optionname)
 #如果存在，取optionid
        optionid = getoptionIdFromoption(optionname)
        print optionid
 #插入optionid和productid
        if exist(product_id,optionid):
            print "exist"
            pass
        else:
            insert(product_id,optionid)


 #是否存在productid和optionid，如果存在，就退出
def exist(product_id,optionid):
    sql = "select * from dbo.Product_SpecificationAttribute_Mapping where ProductId = %d and SpecificationAttributeOptionId = %d" % (product_id,optionid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    if len(rows)==0:
        return False
    else:
        return True

 #通过optionname找出optionid
def getoptionIdFromoption(optionname):
    sql="SELECT Id FROM dbo.SpecificationAttributeOption where SpecificationAttributeId=%d and Name='%s'" % (12,optionname)
    print sql
    cursor.execute(sql)
    row = cursor.fetchall()
    return row[0][0]
 插入optionid和productid   
def insert(product_id,optionid):
    sql = "insert into dbo.Product_SpecificationAttribute_Mapping(ProductId,SpecificationAttributeOptionId,DisplayOrder,AllowFiltering,ShowOnProductPage) values(%d,%d,%d,%d,%d)" % (product_id,optionid,0,0,1)
    print sql
    cursor.execute(sql)
    cnxn.commit()
 #通过sku找出id              
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
#测试optionname是否存在，若不存在，则插入
def test(optionname): 
    if find(optionname) == 0:
        sql="insert into dbo.SpecificationAttributeOption (Name,SpecificationAttributeId,DisplayOrder) values('%s',12,0)" % optionname
        print sql
        cursor.execute(sql)
        cnxn.commit()
#测试optionname是否存在，若不存在，则插入                
def find(optionname): 
    sql="SELECT Id FROM dbo.SpecificationAttributeOption where SpecificationAttributeId=%d and Name='%s'" % (12,optionname)
    cursor.execute(sql) 
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0  

if __name__ == "__main__":
    process()