mysql insert 触发器
添加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
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);
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 ;mysql update触发器
这个是在一个表上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 ;python脚本动态监听
#!/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 &
效果图
