建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
當(dāng)數(shù)據(jù)庫里面的價格變化時，發(fā)送信息到企業(yè)微信中
MySQL insert 觸發(fā)器
添加insert觸發(fā)器，在insert一條新紀(jì)錄時，當(dāng)主單號不為空，并且新增價格和最近一次價格對比不相等時，說明價格有變化。這時觸發(fā)器會自動將上一次老價格添加到當(dāng)前新增行的unit_price_old老價格列。
我們提供的服務(wù)有：成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、山城ssl等。為上千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的山城網(wǎng)站制作公司
這個需求是在一個表上，更新自己身上的其他列，這時需要用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); /*如果一個主單號不為空，并且最近一次價格和現(xiàn)在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 ;
mysql update觸發(fā)器
這個是在一個表上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腳本動態(tài)監(jiān)聽
#!/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數(shù)據(jù)庫連接
: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):
"""
執(zhí)行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):
"""
執(zhí)行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的數(shù)據(jù)
: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):
"""
發(fā)送消息到企業(yè)微信
: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價格變?yōu)? {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('正在監(jiān)聽價格是否有變化.....')
# 1、首先獲取mysql_flight_cabin_book_o_update表中有價格更新的所有行數(shù)據(jù)
mysql_data_dic = get_mysql_flight_cabin_book_o_update_data(mysql_conn)
# 2、其次遍歷有價格變化的行數(shù)據(jù)，發(fā)送給企業(yè)微信
for dic in mysql_data_dic:
# 發(fā)送到企業(yè)微信
send_to_qyweixin(dic)
# 3、最后把mysql_flight_cabin_book_o_update表中的標(biāo)記位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()
運(yùn)行腳本
[root@gfs02 wangsn]# nohup python /usr/local/shell/monitor_price_qywx/monitor_price_to_qiyeweixin.py > /dev/null 2>&1 &
效果圖
網(wǎng)頁標(biāo)題：當(dāng)數(shù)據(jù)庫里面的價格變化時，發(fā)送信息到企業(yè)微信中
轉(zhuǎn)載注明：http://www.dlmjj.cn/article/pjjode.html
