E i ai ni au galuega e fai i lea aso ma lea aso, vaiaso ma lea vaiaso? Mo se faʻataʻitaʻiga, tusia o lipoti. E te talosagaina faʻamaumauga, auʻiliʻili, vaʻavaʻai i ai (faia kalafi, siata), ona lafo atu lea i lou pule. Ae fa'afefea pe a otometi nei mea uma?
I lenei aʻoaʻoga o le a matou fatuina se bot mo Telegram o le a fesoasoani i le faʻaogaina o lipoti. Ma o le mea sili ona manaia o le polokalama atoa o le a aofia ai na o le 50 laina o code! Afai o loʻo e fatuina se bot mo Telegram mo le taimi muamua, e tatau foi ona e faitauina lenei .
Ua fautuaina e Skillbox: Kosi fa'ata'ita'i .
Matou te faʻamanatu atu ia te oe: mo tagata faitau uma o le "Habr" - o se faʻaitiitiga o 10 rubles pe a lesitala i soʻo se vasega Skillbox e faʻaaoga ai le code promotional "Habr".
Tatou amata
Fa'apipi'i faletusi
O le a matou faʻaaogaina e maua ai fa'amatalaga mai le Google BigQuery. , и o le a fesoasoani ia te oe e vaai faalemafaufau i au faamatalaga. o le a auina atu faʻamatalaga maeʻa i Telegram.
pip3 faʻapipiʻi google-cloud-bigquery matplotlib numpy pandas python-telegram-bot
Feso'ota'i Google BigQuery API
Afai matou te mananaʻo e faʻaoga le auaunaga, matou te manaʻomia le faʻafesoʻotaʻi le Google BigQuery API. Ina ia faia lenei mea matou te alu i ai ma fai se galuega fou (pe filifili se mea o iai).
I le vaega e pulea, filifili ENABLE APIS AND SERVICES ma vaavaai mo BigQuery API.
Filifili le Enable to connect the API.
Fausia se ki fa'amatalaga
Tatou toe o i , filifili le Credentials tab, Create credentials and Service account key.
Ona - Teuga tupe fou, ma faʻapipiʻi le igoa i totonu ole igoa ole igoa ole Auaunaga.
Mai le Matafaioi matā'upu lisi, filifili Poloketi > Pule, ona Faia.
O le faila o le a otometi lava ona sii mai e taʻua o creds.json.
Seti GOOGLE_APPLICATION_CREDENTIALS, fa'ailoa le ala ile creds.json ile laina.
auina atu i fafo GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'
Afai na lelei mea uma, ua oʻo i le taimi e amata ai ona tusia le polokalame.
Fausia se talosaga
Mo le aʻoaʻoga o le a matou faʻaogaina faʻamatalaga mai le bigquery-public-data.stackoverflow, mo la matou lipoti o le a matou filifilia le numera o lomiga i aso taitasi.
E fai lava faigofie.
Su'e le laulau -> Va'ai fa'amatalaga -> Fa'asao le va'aiga -> Auina atu le ata
Se'i o tatou faia se galuega e tasi e fa'amatala ai filo ta'itasi.
Fesili ile BigQuery
Muamua matou te faaulufale mai le faletusi.
mai le google.cloud import bigquery
Matou te faia se galuega e taʻua o le query_to_bigquery, lea o le parakalafa o le fesili.
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframeO lenei galuega o le a toe faʻafoʻi le talosaga o se faʻamatalaga faʻamatalaga.
Va'aiga fa'amatalaga
Ina ia foia lenei faafitauli, filifili matplotlib.
faaulufale mai matplotlib.pyplot pei plt
Matou te manaʻomia ni faʻamaufaʻailoga se lima, o le x o le x-axis data, x_label o le ulutala mo le axis, y o le y-axis data, y_label o le ulutala mo le axis, ma le ulutala o le ulutala o le ata atoa.
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return pltFaasaoina le ata
Se'i o tatou fa'aoga ni galuega se lua e fai ai se fa'aaliga ma fa'asaoina.
O le a matou lafoina le numera o faʻasalalauga faʻasalalau i aso uma. Muamua matou te tusia se talosaga.
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""O le fesili e fesoasoani e aoina faʻamatalaga mo vaiaso e lua amata ia Tesema 2, 2018.
Matou te faʻaogaina lenei aso ona o le 2018-12-02 o faʻamaumauga sili ona lata mai na faʻamauina i le bigquery-public-data.stackoverflow.post_history, i isi tulaga e mafai ona e faʻaogaina CURRENT_DATE() e maua ai faʻamatalaga fou.
Valaau le galuega query_to_bigquery e maua ai faʻamatalaga.
fa'amatalaga = query_to_bigquery(fesili)
Ona matou faʻaogaina lea o le koluma faʻamaumauga o aso mo le x-axis, ma le total_posts koluma mo le y-axis.
x = fa'amaumauga ['aso'].tolist()
y = fa'amaumauga ['total_posts'].tolist()
Matou te vaʻaia i le faʻaaogaina o le visualize_bar_chart galuega ma faʻasaoina o se ata.
plt = visualize_bar_chart(x=x, x_label='Aso', y=y, y_label='Aofa'i meli', title='Meli i Aso Ta'itasi')
plt.savefig('viz.png')
Matou te afifi lenei code i se galuega e taʻua get_and_save_image.
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')Auina atu se ata
Ina ia auina atu se lipoti i le tagata e mauaina, e tatau ona e iloa le chat_id parameter.
Matou te faaaogaina ma ta'i/amata. E tali mai le bot ma faʻamatalaga talafeagai, chat_id o loʻo i totonu o le id field.
Se'i o tatou faia le galuega send_image. O le a fa'aogaina le get_and_save_image galuega e toe aumai ma fa'asaoina le ata. Ona matou auina atu lea o mea uma i le fesoʻotaʻiga saʻo.
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png','rb'))Polokalama autu
Mulimuli ane, matou te faia se isi galuega, autu, e faʻalauiloa ai le talosaga. Aua nei galo e sui YOUR_TOKEN mo le bot.
Manatua: o lenei polokalame o le a otometi lava ona auina atu le ata i le taimi e te faʻamaonia. Mo se faʻataʻitaʻiga, o le a matou lafoina se lipoti i le iva i le taeao i aso uma.
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()O se taunuuga, o la matou talosaga o le a pei o lenei:
from google.cloud import bigquery
from telegram.ext import Updater
import matplotlib.pyplot as plt
import numpy as np
import datetime
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframe
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return plt
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png', 'rb'))
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()Faasaoina le faila ma taʻua main.py.
Matou te faʻalauiloaina le talosaga e ala i le ulufale atu i le poloaiga i le laina:
python3 main.py
Ua sauni mea uma. O lea ua i ai le matou robot e aofia ai laina 50 o le tulafono e gaosia lipoti e aunoa ma le matou faʻalavelave.
Sei o tatou siaki le bot e ala i le filifilia o le /send command.
E mafai ona e maua le code ua mae'a ile .
Ua fautuaina e Skillbox:
- Lua tausaga a'oa'oga .
- Kosi i luga ole laiga .
- Kosi tausaga fa'atino .
puna: www.habr.com