bulk add attribute

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

#cursor是数据库浮标
cursor = cnxn.cursor()



#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("c:/Users/Jenny/Desktop/ski/attribute/attribute.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
        options = i[1]
        option_id=int(options)
        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
        return True


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(skuset):
    err = False
    for sku in skuset:
        product_id = getIdFromSku(sku)
        if not product_id:
            print "can't find product id where sku='%s'" % sku
            err = True    

if __name__ == "__main__":
    process()