liff_test/redis_set.py

import opengraph
import redis

from jinja2 import Environment, BaseLoader
import traceback
data="""
{
  "type": "bubble",
  "header": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "text",
        "text": "集仕多 ChoozMo",
        "weight": "bold",
        "size": "xl",
        "color": "#ffffff"
      },
      {
        "type": "text",
        "text": "新聞 AI 主播媒體聯播網最佳方案",
        "size": "md",
        "color": "#ffffff"
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "xxl",
            "url": "https://ppt.cc/fLP10x@.png",
            "offsetTop": "xs"
          }
        ],
        "justifyContent": "flex-end",
        "offsetStart": "255px",
        "position": "absolute",
        "offsetBottom": "20px"
      }
    ],
    "backgroundColor": "#e1642b"
  },
  "hero": {
    "type": "image",
    "url": "https://ppt.cc/fDmzAx@.png",
    "size": "full",
    "aspectRatio": "20:13",
    "action": {
      "type": "uri",
      "uri": "http://linecorp.com/"
    },
    "offsetTop": "lg"
  },
  "body": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "text",
        "text": "圖文+影音+聯播",
        "color": "#aaaaaa",
        "size": "sm"
      },
      {
        "type": "text",
        "text": "全方位媒體曝光，一次到位！",
        "color": "#aaaaaa",
        "size": "sm"
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "網路新聞圖文報導",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "十二家網路媒體聯播",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "AI 主播影音播報影片",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "horizontal",
        "margin": "lg",
        "spacing": "sm",
        "contents": [
          {
            "type": "text",
            "text": "一次到位擁有，只要",
            "margin": "none",
            "size": "sm",
            "gravity": "center",
            "color": "#2384e3",
            "weight": "bold"
          },
          {
            "type": "text",
            "text": "$36,000",
            "size": "xxl",
            "offsetStart": "5px",
            "weight": "bold",
            "color": "#2384e3"
          }
        ],
        "backgroundColor": "#fff3ed",
        "background": {
          "type": "linearGradient",
          "angle": "0deg",
          "startColor": "#fff3ed",
          "endColor": "#ffffff"
        }
      },
      {
        "type": "text",
        "text": "不論是公司宣傳／產品發表／品牌經營，都能透過新聞稿發布，提升曝光及知名度！",
        "wrap": true,
        "size": "13px",
        "align": "start",
        "offsetTop": "10px"
      }
    ]
  },
  "footer": {
    "type": "box",
    "layout": "vertical",
    "spacing": "sm",
    "contents": [
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "button",
            "style": "link",
            "height": "sm",
            "action": {
              "type": "uri",
              "label": "請來採訪我",
              "uri": "https://page.line.me/choozmo"
            },
            "color": "#FFFFFF"
          }
        ],
        "backgroundColor": "#e47e4d",
        "offsetTop": "-3px"
      },
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "button",
            "style": "link",
            "height": "sm",
            "action": {
              "type": "uri",
              "label": "了解更多",
              "uri": "https://page.line.me/choozmo"
            },
            "color": "#FFFFFF"
          }
        ],
        "backgroundColor": "#06C152"
      }
    ],
    "flex": 0
  }
}
"""

data2="""
{
  "type": "bubble",
  "header": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "text",
        "text": "集仕多 ChoozMo",
        "weight": "bold",
        "size": "xl",
        "color": "#ffffff"
      },
      {
        "type": "text",
        "text": "新聞 AI 主播媒體聯播網最佳方案",
        "size": "md",
        "color": "#ffffff"
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "xxl",
            "url": "https://ppt.cc/fLP10x@.png",
            "offsetTop": "xs"
          }
        ],
        "justifyContent": "flex-end",
        "offsetStart": "255px",
        "position": "absolute",
        "offsetBottom": "20px"
      }
    ],
    "backgroundColor": "#e1642b"
  },
  "hero": {
    "type": "image",
    "url": "https://ppt.cc/fDmzAx@.png",
    "size": "full",
    "aspectRatio": "20:13",
    "offsetTop": "lg"
  },
  "body": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "text",
        "text": "圖文+影音+聯播",
        "color": "#aaaaaa",
        "size": "sm"
      },
      {
        "type": "text",
        "text": "全方位媒體曝光，一次到位！",
        "color": "#aaaaaa",
        "size": "sm"
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "網路新聞圖文報導",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "十二家網路媒體聯播",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "baseline",
        "margin": "md",
        "contents": [
          {
            "type": "icon",
            "size": "md",
            "url": "https://ppt.cc/feXZPx@.png",
            "offsetTop": "xs"
          },
          {
            "type": "text",
            "text": "AI 主播影音播報影片",
            "offsetStart": "md",
            "color": "#e47e4d",
            "weight": "bold"
          }
        ]
      },
      {
        "type": "box",
        "layout": "horizontal",
        "margin": "lg",
        "spacing": "sm",
        "contents": [
          {
            "type": "text",
            "text": "一次到位擁有，只要",
            "margin": "none",
            "size": "sm",
            "gravity": "center",
            "color": "#2384e3",
            "weight": "bold"
          },
          {
            "type": "text",
            "text": "$36,000",
            "size": "xxl",
            "offsetStart": "5px",
            "weight": "bold",
            "color": "#2384e3"
          }
        ],
        "backgroundColor": "#fff3ed",
        "background": {
          "type": "linearGradient",
          "angle": "0deg",
          "startColor": "#fff3ed",
          "endColor": "#ffffff"
        }
      },
      {
        "type": "text",
        "text": "不論是公司宣傳／產品發表／品牌經營，都能透過新聞稿發布，提升曝光及知名度！",
        "wrap": true,
        "size": "13px",
        "align": "start",
        "offsetTop": "10px"
      }
    ]
  },
  "footer": {
    "type": "box",
    "layout": "vertical",
    "spacing": "sm",
    "contents": [
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "button",
            "style": "link",
            "height": "sm",
            "action": {
              "type": "uri",
              "label": "請來採訪我",
              "uri": "https://page.line.me/choozmo"
            },
            "color": "#FFFFFF"
          }
        ],
        "backgroundColor": "#e47e4d",
        "offsetTop": "-3px"
      },
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "button",
            "style": "link",
            "height": "sm",
            "action": {
              "type": "uri",
              "label": "了解更多",
              "uri": "https://ai.choozmo.com/"
            },
            "color": "#FFFFFF"
          }
        ],
        "backgroundColor": "#06C152"
      },
      {
        "type": "box",
        "layout": "horizontal",
        "contents": [
          {
            "type": "image",
            "url": "https://ppt.cc/fHYTTx@.png",
            "offsetStart": "35px",
            "size": "xl",
            "position": "absolute"
          },
          {
            "type": "text",
            "text": "技術提供",
            "offsetBottom": "15px",
            "color": "#8B8B8B",
            "size": "sm",
            "weight": "bold",
            "offsetEnd": "40px",
            "position": "absolute"
          }
        ],
        "action": {
          "type": "uri",
          "label": "action",
          "uri": "https://page.line.me/choozmo"
        },
        "width": "100%",
        "height": "50px",
        "alignItems": "center",
        "offsetTop": "5px"
      }
    ],
    "flex": 0,
    "height": "160px"
  }
}

"""

bubble="""
 {
      "type": "bubble",
      "size": "micro",
      "hero": {
        "type": "image",
        "url": "https://scdn.line-apps.com/n/channel_devcenter/img/flexsnapshot/clip/clip10.jpg",
        "size": "full",
        "aspectMode": "cover",
        "aspectRatio": "320:213"
      },
      "body": {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "text",
            "text": "{{title }}",
            "weight": "bold",
            "size": "sm",
            "wrap": true
          },
          {
            "type": "box",
            "layout": "vertical",
            "contents": [
              {
                "type": "box",
                "layout": "baseline",
                "spacing": "sm",
                "contents": [
                  {
                    "type": "text",
                    "text": "ChoozMo商情AI",
                    "wrap": true,
                    "color": "#8c8c8c",
                    "size": "xs",
                    "flex": 5
                  }
                ]
              }
            ]
      }]
}
}
"""

bubble2="""
{
  "type": "bubble",
  "header": {
    "type": "box",
    "layout": "horizontal",
    "position": "relative",
    "backgroundColor": "#FFFFFFFF",
    "contents": [
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "image",
            "url": "https://i.imgur.com/wQbJeGc.png",
            "margin": "none",
            "align": "end",
            "gravity": "top",
            "size": "md",
            "aspectRatio": "16:5",
            "action": {
              "type": "uri",
              "label": "ChoozMo",
              "uri": "https://cmm.ai/line"
            }
          },
          {
            "type": "text",
            "text": "今日熱搜",
            "weight": "bold",
            "color": "#BEB7B7FF",
            "position": "absolute",
            "offsetTop": "5px",
            "contents": []
          }
        ]
      }
    ]
  },
  "hero": {
    "type": "image",
    "url": "https://scdn.line-apps.com/n/channel_devcenter/img/fx/01_4_news.png",
    "size": "full",
    "aspectRatio": "20:13",
    "aspectMode": "cover",
    "action": {
      "type": "uri",
      "label": "Action",
      "uri": "https://linecorp.com/"
    }
  },
  "body": {
    "type": "box",
    "layout": "horizontal",
    "spacing": "md",
    "contents": [
      {
        "type": "spacer"
      },
      {
        "type": "box",
        "layout": "vertical",
        "contents": [
          {
            "type": "text",
            "text": "A1",
            "size": "xs",
            "flex": 1,
            "gravity": "top",
            "contents": []
          },
          {
            "type": "text",
            "text": "A2",
            "size": "xs",
            "flex": 2,
            "gravity": "center",
            "contents": []
          },
          {
            "type": "text",
            "text": "A3",
            "size": "xs",
            "flex": 2,
            "gravity": "center",
            "contents": []
          },
          {
            "type": "text",
            "text": "A4",
            "size": "xs",
            "flex": 1,
            "gravity": "bottom",
            "contents": []
          },
          {
            "type": "spacer"
          }
        ]
      }
    ]
  },
  "footer": {
    "type": "box",
    "layout": "horizontal",
    "contents": [
      {
        "type": "button",
        "action": {
          "type": "uri",
          "label": "More",
          "uri": "https://cmm.ai/line"
        }
      }
    ]
  }
}
"""
bubble3="""
{
  "type": "bubble",
  "direction": "ltr",
  "header": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "box",
        "layout": "horizontal",
        "action": {
          "type": "uri",
          "uri": "https://page.line.me/choozmo"
        },
        "width": "100%",
        "contents": [
          {
            "type": "text",
            "text": "今日熱搜",
            "align": "start",
            "contents": []
          },
          {
            "type": "image",
            "url": "https://i.imgur.com/wQbJeGc.png",
            "align": "end",
            "aspectRatio": "4:1",
            "aspectMode": "cover"
          }
        ]
      }
    ]
  },
  "body": {
    "type": "box",
    "layout": "vertical",
    "paddingAll": "0px",
    "width": "100%",
    "backgroundColor": "#000000FF",
    "action": {
          "type": "uri",
          "uri": "{{ qlink }}"
    },

    "contents": [
      {
        "type": "box",
        "layout": "vertical",

        "contents": [
          {
            "type": "image",
            "url": "{{ imgurl }}",
            "size": "full",
            "aspectMode": "cover"
          },
          {
            "type": "box",
            "layout": "vertical",
            "position": "absolute",
            "offsetBottom": "0px",
            "width": "100%",
            "height": "90px",
            "backgroundColor": "#9C8E7ECC",
            "contents": [
              {
                "type": "text",
                "text": "{{ imgtitle }}",
                "color": "#FFFFFFFF",
                "align": "start",
                "wrap": true,
                "contents": []
              }
            ]
          }
        ]
      },
      {
        "type": "box",
        "layout": "horizontal",
        "position": "absolute",
        "offsetTop": "10px",
        "offsetStart": "5px",
        "width": "100px",
        "borderWidth": "1px",
        "backgroundColor": "#898CDFFF",
        "cornerRadius": "5px",
        "contents": [
          {
            "type": "text",
            "text": "{{title}}",
            "color": "#FFFFFFFF",
            "align": "center",
            "contents": []
          }
        ]
      }
    ]
  },
"footer": {
    "type": "box",
    "layout": "vertical",
    "contents": [
      {
        "type": "text",
        "text": "相關關鍵字",
        "contents": []
      },
      {
        "type": "separator"
      },
      {
        "type": "box",
        "layout": "horizontal",
        "contents": [
          {
            "type": "text",
            "text": "{{K1}}",
            "contents": []
          },
          {
            "type": "text",
            "text": "{{K2}}",
            "contents": []
          }
        ]
      },
      {
        "type": "box",
        "layout": "horizontal",
        "contents": [
          {
            "type": "text",
            "text": "{{K3}}",
            "contents": []
          },
          {
            "type": "text",
            "text": "{{K4}}",
            "contents": []
          }
        ]
      }
    ]
  }
}
"""


data3_begin="""
{
  "type": "carousel",
  "contents": [
"""


data3_end="""
  ]
}
"""


import dataset
import json
import sys
#from opengraph import OpenGraph



db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/gtrends?charset=utf8mb4')

cursor=db.query('SELECT distinct qtitle FROM gtrends.trending_search_flat order by dt desc limit 5')
qtitles=[]
for c in cursor:
  qtitles.append(c['qtitle'])



result=[]

for qt in qtitles:
  cursor=db.query('SELECT distinct trending_search_flat.qtitle,trending_search_flat.atitle,trending_search_flat.aimg,trending_search_flat.aurl,kw_related.original,kw_related.kw FROM gtrends.trending_search_flat,kw_related where trending_search_flat.qtitle=kw_related.original and  trending_search_flat.qtitle="'+qt+'" order by trending_search_flat.dt desc')
  title=None
  imgurl=None
  imgtitle=qlink=None
  k1=k2=k3=k4=None
  lst_dict={}
  for c in cursor:
    title=c['qtitle']
    qlink=c['aurl']

    imgtitle=c['atitle']
#    imgurl=c['aimg']
    print(qlink)
    if imgurl is None:
      try:
        video = opengraph.OpenGraph(url=qlink)
        if video.is_valid():
          if video['image'] is not None:
            imgurl=video['image']
            print('GET OG IMAGE **************')
            print(imgurl)
      except:
        traceback.print_exc()
        imgurl=None

    lst_dict[c['kw']]=1
    if len(lst_dict)>=4:
      break
  if imgurl is None:
    print('none')
    imgurl=c['aimg']
  print(lst_dict)
  lst=list(lst_dict.keys())
  k1=lst[0]
  k2=lst[1]
  if len(lst)>=3:
    k3=lst[2]
  else:
    k3=' '
  if len(lst)>=4:
    k4=lst[3]
  else:
    k4=' '

  result.append({'title':title,'imgurl':imgurl,'qlink':qlink,'imgtitle':imgtitle,'K1':k1,'K2':k2,'K3':k3,'K4':k4})

result_lst=result
print(result)

r = redis.Redis(host='db.ptt.cx', port=6379, db=2,password='choozmo9')

rtemplate = Environment(loader=BaseLoader).from_string(bubble3)

result=""
for r2 in result_lst:
#  r3 = rtemplate.render({'title':title,'imgurl':imgurl,'qlink':qlink,'imgtitle':imgtitle,'K1':k1,'K2':k2,'K3':k3,'K4':k4})
  r3 = rtemplate.render(r2)

  result+=r3
  result+=','

result=result[:-1]
final_result=data3_begin+result+data3_end
full_result='{"title":"今日熱搜關鍵字","data":'+final_result+'}'
#final_result=data3_begin+result+data3_end

#print(final_result)
#r.set('btn_data',final_result)
r.set('btn_data',full_result)
#r.set('btn_data',data2)
sys.exit()