Momwe mungagawire malipoti osavuta ku robot. Kulemba bot mu Python ndi Google BigQuery

Kodi muli ndi ntchito zomwe zimabwereza tsiku ndi tsiku, sabata ndi sabata? Mwachitsanzo, kulemba malipoti. Mumapempha deta, kusanthula, kuwona m'maganizo (pangani ma graph ndi ma chart), ndiyeno tumizani kwa abwana anu. Koma bwanji ngati mutapanga zonsezi?

Mu phunziro ili, tipanga telegalamu bot yomwe ingathandize kuchitira malipoti. Ndipo gawo labwino kwambiri ndilakuti, pulogalamu yonseyo ingokhala mizere 50 yamakhodi! Ngati mukupanga bot ya Telegraph kwa nthawi yoyamba, mungafunenso kuwerenga iyi. positi.

Skillbox imalimbikitsa: Njira yothandiza Wopanga Python kuyambira poyambira.

Tikukukumbutsani: kwa owerenga onse a Habr - kuchotsera ma ruble 10 polembetsa maphunziro aliwonse a Skillbox pogwiritsa ntchito nambala yotsatsira ya Habr.

Tiyeni tiyambe

Kukhazikitsa malaibulale

Tidzagwiritsa ntchito google-cloud-bigquery kuti mutengenso data kuchokera ku Google BigQuery. matloti, osasamala и panda zidzathandiza kuwona deta. python-telegram-bot adzatumiza zomwe zamalizidwa ku Telegraph.

pip3 kukhazikitsa google-cloud-bigquery matplotlib numpy pandas python-telegram-bot

Kulumikiza API ya Google BigQuery

Ngati tikufuna kugwiritsa ntchito ntchitoyi, tifunika kulumikizana ndi API ya Google BigQuery. Kuti muchite izi, pitani ku Google Developers Console ndi kupanga pulojekiti yatsopano (kapena sankhani yomwe ilipo).

Mugawo lowongolera, sankhani THANDIZANI APIS NDI SERVICES ndikuyang'ana BigQuery API.

Sankhani Yambitsani kulumikiza API.

Pangani kiyi ya akaunti

Tikunyamukanso Google Developers Console, sankhani tabu ya Credentials, Pangani zidziwitso ndi kiyi ya akaunti ya Service.

Ndiye - Akaunti Yatsopano yautumiki, ndipo mu gawo la dzina la akaunti ya Service lowetsani dzina.

Kuchokera pamndandanda wotsitsa wa Maudindo, sankhani Project> Mwini, kenako Pangani.

Fayilo yomwe idzatsitsidwe yokha imatchedwa creds.json.

Khazikitsani GOOGLE_APPLICATION_CREDENTIALS pofotokoza njira yopita ku creds.json patheshoni.

tumizani kunja GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'

Ngati zonse zidayenda bwino, ndi nthawi yoti muyambe kulemba pulogalamuyi.

Kupanga pulogalamu

Pa phunziroli, tigwiritsa ntchito data kuchokera ku bigquery-public-data.stackoverflow, ndipo pa lipoti lathu, tidzasankha kuchuluka kwa zolemba zatsiku ndi tsiku.

Zonse ndi zophweka.

Funsani patebulo -> Onani m'maso mwazo data -> Sungani zowonera -> Tumizani chithunzicho

Tiyeni tipange ntchito imodzi kuti tifotokoze ulusi uliwonse.

Funso ku BigQuery

Choyamba, tiyeni titenge kunja laibulale.

kuchokera ku google.cloud import bigquery

Timapanga ntchito yotchedwa query_to_bigquery, pomwe gawo ndi funso.

def query_to_bigquery(query):
    client = bigquery.Client()
    query_job = client.query(query)
    result = query_job.result()
    dataframe = result.to_dataframe()
    return dataframe

Ntchitoyi idzabwezera funso ngati data frame.

Kuwona deta

Kuti tithetse vutoli, timasankha matplotlib.

tumizani matplotlib.pyplot ngati plt

Timafunikira magawo asanu, pomwe x ndi data ya x-axis, x_label ndiye mutu wa axis, y ndi data ya y-axis, y_label ndiye mutu wa axis, ndipo mutu ndi mutu wa chiwonetsero chonse.

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

Kusunga chithunzi

Tsopano tiyeni tigwiritse ntchito ntchito ziwiri kuti tipange zowonera ndikuzisunga.

Tikutumizirani kuchuluka kwa zolemba zomwe zimasindikizidwa tsiku lililonse. Choyamba, tilemba funso.

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
        """

Funsoli limasonkhanitsa deta kwa milungu iwiri, kuyambira pa Disembala 2, 2018.

Timagwiritsa ntchito tsikuli chifukwa 2018-12-02 ndi data yaposachedwa kwambiri yojambulidwa mu bigquery-public-data.stackoverflow.post_history, apo ayi mutha kugwiritsa ntchito CURRENT_DATE() kuti mupeze zatsopano.

Timayitana ntchito ya query_to_bigquery kuti tipeze zambiri.

dataframe = query_to_bigquery(funso)

Kenako timagwiritsa ntchito gawo la deti la x-axis ndi total_posts column ya y-axis.

x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()

Timaziwona pogwiritsa ntchito visualize_bar_chart ndikusunga ngati chithunzi.

plt = visualize_bar_chart(x=x, x_label='Tsiku', y=y, y_label='Zolemba Zonse', title='Zolemba Tsiku ndi Tsiku')
plt.savefig('viz.png')

Timakulunga code iyi mu ntchito yotchedwa 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')

Kutumiza chithunzi

Kuti mutumize lipoti kwa wolandira, muyenera kudziwa chat_id parameter.

Timagwiritsa ntchito userinfobot ndi lembani /kuyamba. Bot imayankha ndi zofunikira, chat_id ili m'munda wa id.

Tsopano timapanga ntchito ya send_image. Idzagwiritsa ntchito get_and_save_image kuti itenge ndikusunga chithunzicho. Kenako timatumiza kwa oyenerera.

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'))

Pulogalamu yayikulu

Pomaliza, pangani ntchito ina, yayikulu, kuti mutsegule pulogalamuyi. Osayiwala kusintha YOUR_TOKEN pa bot.

Kumbukirani: pulogalamuyi idzatumiza chithunzicho nthawi yomweyo. Mwachitsanzo, timatumiza lipoti nthawi ya 9:00 AM tsiku lililonse.

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()

Pomaliza, ntchito yathu idzawoneka motere:

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()

Sungani fayilo ndikuyitcha main.py.

Yambitsani pulogalamuyi polemba lamulo ili mu terminal:

python3 main.py

Zonse zakonzeka. Tsopano tili ndi bot, yomwe ili ndi mizere 50 ya code, yomwe imapanga malipoti popanda kulowererapo.

Tiyeni tiwone bot kuchokera pano, posankha / kutumiza lamulo.

Mutha kupeza code yomalizidwa GitHub yanga.

Skillbox imalimbikitsa:

• Zaka ziwiri zothandiza maphunziro "Ndine wopanga mawebusayiti a PRO".
• Njira yapaintaneti "C # wopanga kuchokera ku 0".
• Maphunziro othandiza a chaka chimodzi "Wopanga PHP kuchokera ku 0 kupita ku PRO".

Source: www.habr.com