SQL写入与更新语句优化

# 要写入的字典数据
data_dict = dict(
    fzID=data.get('fzID'), statusCode=data.get('statusCode'), statusName=data.get('statusName'),
    thumb=data.get("thumb"), atArea=data.get("atArea"), buildingName=data.get("buildingName"),
    communityName=data.get("communityName"), areaSize=data.get("areaSize"), startPrice=data.get("startPrice"),
    currentPrice=data.get("currentPrice"), unitPrice=data.get("unitPrice"), buildingType=data.get("buildingType"),
    bTypeName=data.get("bTypeName"), taxesBy=data.get("taxesBy"), taxesName=data.get("taxesName"),
    canLoan=data.get("canLoan"), LoanText=data.get("LoanText"), discountRate=data.get("discountRate"),
    puchaseLimit=data.get("puchaseLimit"), countyName=countyName, startTime=data.get("startTime"),
    endTime=data.get("endTime"), editDate=data.get("editDate"), competePersons=data.get("competePersons"),
    callTimes=data.get("callTimes"), yijialv=data.get("yijialv"), ccode=data.get("ccode"),
    ajklink=data.get("ajklink"), bemetro=data.get("bemetro"), jiaofu=jiaofu
)

# data_dict = {
#     'fzID': '6342304417171',
#     'statusCode': 2,
#     'statusName': '即将开始',
#     'thumb': 'https://img.51paimaifang.com/tb/202012/1810/tb6342304417171c00.jpg',
#     'atArea': '广东、深圳、龙岗',
#     'buildingName': '深圳市龙岗镇盛平村佳盛园D单元复式D803房',
#     'communityName': '育龙庭',
#     'areaSize': 114.09,
#     'startPrice': 252.9696,
#     'currentPrice': 252.9696,
#     'unitPrice': 2.2172810938733,
#     'buildingType': 1,
#     'bTypeName': '住宅',
#     'taxesBy': 1,
#     'taxesName': '税费全包',
#     'canLoan': 1,
#     'LoanText': '一键贷款',
#     'discountRate': 8,
#     'puchaseLimit': 1,
#     'countyName': '龙岗',
#     'startTime': '2021/01/15 10:00:00',
#     'endTime': '2021/01/16 10:00:00',
#     'editDate': '2020/12/18 10:54',
#     'competePersons': 0,
#     'callTimes': 0,
#     'yijialv': -1,
#     'ccode': 'sz-2411051182955',
#     'ajklink': 'https://shenzhen.anjuke.com/community/view/96690',
#     'bemetro': 1,
#     'jiaofu': '交付'
# }

# 写入
insert_house = f"""
    INSERT INTO houselist(fzID, statusCode, statusName, thumb, atArea, buildingName, communityName,
    areaSize, startPrice, currentPrice, unitPrice, buildingType, bTypeName, taxesBy, taxesName,
    canLoan, LoanText, discountRate, puchaseLimit, countyName, startTime, endTime, editDate,
    competePersons, callTimes, yijialv, ccode, ajklink, bemetro, jiaofu)
    VALUES (
    {fzID}, {statusCode}, {statusName}, {thumb}, {atArea}, {buildingName}, {communityName}, {areaSize},
    {startPrice}, {currentPrice}, {unitPrice}, {buildingType}, {bTypeName}, {taxesBy}, {taxesName},
    {canLoan}, {LoanText}, {discountRate}, {puchaseLimit}, {countyName}, {startTime}, {endTime},
    {editDate}, {competePersons}, {callTimes}, {yijialv}, {ccode}, {ajklink}, {bemetro}, {jiaofu}
    )
"""
# 执行语句
cursor.execute(insert_house)


# 更新
update_house = f"""
    UPDATE houselist SET fzID={fzID}, statusCode={statusCode}, statusName={statusName}, thumb={thumb},
    atArea={atArea}, buildingName={buildingName}, communityName={communityName}, areaSize={areaSize},
    startPrice={startPrice}, currentPrice={currentPrice}, unitPrice={unitPrice}, buildingType={buildingType},
    bTypeName={bTypeName}, taxesBy={taxesBy}, taxesName={taxesName}, canLoan={canLoan}, LoanText={LoanText},
    discountRate={discountRate}, puchaseLimit={puchaseLimit}, countyName={countyName}, startTime={startTime},
    endTime={endTime}, editDate={editDate}, competePersons={competePersons}, callTimes={callTimes},
    yijialv={yijialv}, ccode={ccode}, ajklink={ajklink}, bemetro={bemetro}, jiaofu={jiaofu} WHERE fzID={fzID}
"""
# 执行语句
cursor.execute(update_house)

# 写入
keys = ", ".join(data_dict.keys())
values = ', '.join(['%s'] * len(data_dict))
value = tuple([v for v in data_dict.values()])

insert_house = 'INSERT INTO %s (%s) VALUES (%s)' % ('tablename', keys, values)
cursor.execute(insert_house, value)

# 更新
kvs = ", ".join(list(map(lambda k: k + "=%s", list(data_dict.keys()))))
value = tuple([v for v in data_dict.values()])

update_house = "UPDATE %s SET %s WHERE fzID=%s" % ("tablename", kvs, repr(data_dict.get('fzID')))
cursor.execute(update_house, value)