Shulou(Shulou.com)06/01 Report--

Mysql insert trigger

Add an insert trigger to indicate that the price has changed when the main order number is not empty and the new price is not equal to the last price comparison when insert has a new record. At this point, the trigger will automatically add the last old price to the unit_price_old old price column of the current new line.

The requirement is to update other columns on a table, using before and set new. Column name, and new must be to the left of the equal sign.

Delimiter / / create trigger insert_flight_cabin_unit_pricebeforeinsert on flight_cabin_book_o_updatefor each rowbegin/* note the assignment of the equal variable. The select to the right of the equal sign must be parenthesized * / / * to get the last update time of each main order number * / 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) / * get the last updated price of each main order number * / 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) / * if a main order number is not empty and the most recent price is different from the current insert price, the price has been updated * / if new.waybill is not null and new.waybill! =''and new.unit_price! = @ last_unit_price thenset new.unit_price_old = @ last_unit_price;set new.unit_price_old_time = @ last_creat_time;end if;end / / delimiter; mysql update trigger

This is the other column of update itself on a table, which also needs to use before, and set new. Column name, and new must be to the left of the equal sign

Delimiter / / create trigger update_flight_cabin_unit_pricebeforeupdate on czxfor each rowbeginif new.unit_price! = old.unit_price thenset new.unit_price_old = old.unit_price;set new.is_update_price = 'YYX set new.update_price_time=now (); end if;end / / delimiter; python script dynamic listening

#! / usr/bin/python#-*-coding:utf8-*-# author: chenzhixinfrom contextlib import contextmanagerimport pymysql as mysqldbimport requestsimport time@contextmanagerdef get_mysql_conn (* * kwargs): "establish a MySQL database connection: 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): "" execute the select type statement of mysql: param conn:: param sql:: return: "with conn as cur: cur.execute (sql) rows = cur.fetchall () return rowsdef execute_mysql_sql (conn, sql):"execute the dml and ddl statements of mysql Does not include select statement: param conn:: param sql:: return: "with conn as cur: cur.execute (sql) def get_mysql_flight_cabin_book_o_update_data (conn):" get data from 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 ('detected price change:\ nprices, 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): "send message to WeCom: param dic:: return:" headers = {"Content-Type": "text/plain"} # s = "- notice of change in graphite price-- main order number: {waybill}\ nFlight number: {flight_no}\ N flight date: {flight_time}\ n\ ntime 1: {unit_price_old_time}\ nMurray -\ nPrice 1: {unit_price_old}\ nMurray -\ n {creat}\ nMaster order number: {waybill}\ nPrice becomes: {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 = ""-notice of graphite price change-main order number: {waybill} flight number: {flight_no} flight date: {flight_time} time 1: {unit_price_old_time} price 1: {unit_price_old} time 2: {creat} price 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 ('listening for price changes.') # 1. First, get all the row data in the mysql_flight_cabin_book_o_ update table with price updates mysql_data_dic = get_mysql_flight_cabin_book_o_update_data (mysql_conn) # 2, and then traverse the row data with price changes Send to WeCom for dic in mysql_data_dic: # send to WeCom send_to_qyweixin (dic) # 3, and finally set the tag bit is_update_price in the mysql_flight_cabin_book_o_ update table to empty 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 () run the script

[root@gfs02 wangsn] # nohup python / usr/local/shell/monitor_price_qywx/monitor_price_to_qiyeweixin.py > / dev/null 2 > & 1 &

Effect picture

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.