您好,登錄后才能下訂單哦!
添加insert觸發器,在insert一條新紀錄時,當主單號不為空,并且新增價格和最近一次價格對比不相等時,說明價格有變化。這時觸發器會自動將上一次老價格添加到當前新增行的unit_price_old老價格列。
這個需求是在一個表上,更新自己身上的其他列,這時需要用before,并且set new.列名,并且new必須在等號左邊。
delimiter // create trigger insert_flight_cabin_unit_price before insert on flight_cabin_book_o_update for each row begin /*注意給變量賦值,等號右邊的select必須加括號*/ /*獲取每條主單號的最近一次更新時間*/ set @last_creat_time = (select creat from flight_cabin_book_o_update where waybill=new.waybill and flight_no=new.flight_no and flight_time=new.flight_time order by creat desc limit 1); /*獲取每條主單號的最近一次更新價格*/ set @last_unit_price = (select unit_price from flight_cabin_book_o_update where waybill=new.waybill and flight_no=new.flight_no and flight_time=new.flight_time and creat = @last_creat_time limit 1); /*如果一個主單號不為空,并且最近一次價格和現在insert的價格不相同,就說明價格更新了*/ if new.waybill is not null and new.waybill !='' and new.unit_price != @last_unit_price then set new.unit_price_old = @last_unit_price; set new.unit_price_old_time = @last_creat_time; end if; end // delimiter ;
這個是在一個表上update自己身上的其他列,也需要用before,并且 set new.列名,并且new必須在等號左邊
delimiter // create trigger update_flight_cabin_unit_price before update on czx for each row begin if new.unit_price != old.unit_price then set new.unit_price_old = old.unit_price; set new.is_update_price = 'Y'; set new.update_price_time=now(); end if; end // delimiter ;
#!/usr/bin/python # -*- coding:utf8 -*- # author: chenzhixin from contextlib import contextmanager import pymysql as mysqldb import requests import time @contextmanager def get_mysql_conn(**kwargs): """ 建立MySQL數據庫連接 :param kwargs: :return: """ conn = mysqldb.connect(host=kwargs.get('host', 'localhost'), user=kwargs.get('user'), password=kwargs.get('password'), port=kwargs.get('port', 3306), database=kwargs.get('database') ) try: yield conn finally: if conn: conn.close() def execute_mysql_select_sql(conn, sql): """ 執行mysql的select類型語句 :param conn: :param sql: :return: """ with conn as cur: cur.execute(sql) rows = cur.fetchall() return rows def execute_mysql_sql(conn, sql): """ 執行mysql的dml和ddl語句,不包括select語句 :param conn: :param sql: :return: """ with conn as cur: cur.execute(sql) def get_mysql_flight_cabin_book_o_update_data(conn): """ 獲取 kb_kettle_data.flight_cabin_book_o_update的數據 :param conn: :return: """ sql = "select " \ "id, " \ "waybill, " \ "flight_no," \ "flight_time," \ "unit_price, " \ "unit_price_old, " \ "unit_price_old_time," \ "creat " \ "from flight_cabin_book_o_update " \ "where unit_price_old is not null" mysql_table_rows = execute_mysql_select_sql(conn, sql) if mysql_table_rows: print('檢測到價格變化:\n', mysql_table_rows) for index, row in enumerate(mysql_table_rows, 1): id = row[0] waybill = row[1] flight_no = row[2] flight_time = row[3] unit_price = row[4] unit_price_old = row[5] unit_price_old_time = row[6] creat = row[7] yield {'id': id, 'waybill': waybill, 'flight_no': flight_no, 'flight_time': flight_time, 'unit_price': unit_price, 'unit_price_old': unit_price_old, 'unit_price_old_time': unit_price_old_time, 'creat': creat } def send_to_qyweixin(dic): """ 發送消息到企業微信 :param dic: :return: """ headers = {"Content-Type": "text/plain"} # s = "--石墨價格變化通知--\n主單號:{waybill}\n航班號:{flight_no}\n航班日期:{flight_time}\n\n時間1:{unit_price_old_time}\n-----------------------\n價格1:{unit_price_old}\n-----------------------\n{creat}\n主單號:{waybill}\n價格變為: {unit_price}".format( # waybill=dic['waybill'], # unit_price_old=dic['unit_price_old'], # unit_price_old_time=dic['unit_price_old_time'], # creat=dic['creat'], # unit_price=dic['unit_price'] # ) s = """ ---石墨價格變化通知--- 主單號:{waybill} 航班號:{flight_no} 航班日期:{flight_time} 時間1:{unit_price_old_time} 價格1:{unit_price_old} 時間2:{creat} 價格2:{unit_price} """.format( waybill=dic['waybill'], flight_no=dic['flight_no'], flight_time=dic['flight_time'], unit_price_old=dic['unit_price_old'], unit_price_old_time=dic['unit_price_old_time'], creat=dic['creat'], unit_price=dic['unit_price'] ) data = { "msgtype": "text", "text": { "content": s, } } r = requests.post( url='https://qyapi.weixin.qq.com/cgi-bin/webhook/sexxxd5-eb13', headers=headers, json=data) print(r.text) def main(): mysql_conn_args = dict(user='user1', host='10.xx.xx.xx', password='123456', database='xxxxx') with get_mysql_conn(**mysql_conn_args) as mysql_conn: while True: print('正在監聽價格是否有變化.....') # 1、首先獲取mysql_flight_cabin_book_o_update表中有價格更新的所有行數據 mysql_data_dic = get_mysql_flight_cabin_book_o_update_data(mysql_conn) # 2、其次遍歷有價格變化的行數據,發送給企業微信 for dic in mysql_data_dic: # 發送到企業微信 send_to_qyweixin(dic) # 3、最后把mysql_flight_cabin_book_o_update表中的標記位is_update_price置為空 update_flag_sql = "update flight_cabin_book_o_update set unit_price_old=null,unit_price_old_time=null where waybill='{}'".format(dic['waybill']) execute_mysql_sql(mysql_conn, update_flag_sql) time.sleep(60) if __name__ == '__main__': main()
[root@gfs02 wangsn]# nohup python /usr/local/shell/monitor_price_qywx/monitor_price_to_qiyeweixin.py > /dev/null 2>&1 &
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。