Python websocket sql 有没有办法不用写十亿个'if...以利夫'？

Python websocket sql Is there any way without having to write a billion of 'if...elif'?

提问人：Toby wkw 提问时间：11/8/2023 最后编辑：Toby wkw 更新时间：11/8/2023 访问量：62

问：

我正在编写一个 websocket API，用于从数据提供者那里获取实时数据并更新我自己的数据库。数据采用 JSON 格式。该机制是，当程序第一次向其服务器发送请求时，它将返回包含所有字段的JSON格式数据。只要连接处于活动状态，一旦特定字段发生更改，服务器就会将 JSON 格式数据发回给我，其中仅包含更改的字段。 因此，我的 sql 语句将不得不使用 if 或 switch 大小写来满足所有 secenarios。 但是有太多可能的组合。有没有办法做到这一点，而不必写十亿个“如果......以利夫'？谢谢。

我第一次发出请求时的示例 JSON 数据（包括所有字段）：

[
{
  "Fields":{
    "ACTIV_DATE":"2023-08-22",
    "ACT_FLAG1":" ",
    "ACT_FLAG2":" ",
    "ACT_FLAG3":" ",
    "ACT_TP_1":"B\u21e7",
    "ACT_TP_2":"B\u21e7",
    "ACT_TP_3":"B\u21e7",
    "ACVOL_1":null,
    "ASIA_CLOSE":null,
    "ASIA_CL_DT":null,
    "ASIA_HIGH":null,
    "ASIA_HI_TM":null,
    "ASIA_LOW":null,
    "ASIA_LW_TM":null,
    "ASIA_NETCH":null,
    "ASIA_OPEN":null,
    "ASIA_OP_TM":null,
    "ASK":7.2929,
    "ASKSIZE":null,
    "ASK_1":7.2925,
    "ASK_2":7.2885,
    "ASK_HIGH_1":7.2932,
    "ASK_HI_TME":"08:26:00",
    "ASK_LOW_1":7.268,
    "ASK_LO_TME":"01:34:00",
    "ASK_SPREAD":null,
    "BCAST_REF":"CNY=",
    "BCKGRNDPAG":"    ",
    "BEST_ASIZ1":null,
    "BEST_ASIZ2":null,
    "BEST_ASIZ3":null,
    "BEST_ASIZ4":null,
    "BEST_ASIZ5":null,
    "BEST_ASK1":null,
    "BEST_ASK2":null,
    "BEST_ASK3":null,
    "BEST_ASK4":null,
    "BEST_ASK5":null,
    "BEST_BID1":null,
    "BEST_BID2":null,
    "BEST_BID3":null,
    "BEST_BID4":null,
    "BEST_BID5":null,
    "BEST_BSIZ1":null,
    "BEST_BSIZ2":null,
    "BEST_BSIZ3":null,
    "BEST_BSIZ4":null,
    "BEST_BSIZ5":null,
    "BID":7.2923,
    "BIDSIZE":null,
    "BID_1":7.2911,
    "BID_2":7.2882,
    "BID_HIGH_1":7.2926,
    "BID_LOW_1":7.267,
    "BID_NET_CH":0.0058,
    "BID_SPREAD":null,
    "BID_TICK_1":"\u21e7",
    "BKGD_REF":"Chinese Yuan",
    "BOND_TYPE":"   ",
    "BPV":null,
    "B_ASK1_TIM":null,
    "B_ASK2_TIM":null,
    "B_ASK3_TIM":null,
    "B_ASK4_TIM":null,
    "B_ASK5_TIM":null,
    "B_BID1_TIM":null,
    "B_BID2_TIM":null,
    "B_BID3_TIM":null,
    "B_BID4_TIM":null,
    "B_BID5_TIM":null,
    "CALL_PRC":null,
    "CCY_NAME":"                              ",
    "CLOSE_ASK":7.289,
    "CLOSE_BID":7.2865,
    "CLOSE_TYPE":"B ",
    "CNTCT_ID":null,
    "CONTEXT_ID":3284,
    "CONVEXITY":null,
    "CROSS_SC":"1E+00",
    "CTBLOC_ID1":"   ",
    "CTBLOC_ID2":"   ",
    "CTBLOC_ID3":"   ",
    "CTBTR_1":"Refinitiv",
    "CTBTR_2":"            ",
    "CTBTR_3":"            ",
    "CTBTR_BKGD":null,
    "CTBTR_ID1":"    ",
    "CTBTR_ID2":"    ",
    "CTBTR_ID3":"    ",
    "CTB_LOC1":"   ",
    "CTB_LOC2":"   ",
    "CTB_LOC3":"   ",
    "CTB_PAGE1":"    ",
    "CTB_PAGE2":"    ",
    "CTB_PAGE3":"    ",
    "CURRENCY":"CNY",
    "DAYS_MAT":null,
    "DEALT_VL1":null,
    "DEALT_VL2":null,
    "DEALT_VL3":null,
    "DELTA":null,
    "DLG_CODE1":"      ",
    "DLG_CODE2":"      ",
    "DLG_CODE3":"      ",
    "DSO_ID":16416,
    "DSPLY_NAME":"Refinitiv",
    "DVOL1_SC":" ",
    "DVOL2_SC":" ",
    "DVOL3_SC":" ",
    "EMAIL_ADRS":"                                        ",
    "EURO_CLOSE":null,
    "EURO_CL_DT":null,
    "EURO_HIGH":null,
    "EURO_HI_TM":null,
    "EURO_LOW":null,
    "EURO_LW_TM":null,
    "EURO_NETCH":null,
    "EURO_OPEN":null,
    "EURO_OP_TM":null,
    "FIX_DATE":null,
    "FWD1_PRICE":null,
    "FWD2_PRICE":null,
    "GEN_TEXT16":"                ",
    "GEN_VAL1":null,
    "GEN_VAL10":null,
    "GEN_VAL2":null,
    "GEN_VAL3":null,
    "GEN_VAL4":null,
    "GEN_VAL5":null,
    "GEN_VAL6":null,
    "GEN_VAL7":null,
    "GEN_VAL8":null,
    "GEN_VAL9":null,
    "GN_TXT16_2":"                ",
    "GN_TXT24_1":"                        ",
    "GN_TXT32_1":"                                ",
    "GN_TXT32_2":"                                ",
    "GV10_TEXT":"      ",
    "GV1_DATE":null,
    "GV1_FLAG":" ",
    "GV1_TEXT":"SPOT  ",
    "GV1_TIME":null,
    "GV2A_RTIM1":null,
    "GV2A_RTIM2":null,
    "GV2A_RTIM3":null,
    "GV2B_RTIM1":null,
    "GV2B_RTIM2":null,
    "GV2B_RTIM3":null,
    "GV2_DATE":null,
    "GV2_FLAG":" ",
    "GV2_TEXT":"USDCNY",
    "GV2_TIME":null,
    "GV3_FLAG":" ",
    "GV3_TEXT":"      ",
    "GV4_FLAG":" ",
    "GV4_TEXT":"      ",
    "GV5_TEXT":"      ",
    "GV6_TEXT":"      ",
    "GV7_TEXT":"      ",
    "GV8_TEXT":"      ",
    "GV9_TEXT":"      ",
    "HIGHTP_1":"B",
    "HIGH_1":7.2926,
    "HIGH_2":7.2911,
    "HIGH_3":7.2909,
    "HIGH_4":7.2909,
    "HIGH_5":7.2909,
    "HIGH_TIME":"08:26:00",
    "HIGH_TIME2":"08:19:00",
    "HIGH_TIME3":"07:30:00",
    "HIGH_TIME4":"07:30:00",
    "HIGH_TIME5":"07:30:00",
    "HIGH_YLD":null,
    "HSTCLBDDAT":"2023-08-21",
    "HSTCLSDATE":"2023-08-21",
    "HST_CLOSE":7.2865,
    "HST_CLSBID":7.2865,
    "HST_CLSYLD":null,
    "IND_NEWS":"          ",
    "INST_DESC":"                              ",
    "IRGFID":null,
    "IRGPRC":0.0796,
    "IRGVAL":null,
    "LONGLINK1":null,
    "LOWTP_1":"A",
    "LOW_1":7.268,
    "LOW_2":7.268,
    "LOW_3":7.268,
    "LOW_4":7.268,
    "LOW_5":7.268,
    "LOW_TIME":"01:34:00",
    "LOW_TIME2":"01:34:00",
    "LOW_TIME3":"01:34:00",
    "LOW_TIME4":"01:34:00",
    "LOW_TIME5":"01:34:00",
    "LOW_YLD":null,
    "MATUR_DATE":null,
    "MID_1":null,
    "MID_2":null,
    "MID_3":null,
    "MID_NET_CH":null,
    "MID_PRICE":null,
    "MID_SPREAD":null,
    "MONTH_HIGH":null,
    "MONTH_LOW":null,
    "MTD":null,
    "MTHHI_DT":null,
    "MTHLO_DT":null,
    "NETCHNG_1":0.0058,
    "NEWS":"    ",
    "NEWSTM_MS":null,
    "NEWS_TIME":null,
    "NOMINAL":null,
    "NUM_BIDS":null,
    "NUM_MOVES":null,
    "OFFCL_CODE":"MHSH        ",
    "OFFC_CODE2":"            ",
    "OFF_CD_IN2":"   ",
    "OFF_CD_IND":"   ",
    "OPEN_BID":7.267,
    "OPEN_PRC":7.267,
    "OPEN_TIME":"01:34:00",
    "OPEN_TYPE":"B ",
    "OPEN_YLD":null,
    "PCTCHG_3M":null,
    "PCTCHG_6M":null,
    "PCTCHG_INC":null,
    "PCTCHG_MTD":null,
    "PCTCHG_TRT":null,
    "PCTCHG_YTD":null,
    "PCTCHNG":0.08,
    "PREF_DISP":153,
    "PREMIUM":null,
    "PREV_DISP":null,
    "PRIMACT_1":7.2923,
    "PRIMACT_2":7.2911,
    "PRIMACT_3":7.2882,
    "PROD_PERM":363,
    "PROV_SYMB":"                                ",
    "PR_FREQ":"      ",
    "PUTCALLIND":"CALL",
    "P_C_IND1":"    ",
    "QUOTE_TYPE":"   ",
    "QUOTIM_MS":null,
    "RCS_AS_CLA":"   ",
    "RDNDISPLAY":153,
    "RDN_EXCHD2":"NY$",
    "RDN_EXCHID":"   ",
    "RECORDTYPE":209,
    "REL_SPEEDG":null,
    "RIC_DESC":"        ",
    "RT_YIELD_1":null,
    "SALTIM_MS":null,
    "SCALING":"1       ",
    "SC_ACT_TP1":"  ",
    "SC_ACT_TP2":"  ",
    "SC_ACT_TP3":"  ",
    "SC_AFLAG1":" ",
    "SC_AFLAG2":" ",
    "SC_AFLAG3":" ",
    "SEC_ACT_1":7.2929,
    "SEC_ACT_2":7.2925,
    "SEC_ACT_3":7.2885,
    "SEC_HIGH":null,
    "SEC_HI_TP":" ",
    "SEC_LOW":null,
    "SEC_LO_TP":" ",
    "SEC_VOL1":null,
    "SEQNUM":null,
    "SETTLEDATE":null,
    "SPS_SP_RIC":".[SPSEVAI-VAH10-P4",
    "START_DT":null,
    "TERM":null,
    "TIMACT":"08:29:00",
    "TIMCOR":null,
    "TIMCOR_MS":null,
    "TRADE_DATE":null,
    "TRDPRC_1":null,
    "TRDTIM_1":null,
    "TRDTIM_MS":null,
    "TRDVOL_1":null,
    "TRD_UNITS":"4DP ",
    "US_CLOSE":null,
    "US_CL_DT":null,
    "US_HIGH":null,
    "US_HI_TM":null,
    "US_LOW":null,
    "US_LW_TM":null,
    "US_NETCH":null,
    "US_OPEN":null,
    "US_OP_TM":null,
    "VALUE_DT1":"2023-08-22",
    "VALUE_DT2":"2023-08-22",
    "VALUE_DT3":"2023-08-22",
    "VALUE_TS1":"08:29:57",
    "VALUE_TS2":"08:19:04",
    "VALUE_TS3":"08:09:39",
    "WEB_ADRS":"                                                                                ",
    "WEEK_HIGH":null,
    "WEEK_LOW":null,
    "WEIGHTING":null,
    "WKHI_DT":null,
    "WKLO_DT":null,
    "WTD_AVE1":null,
    "YLD_NETCHG":null,
    "YRBDHI_IND":" ",
    "YRBDLO_IND":" ",
    "YRBIDHIGH":7.317,
    "YRBIDLOW":6.6905,
    "YRHIGH":7.317,
    "YRHIGHDAT":null,
    "YRLOW":6.6915,
    "YRLOWDAT":null,
    "YTD":null
  },
  "ID":2,
  "Key":{
    "Name":"CNY=X",
    "Service":"ELEKTRON_DD"
  },
  "PermData":"AwEBNjw=",
  "Qos":{
    "Rate":"JitConflated",
    "Timeliness":"Realtime"
  },
  "SeqNumber":6334,
  "State":{
    "Data":"Ok",
    "Stream":"Open"
  },
  "Type":"Refresh"
}

]

当服务器向我发送更新的数据时的示例 JSON 数据（仅包括更改的字段）：

 [
  {
    "DoNotConflate":true,
    "Fields":{
      "ACTIV_DATE":"2023-08-22",
      "ACT_TP_2":"B\u21e7",
      "ACT_TP_3":"B\u21e7",
      "ASK":7.292,
      "ASK_HIGH_1":7.2938,
      "ASK_HI_TME":"08:31:00",
      "ASK_LOW_1":7.268,
      "ASK_LO_TME":"01:34:00",
      "BID":7.2908,
      "BID_HIGH_1":7.2933,
      "BID_LOW_1":7.267,
      "BID_NET_CH":0.0043,
      "BID_TICK_1":"\u21e9",
      "CLOSE_ASK":7.289,
      "CLOSE_BID":7.2865,
      "CLOSE_TYPE":"B ",
      "GV2_TEXT":"USDCNY",
      "HIGHTP_1":"B",
      "HIGH_1":7.2933,
      "HIGH_TIME":"08:34:00",
      "HSTCLBDDAT":"2023-08-21",
      "HSTCLSDATE":"2023-08-21",
      "HST_CLOSE":7.2865,
      "HST_CLSBID":7.2865,
      "IRGPRC":0.059,
      "LOWTP_1":"A",
      "LOW_1":7.268,
      "LOW_TIME":"01:34:00",
      "NETCHNG_1":0.0043,
      "OPEN_BID":7.267,
      "OPEN_PRC":7.267,
      "OPEN_TIME":"01:34:00",
      "OPEN_TYPE":"B ",
      "PCTCHNG":0.06,
      "PRIMACT_1":7.2908,
      "SCALING":"1       ",
      "SEC_ACT_1":7.292,
      "TIMACT":"08:39:00",
      "VALUE_DT1":"2023-08-22",
      "VALUE_TS1":"08:39:04",
      "YRBIDHIGH":7.317,
      "YRBIDLOW":6.6905,
      "YRHIGH":7.317,
      "YRLOW":6.6915
    },
    "ID":2,
    "Key":{
      "Name":"CNY=X",
      "Service":"ELEKTRON_DD"
    },
    "SeqNumber":6350,
    "Type":"Update",
    "UpdateType":"Unspecified"
  }
]
python sql json if-statement websocket

评论

答：

0赞 Amadan 11/8/2023 #1

您可以动态构建 SQL，无需。像这样的东西应该可以工作（其中是更新列表的单个元素）：ifitem

fields = list(item["Fields"])
update_sql = ', '.join(f"{field}=%s" for field in fields)
cmd_data = [item["Fields"][field] for field in fields]
cmd_data.append(item["ID"])
cmd = f"UPDATE mytable SET {update_sql} WHERE ID=%s"
cursor.execute(cmd, cmd_data)

编辑：如果您需要字段过滤/映射，您可以执行以下操作：

field_map = {
    "OPEN_BID": "o_bid",
    "CLOSE_BID": "c_bid",
}

item_data = {
    v: item["Fields"][k]
    for k, v in field_map
    if k in item["Fields"]
}
fields = list(item_data)
update_sql = ', '.join(f"{field}=%s" for field in fields)
cmd_data = [item_data[field] for field in fields]
cmd_data.append(item["ID"])
cmd = f"UPDATE mytable SET {update_sql} WHERE ID=%s"
cursor.execute(cmd, cmd_data)

这将执行

UPDATE mytable SET o_bid=7.267, c_bid=7.2865 WHERE ID=2

评论

0赞 Toby wkw 11/8/2023
感谢您的回复。但是有一个问题，即JSON数据中的字段不等于数据库中列的名称。而且，也不是需要 JSON 数据中的所有字段。只需要 10 个字段。但我真的很感激你的回应。
1赞 Abdul Aziz Barkat 11/8/2023
@Tobywkw，您可以使用字典将 JSON 键映射到相应的列名。如果这似乎不能满足您的需求，您可能会选择一些外部软件包，为您提供某种序列化和 ORM。
0赞 Amadan 11/8/2023
请在问题中附上相关数据。我读不懂你的心思:)
0赞 Toby wkw 11/8/2023
@AbdulAzizBarkat 您的意思是使用 JSON 键作为键，使用数据库列名作为值创建字典吗？在那之后我该怎么办？
0赞 Toby wkw 11/8/2023
@Amadan 对不起，我错过了我问题中的那部分。

上一个：Javascript if 语句不读取条件

下一个：每日生产表和考勤表集成