需求:將Excel的數(shù)據(jù)同步到數(shù)據(jù)庫(kù), 由于sql 比較渣渣,于是打算用Python讀取后在update到數(shù)據(jù)庫(kù), 每次使用都要百度, 這次決定把他記下來(lái),繼續(xù)更新我的博客
用到的庫(kù):xlrd
def update_service():
# 文件的路徑
data = xlrd.open_workbook("/Users/yifan/Downloads/服務(wù)評(píng)級(jí).xlsx")
# 指定sheet索引打開(kāi)對(duì)應(yīng)的表
table = data.sheet_by_index(0)
for i in range(table.nrows):
if i > 0 and table.row(i)[1] is not None: # 從第二列開(kāi)始, 并且第二行不為空
print str(table.row_values(i)[0]), str(table.row_values(i)[1])
下面是我操作的完整實(shí)例, 用的flask, 使用的peewee, orm
@common.route("/update_service", methods=["GET"])
def update_service():
ss = Services.select()
#
data = xlrd.open_workbook("/Users/Downloads/服務(wù)評(píng)級(jí).xlsx")
table = data.sheet_by_index(1)
for i in range(table.nrows):
if i > 0 and table.row(i)[1] is not None:
for s in ss:
if s.service_name == str(table.row_values(i)[0]):
s.level = int(table.row_values(i)[1])
s.save()
continue
return response_json(200, "", "ok")
