93 lines
4.7 KiB
Python
93 lines
4.7 KiB
Python
import pandas, pymysql, json, re, os
|
||
|
||
def makejson(text):
|
||
textlist = text.split("\n")
|
||
return textlist
|
||
|
||
def escapestring(text):
|
||
res = re.sub(r"\'","\\'",text)
|
||
return res
|
||
|
||
def makeOptContent(optgroup,options):
|
||
optlist = options.split("\n")
|
||
optgrplist = optgroup.split("\n")
|
||
res = []
|
||
for i in range(len(optlist)):
|
||
valuelist = optlist[i].split(",")
|
||
optCont = {}
|
||
for j in range(len(optgrplist)):
|
||
optCont[optgrplist[j]] = re.sub(r"\'", "’", valuelist[j])
|
||
#optCont[optgrplist[j]] = valuelist[j]
|
||
res.append(optCont)
|
||
return res
|
||
|
||
def makeOPtPrice(price,optprice):
|
||
opp = optprice.split("\n")
|
||
res = []
|
||
for i in range(len(opp)):
|
||
_opp = int(opp[i]) + int(price)
|
||
res.append(_opp)
|
||
return res
|
||
|
||
def getXlsfiles():
|
||
fpath = "/Users/maddiekorea/Desktop/스마트스토어/"
|
||
xlslist = os.listdir(fpath)
|
||
xlslist.sort()
|
||
for i in range(len(xlslist)):
|
||
xlslist[i] = fpath + xlslist[i]
|
||
return xlslist
|
||
|
||
if __name__ == '__main__':
|
||
files = getXlsfiles()
|
||
for index in range(len(files)):
|
||
df = pandas.read_excel(files[index], header=1, skiprows=[2, 3, 4], usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 18, 19, 20])
|
||
df = df.fillna("")
|
||
for row in df.iterrows():
|
||
try:
|
||
db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
|
||
db='conteenew_catalogue', charset='utf8', unix_socket="/tmp/mysql.sock")
|
||
cursor = db.cursor()
|
||
dispSql = """INSERT INTO `conteenew_catalogue`.`dispPrds` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')
|
||
""".format(row[1]['상품번호'], row[1]['카테고리코드'], row[1]['판매자 상품코드'], row[1]['브랜드'],
|
||
escapestring(row[1]['상품명']), row[1]['상품상태'], row[1]['판매가'], row[1]['부가세'],
|
||
row[1]['재고수량'], row[1]['옵션형태'], row[1]['대표이미지'],
|
||
json.dumps(makejson(row[1]['추가이미지']), ensure_ascii=False))
|
||
cursor.execute(dispSql)
|
||
db.commit()
|
||
db.close()
|
||
if row[1]['옵션형태'] == "조합형":
|
||
optcontent = makeOptContent(row[1]['옵션명'], row[1]['옵션값'])
|
||
optprice = makeOPtPrice(row[1]['판매가'], row[1]['옵션가'])
|
||
stock = makejson(row[1]['옵션 재고수량'])
|
||
for i in range(len(optcontent)):
|
||
try:
|
||
db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
|
||
db='conteenew_catalogue', charset='utf8',
|
||
unix_socket="/tmp/mysql.sock")
|
||
cursor = db.cursor()
|
||
skuSql = """INSERT INTO `conteenew_catalogue`.`skuEntries` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}')
|
||
""".format(row[1]['상품번호'], row[1]['판매자 상품코드'], row[1]['브랜드'],
|
||
escapestring(row[1]['상품명']), json.dumps(optcontent[i], ensure_ascii=False),
|
||
optprice[i], stock[i])
|
||
cursor.execute(skuSql)
|
||
db.commit()
|
||
db.close()
|
||
except:
|
||
print(skuSql)
|
||
else:
|
||
try:
|
||
db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
|
||
db='conteenew_catalogue', charset='utf8', unix_socket="/tmp/mysql.sock")
|
||
cursor = db.cursor()
|
||
skuSql = """INSERT INTO `conteenew_catalogue`.`skuEntries` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}')
|
||
""".format(row[1]['상품번호'], row[1]['판매자 상품코드'], row[1]['브랜드'], escapestring(row[1]['상품명']),
|
||
json.dumps({'원래없음': re.sub(r"\'", "’", row[1]['상품명'])}, ensure_ascii=False),
|
||
row[1]['판매가'], row[1]['재고수량'])
|
||
cursor.execute(skuSql)
|
||
db.commit()
|
||
db.close()
|
||
except:
|
||
print(skuSql)
|
||
except:
|
||
print(dispSql)
|