需求:將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")