More than 1 year has passed since last update.

CIST (公立千歳科学技術大学)Advent Calendar 2022

AWSでdiscordbotを動かして勤務時間マネジメントさせてみた

Last updated at 2022-12-23Posted at 2022-12-19

開発環境
Amazon Linux release 2 (Karoo)
mysql 5.6.51
Python 2.7.18

ローカル　
macOS Ventura 13.1

開発背景

大学の授業で毎度お馴染み「開発環境は与える。好きなモノ作れ」系の最終課題が出題された。環境としてAWSの利用料金100$が与えられた。
私は大学の先生のもとでプログラマーのアルバイトをしているがその勤務管理に時間を取られるため勤務時間をマネジメントしてくれるチャットボットを作成しようと考えた。

これが実際行なっているdiscordの勤務時間の報告である。月末に先生から「今月の勤務時間○時間で合ってる？」と聞かれ会話の検索を利用し手元の計算機で計算している。
あまりにも非効率的すぎる。情報系ならもう少しスマートに行いたい！
開発のゴールとしてはとりあえず任意の月の勤務時間の合計を自動で出力までをゴールとする。

開発

まずEC2でインスタンスを起動しサーバを立ち上げよう。

インバウンドルールで22番ポートが解放されていることを確認したらsshでローカルからサーバに遠隔で侵入する。

sshの書式

ssh -i {鍵のパス} ec2-user@{パブリックIPv4アドレス}

ログイン出来たら開発環境が整った。それじゃあこのサーバに今回利用するdiscord.pyをインストールする。
discrod.pyの詳細についてはこちらをご覧ください。

pip3 install -U discord

いよいよコーディング...の前に今回作成するdiscordbotの作成から入ります。こちらは非常に簡単で公式でのドキュメントもあるのでこちらを参考にしbotをサーバに招待するところまで進めましょう。

それではコーディングを始めていきます。任意のディレクトリでtouch main.pyとtouch .envを実行し二つのファイルを作成しましょう。
vim main.pyでmain.pyとvim .envで2つのファイルを以下のように編集します。

main.py

import discord
from dotenv import load_dotenv
from discord.ext import commands
import os

load_dotenv()
TOKEN = os.getenv('TOKEN')

@bot.event
async def on_ready():
    print('起動完了')

bot.run(TOKEN)

.env

TOKEN=botのTOKEN

ここまで出来たらmain.pyが置いてあるディレクトリでpython3 main.pyと実行する。コンソールに「起動完了」と出力されればbotの動作確認は終了。

MySQLのセットアップ

それでは次にデータベースの設定を行う。今回はDBMSとしてMySQLを採用する。
「sudo yum localinstall -y https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm」を実行しMySQLのリポジトリをインストールする。
次に「sudo yum install -y mysql-community-server」を実行しMySQLをインストールする。
最後に動作確認を行う「sudo systemctl start mysqld」を実行し
「sudo systemctl status mysqld」を実行すると
Active：active（running）と表示されればMySQLのセットアップは完了

テーブル作成

コンソールに「mysql -uroot」と入力する。そうするとmysql>と表示される。
そこで「CREATE DATABASE {データベース名};」と入力する。

mysql>CREATE DATABASE MyApp;

この場合データベースの名前はMyAppとなる。すると次のように表示される

mysql> CREATE DATABASE MyApp;
Query OK, 1 row affected (0.00 sec)

これでデータベースの作成は完了。次に「use {データベース名};」と入力する。

mysql> use MyApp
Database changed

このように表示されればOK!

テーブルを作成していきます。人によって作りたいモノはそれぞれだと思うのでここからは参考程度に
自分の場合のテーブル構造は以下のように作りたい

id	name	year	month	day	time	start_time	end_time	content
各ユーザのid	名前	登録した年	登録した月	登録した日	勤務時間	勤務開始時間	勤務終了時間	勤務内容

このテーブルを作成するためのクエリはを打ち込む。テーブル名はIRとしている

mysql> CREATE TABLE IR (id INT,name VARCHAR(40),year INT,month INT,day INT,time FLOAT,start_time VARCHAR(10),end_time VARCHAR(10),content VARCHAR(80) NOT NULL);

打ち込みQuery　OK!と表示されればテーブル作成完了。

データベースと接続

今回ドライバとしてmysql-connector-pythonを採用する。こいつの使い方に困った時公式ドキュメントを参照していただきたい

main.pyからテーブルへのデータ挿入を可能にするためdb.pyを作成する。main.pyが置いてあるディレクトリでtouch db.pyを入力する。vim db.pyで以下のように編集する。

db.py

import mysql.connector
from mysql.connector import Error
class DB:
    def __init__(self,
            db_name,
            user_password,
            user_name,
            host_name
            ):
        self.db_name=db_name
        self.user_password=user_password
        self.user_name=user_name
        self.host_name=host_name

    def create_db_connection(self):
        connection = None
        try:
            connection = mysql.connector.connect(
                host=self.host_name,
                user=self.user_name,
                passwd=self.user_password,
                database=self.db_name
            )
            print("MySQL Database connection successful")
        except Error as err:
            print(f"Error: '{err}'")
        return connection
    def execute_query(self,connection, query):
        cursor = connection.cursor()
        try:
            cursor.execute(query)
            connection.commit()
            print("Query successful")
            #return connection
            return "データベースに登録されました"
        except Error as err:
            print(f"Error: '{err}'")
            return err
    def read_query(self,connection, query):
        cursor = connection.cursor()
        result = None
        try:
            cursor.execute(query)
            result = cursor.fetchall()
            return result
        except Error as err:
            return err

簡単にコードの説明を行う
__init__という部分ではコンストラクタを表しておりこのインスタンス生成された時のフィールド値に値を代入する。
excute_queryはクエリの実行、read_queryも同じくクエリの実行だが主にSELECT文を使うためのもの。

「vim .env」で.envの追加も今行っておこう。

.env

PASS={mysqlに入る時のパスワード}
USER_NAME={mysqlに入る時のユーザ名}
HOST=localhost
DB=MyApp

これでデータベースへのCRUD処理ができる状態になった。

データベースへのINSERT

main.pyを「vim main.py」で編集し以下のように編集する。

main.py

import os
import re
import discord
from dotenv import load_dotenv
from discord.ext import commands
from datetime import datetime, timedelta, timezone
from db import DB
import mysql.connector

load_dotenv()
TOKEN = os.getenv('TOKEN')
db_name=os.getenv('DB')
user_name=os.getenv('USER_NAME')
host_name=os.getenv('HOST')
user_password=os.getenv('PASS')
bot=commands.Bot(command_prefix="!")
db=DB(db_name,user_password,user_name,host_name)

@bot.event
async def on_ready():
    db=DB(db_name,user_password,user_name,host_name)
    print('起動完了')
    
@bot.event
async def on_message(message):
    if message.author.bot:
        return
    reg_res=re.compile(r'^(\d{4})-(\d{4})').search(message.content)
    if(reg_res):
        m = re.match(r'^(\d{4})-(\d{4})', message.content)
        now=datetime.now()
        print(now)
        work_time=time(m.group(1),m.group(2))
        query=f"INSERT INTO IR VALUES('{message.author.id}','{message.author.name}',{now.year},{now.month},{now.day},{work_time},{m.group(1)},{m.group(2)},'{message.content[10:]}')"
        print(query)
        con=db.create_db_connection()
        msg=db.execute_query(con,query)
        await message.channel.send(msg)
    elif(re.compile(r'^\d+月の集計').search(message.content)):
        m = re.match(r'^(\d*)月の集計', message.content)
        query=f"SELECT name,SUM(time) FROM IR WHERE month={m.group(1)} GROUP BY name;"
        print(query)
        con=db.create_db_connection()
        results=db.read_query(con,query)
        embed = discord.Embed(title=f"{m.group(1)}月集計結果")
        embed.set_author(name="勤務管理bot")
        for result in results:
            embed.add_field(name=result[0],value=f"{result[1]}時間",inline=False)
        await message.channel.send(embed=embed)
    elif(message.content=="テーブル一覧"):
        query="SELECT * FROM IR;"
        con=db.create_db_connection()
        results=db.read_query(con,query)
        embed = discord.Embed(title="IRテーブル")
        embed.set_author(name="勤務管理bot")
        name=""
        work=""
        content=""
        for result in results:
            result=list(result)
            name+=result[1]+"\n"
            work+=f"{result[5]}時間\n"
            content+=result[8]+"\n"
        embed.add_field(name="名前",value=name,inline=True)
        embed.add_field(name="勤務時間",value=work,inline=True)
        embed.add_field(name="勤務内容",value=content,inline=True)
        await message.channel.send(embed=embed)
    else:
        pass

def time(start,end):
    try:
        startT = datetime.strptime(start,'%H%M')
        endT = datetime.strptime(end,'%H%M')
        time=endT-startT
        time=float(time.total_seconds())/60/60
        return round(time,3)
    except ValueError as e:
        return e
bot.run(TOKEN)

これについても少しコードの説明を行う。

@bot.event
async def on_ready():
    db=DB(db_name,user_password,user_name,host_name)
    print('起動完了')

ここで起動の際先ほど作成したクラスのインスタンスを生成してしまう

@bot.event
async def on_message(message):
    if message.author.bot:
        return
    reg_res=re.compile(r'^(\d{4})-(\d{4})').search(message.content)
    if(reg_res):
        m = re.match(r'^(\d{4})-(\d{4})', message.content)

        now=datetime.now()
        print(now)
        work_time=time(m.group(1),m.group(2))
        query=f"INSERT INTO IR VALUES('{message.author.id}','{message.author.name}',{now.year},{now.month},{now.day},{work_time},{m.group(1)},{m.group(2)},'{message.content[10:]}')"
        print(query)
        con=db.conect_db()
        con=db.create_db_connection()
        msg=db.execute_query(con,query)
        await message.channel.send(msg)

新たに追加されたこの部分はメッセージが送信された時のイベント処理を記載しているいつも勤務時間の報告は
{4桁の数字}-{4桁の数字}
{勤務内容}
であるため正規表現でキャッチする。個人的に正規表現に慣れていないためこれで合ってるかは不安ですがキャッチできているので大丈夫でしょう
query=...でクエリを作成します。messageの筆者id,名前,....業務内容
そしたらawait message.channel.send(msg)でクエリが実行されたかどうかを送信します。

登録できたら次は集計です

elif(re.compile(r'^\d+月の集計').search(message.content)):
        m = re.match(r'^(\d*)月の集計', message.content)
        query=f"SELECT name,SUM(time) FROM IR WHERE month={m.group(1)} GROUP BY name;"
        print(query)
        con=db.conect_db()
        con=db.create_db_connection()
        results=db.read_query(con,query)
        embed = discord.Embed(title=f"{m.group(1)}月集計結果")
        embed.set_author(name="勤務管理bot")
        for result in results:
            embed.add_field(name=result[0],value=f"{result[1]}時間",inline=False)
        await message.channel.send(embed=embed)

ここではユーザが「○月の集計」と送ったらその月の勤務時間合計をSELECT文を用いて表示しています。
discordには表という概念はないのでEmbed(埋め込みテキスト)を用いて書式を整えていきます。Embedはbotのみ利用可能なのでおすすめです。

query=f"SELECT name,SUM(time) FROM IR WHERE month={m.group(1)} GROUP BY name;"

このクエリでIRテーブルから登録されている名前ごとに勤務時間の合計と名前をIRテーブルから取り出します。

大学2年の頃選択授業だったけどデータベースの授業履修していて良かった！

返り値はタプル型ですがお恥ずかしながらタプルの扱いがわからないためリストに毎度変換しています。

elif(message.content=="テーブル一覧"):
        query="SELECT * FROM IR;"
        con=db.conect_db()
        con=db.create_db_connection()
        results=db.read_query(con,query)
        embed = discord.Embed(title="IRテーブル")
        embed.set_author(name="勤務管理bot")
        name=""
        work=""
        content=""
        for result in results:
            result=list(result)
            name+=result[1]+"\n"
            work+=f"{result[5]}時間\n"
            content+=result[8]+"\n"
        embed.add_field(name="名前",value=name,inline=True)
        embed.add_field(name="勤務時間",value=work,inline=True)
        embed.add_field(name="勤務内容",value=content,inline=True)
        await message.channel.send(embed=embed)

こいつはテーブル情報の確認用です

実行

長くなってしまいましたが実際にAWS上で動かしてみて集計してみましょう
main.pyがあるディレクトリで「python3 main.py」と入力しdiscordで勤務管理を行わせてみます。

しっかり登録されたみたいです。

次にテーブルを確認してみます

上3つの「aws構築、pythonコーディング,AWSイジイジ」と下2つの「プロジェクトのコーディング、qiita書いた」は自分なのですが私一人だけの結果を出しても面白くないし集計はユーザごとに行うため大学の友達に協力していただきました。
「睡眠、資料作成、アルバイト」はそれぞれ違う人のデータであるためこのテーブルには自分含め4人のデータが入っていることになります。

最後に12月の集計を行います。
しっかり4人分の集計が行われています。上から3つ目が自分ですね

振り返り&考察&今後の展望

振り返り

しっかり当初の目的である月の勤務時間の管理ができている。
公式がリリースしているAPI使えばめっちゃ簡単にbotの実装ができる
AWSでサーバを立ててアプリケーションを動かすのは金なし大学生では難しいが環境を提供していただける授業はありがたい
大学のアドカレの締め切りが25日(現在23日)だから急いで作ったため設計がめちゃくちゃ。もっと関数化、クラス化した方が良かったかも

考察

テーブルに登録した年、月、日を挿入してるから年単位、日単位の集計もクエリ次第でできそう

今後の展望

せっかくなのでAmazon auroraとか使ってみたい
最終課題の締め切りは冬休み明けなのであとの期間でエラーハンドリングとかをやっておく

参考文献

MySQL Connector/Python Developer Guide
Python で SQL データベースを作成して操作する方法
 discord公式ドキュメント

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up