With LINEBot, I made an app that informs me of the "bus time"

(1) Background of application creation

・ Since I moved, I started to use the bus for commuting. ・ I take a bus timetable with my iphone and check it from the "photos" one by one. ・ The commuting route is [Home ↔︎ (bus) ↔︎ Nearest station ↔︎ (train) ↔︎ Company station], especially on the way back, I want to get on the bus as soon as I arrive at the nearest station. ・ It is troublesome to perform the next operation every time you check the bus time. (Launch the photo app on your iphone) → (Search for the bus time taken from the photo app) ・ I want to know the bus time more efficiently. ・ I want to make something useful for my life using python, LINEBot, etc. that I am studying. ・ Although I'm diligent about studying, I'm getting tired of creating apps that imitate people. ・ The above is the background of this application creation. (Caution) The source is a beginner so it is dirty (please forgive me). (Note) I take the timetable from the csv file, test it in the local environment, and then deploy it to Heroku (there may be a more efficient way).

↓↓↓↓↓↓↓ (Completed image) ↓↓↓↓↓↓↓↓ When you send "Go" to LINE Bot, the image of the bus, the bus time from the bus stop closest to your home to the station, and the next bus time are returned. When you say "return", the bus time from the nearest station to the bus stop closest to your home will be returned.

(2) What not to write in this article and the structure of the article

① Do not write in this article

・ How to create a LINE Bot channel ・ Details on how to deploy to Heroku

② Article structure

(1) Background of application creation (2) What not to write in this article and the structure of the article (3) Environment construction (4) Operation check in local environment (5) Incorporate into LINE Bot using Heroku

(3) Environment construction

・ Mac ・ Python3 ・ Sqlite3 ・ Postgresql ・ Heroku ・ Flask

First, create a directory linebot_jikokuhyou on your desktop, build a virtual environment in the directory, and start it.

python3 -m venv .
source bin/activate

(4) Operation check in local environment

First, test in your local environment. The database uses sql. Import the csv file prepared in advance into sql and verify whether the program works normally locally with Flask.

① Prepare the working directory and working file

Prepare directories and files as shown below. iki.csv and kaeri.csv use the files prepared by yourself (described later). Files other than the above will be created as empty files.

linebot_jikokuhyou
├csv_kakou.py
├csv_to_sql.py
├local_main.py
├jikoku_main.py
├assets
│  ├database.py
│  ├models.py
│  ├__ini__.py
│  ├iki.csv (csv file prepared by yourself)
│  └kaeri.csv (csv file prepared by yourself)
│ 
└templates
   ├test.html
   └test2.html

(Supplemental explanation)

-The following timetable is prepared as a csv file (sample). (↓↓↓↓↓↓ Bus timetable from the stop near your home to the nearest station) (↓↓↓↓↓↓ Bus timetable from the nearest station to the stop near your home)

② Create a jikoku.csv file and execute it

-Create jikoku.csv by processing the timetable iki.csv from your home to the nearest station and the timetable kaeri.csv from your nearest station to your home. -Create a file csv_kakou.py to create jikoku.csv. -If you execute the following, a file jikoku.csv that is a processed version of iki.csv and kaeri.csv will be created in the assets directory. ・ First of all, the processing of csv is finished.

.py:csv_kakou.py

#iki.Processing of csv
list = []
with open('assets/iki.csv',encoding='utf-8')as f:
    #Process to read line by line
    for i in f:
        columns = i.rstrip()
        list.append(columns)
list2 = []
for i in list:
    columns2 = i.split(',')
    for ii in range(len(columns2)):
        if ii != 0:
            list2.append(columns2[0]+'Time'+columns2[ii]+'Minutes')
list2.pop(0)
num = 1
with open('assets/jikoku.csv','w',encoding='utf-8')as f:
    go_or_come = 'To go'
    for time in list2:
        f.write(str(num) +','+time+','+str(go_or_come)+'\n')
        num+=1

#kaeri.Processing of csv
list = []
with open('assets/kaeri.csv',encoding='utf-8')as f:
    #Process to read line by line
    for i in f:
        columns = i.rstrip()
        list.append(columns)
list2 = []
for i in list:
    columns2 = i.split(',')
    for ii in range(len(columns2)):
        if ii != 0:
            list2.append(columns2[0]+'Time'+columns2[ii]+'Minutes')
list2.pop(0)
with open('assets/jikoku.csv','a',encoding='utf-8')as f:
    go_or_come = 'Return'
    for time in list2:
        f.write(str(num) +','+time+','+str(go_or_come)+'\n')
        num+=1

・ ↓↓↓↓↓↓↓ The jikoku.csv created in the assets directory is as follows (partial excerpt). There are 64 records in total.

③ Create database.py and models.py files in the assets directory

Create each in the assets directory.

.py:database.py

#coding: utf-8


from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime
import os

database_file = os.path.join(os.path.abspath(os.path.dirname(__file__)),'data.db')

engine = create_engine('sqlite:///' + database_file,convert_unicode=True,echo=True)
db_session = scoped_session(
                sessionmaker(
                    autocommit = False,
                    autoflush = False,
                    bind = engine
                )
            )
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    #Import models in the assets folder
    import assets.models
    Base.metadata.create_all(bind=engine)

.py:models.py

#coding: utf-8

from sqlalchemy import Column,Integer,String,Boolean,DateTime,Date,Text
from assets.database import Base
from datetime import datetime as dt

#Database table information
class Data(Base):
    #Table name settings,Set to the name data
    __tablename__ = "data"
    #Set Column information, set unique to False (meaning that the same value is accepted)
    #The primary key is required when searching for a row, usually set
    id = Column(Integer,primary_key=True)
    #Judge whether to go or return
    go_or_come = Column(Text,unique=False)
    #Numbering separately from the primary key
    num = Column(Integer,unique=False)
    #Timetable time
    time = Column(Text,unique=False)
    #timestamp
    timestamp = Column(DateTime,unique=False)

    #initialize
    def __init__(self,go_or_come=None,num=0,time=None,timestamp=None):
        self.go_or_come = go_or_come
        self.num = num
        self.time = time
        self.timestamp = timestamp

④ Create a csv_to_sql.py file

-Create a file csv_to_sql.py that reads csv data and writes it to sql data.

.py:csv_to_sql.py

from assets.database import db_session
from assets.models import Data

#Initialization process
from assets.database import init_db
init_db()

#Processing to write from csv to sql
with open('assets/jikoku.csv',encoding='utf-8')as f:
    for i in f:
        columns = i.rstrip().split(',')
        num = int(columns[0])#num is models.Since it is defined as an int type in py, I made it an int type
        time = columns[1]
        go_or_come = columns[2]
        row = Data(num=num,time=time,go_or_come=go_or_come)
        db_session.add(row)
        db_session.commit()

(Supplemental explanation)

-Init_db () initializes sql. -After de_session.add, write to sql by doing db_session.commit. -Check if it was written properly in sql. Go to the assets directory and enter the following to enter sqlite mode.

sqlite3 data.db

If you enter the following in sqlite,

select * from data;

The following was output, and I was able to confirm that the data was written to sql.

⑤ Create a jikoku.py file

-A file that acquires the specified time from the time saved in the sql database. -If'go'or'return' is assigned to the argument, the latest bus time from the current time and the next bus time are extracted from sql and returned as the return value.

.py:jikoku.py

from assets.database import db_session
from assets.models import Data
import datetime


def jikoku_search(route):
    #Read sql
    data = db_session.query(Data.num,Data.time,Data.go_or_come,).all()

    #Get current date and time (datetime type)
    date_todaytime = datetime.datetime.today()
    #Convert the above to str type
    str_todaytime = date_todaytime.strftime('%Y year%m month%d day%H o'clock%M minutes')
    #Of the current date and time, only ● year ● month ● day is acquired(date type)
    date = datetime.date.today()
    #Convert the above to str type
    str_date = date.strftime('%Y year%m month%d day')

    #Set variables
    bustime = ''
    next_bustime = ''

    #route classifies going and returning
    route = route
    #Extract the departure time of the latest bus and the departure time of the next bus from sql
    for i in data:
        str_sql = i[1]
        #Add the current date and time ● year ● month ● day to the time of sqr to make it “date and time”
        str_sql_rr = str_date + str_sql
        #Convert the above to datetime type
        date_sql_rr = datetime.datetime.strptime(str_sql_rr,'%Y year%m month%d day%H o'clock%M minutes')
        #Get the latest bus date and time compared to the current date and time
        if date_sql_rr > date_todaytime and i[2]== route:#go_or_If come matches route, do the following
            #Get the difference between the departure date and time of the latest bus and the current date and time
            date_sabun = date_sql_rr-date_todaytime
            #The difference of datetime is timedelta type. Since timedelta type cannot be made into str type with strftime, str()Made into str type
            #timedelta type is 0:00:Since it is 00 and the difference is within 1 hour from the timetable, "minutes" are extracted by slicing.
            if str(date_sabun)[0:1] == "0":
                bustime = 'The next bus is'+str_sql_rr+'Depart for.' + 'after' + str(date_sabun)[2:4] + 'It's a minute.'
            else:
                bustime = 'The next bus is'+str_sql_rr+'Depart for.' + 'after'+ str(date_sabun)[0:1] + 'time' + str(date_sabun)[2:4] + 'It's minutes.'

            #Get num of the departure date and time of the next bus
            next_num = i[0]
            #Get the departure time of the next bus (processing when there is the latest bus but the next bus exceeds the last train)
            try:
                _next_bustime = db_session.query(Data.num,Data.time,Data.go_or_come).all()[next_num].time
                #Add the current date and time ● year ● month ● day to the departure time of the next bus to make it “date and time”
                next_bustime = str_date + _next_bustime+'Depart for.'
            except:
                next_bustime="It's past the last train."

            #Processing to exit the for statement after getting the bus time
            break

        #Processing when the last train is over for both the latest bus and the next bus
        else:
            bustime="The next bus is past the last train."
            next_bustime="The next bus is also over the last train."


    return bustime,next_bustime

⑥ Create local_main.py

-Create local_main.py to verify that jikoku.py works properly in the local environment. -First, display test.html and read the sql database. -Since "go" and "return" are specified in test.html, the arguments for "go" and "return" are assigned to the jikoku_search method of jikoku.py, and the return value is set to test2.html. return.

.py:local_main.py

from flask import Flask,request,render_template
from assets.database import db_session
from assets.models import Data
import jikoku_main as jm

app = Flask(__name__)

@app.route('/')
def test():
    #Read from sql
    data = db_session.query(Data.num,Data.time,Data.go_or_come,).all()
    return render_template('test.html',data=data)

@app.route('/iki')
def test2():
    result1,result2 = jm.jikoku_search('To go')
    return render_template('test2.html',bustime=result1,next_bustime=result2)

@app.route('/kaeri')
def test3():
    result1,result2 = jm.jikoku_search('Return')
    return render_template('test2.html',bustime=result1,next_bustime=result2)

if __name__ == '__main__':
    app.run(debug=True)

⑦ Create test.html and test2.html in the templates directory

Create each in the templates directory as follows.

.html:test.html

<!DOCTYPE html>
<html lang='ja'>
  <head>
      <meta charset='utf-8'>
      <title>Jikokuhyou</title>
      <style>body{padding:10px;}</style>
  </head>


  <body>
    <form action='/iki' method='get'>
      <button type='submit'>To go</button>
    </form>
    <form action='/kaeri' method='get'>
      <button type='submit'>Return</button>
    </form>
  </body>

</html>

.html:test2.html

<!DOCTYPE html>
<html lang='ja'>
  <head>
      <meta charset='utf-8'>
      <title>Jikokuhyou</title>
      <style>body{padding:10px;}</style>
  </head>


  <body>
    {{'The latest bus is'+bustime}} <br>{{'The next bus is'+next_bustime}}
    <form action='/' method='get'>
      <button type='submit'>Return</button>
    </form>
  </body>
</html>

⑧ Create a \ __ init__ file in the assets directory

-\ Init is a file required to read database.py and models.py as modules from app.py. Nothing is described inside.

.py:__init__.py

⑨ Verify in local environment

-First, execute csv_to_sql.py to initialize the database, read from csv to sql, and create data.db. -Next, run local_main.py to start Flask and check it in your browser. ・ ↓↓↓↓↓↓↓ (Browser confirmation) Press either the "Go" or "Return" button. ・ ↓↓↓↓↓↓↓ (Browser confirmation) It was displayed properly (current time is 2:13)

At this point, you can properly extract the bus timetable in your local environment.

(5) Incorporate into LINE Bot using Heroku

-Since it was verified in the local environment, it is finally converted to LINE Bot using Heroku.

① Prepare the working directory and working file

-Add directories and files as shown below. -For reference, add the data.db and jikoku.csv created above.

linebot_jikokuhyou
├csv_kakou.py
├csv_to_sql.py
├local_main.py
├jikoku_main.py
├main.py (additional)
├requirments.txt (additional)
├runtime.txt (additional)
├Procfile (additional)
├assets
│  ├database.py
│  ├models.py
│  ├data.db (files created so far)
│  ├__ini__.py
│  ├jikoku.csv (files created so far)
│  ├iki.csv
│  └kaeri.csv
│ 
└templates
   ├test.html
   └test2.html

② Create main.py

-First, use line-sdk to create a file main.py that notifies the bus time. -Import the jikoku_main module and use the jikoku_search method of jikoku_main.

.py:main.py

from flask import Flask, request, abort
from linebot import (
    LineBotApi, WebhookHandler
)
from linebot.exceptions import (
    InvalidSignatureError
)
from linebot.models import (
    MessageEvent, TextMessage, TextSendMessage,LocationMessage,ImageSendMessage
)
import os
import jikoku_main as jm


app = Flask(__name__)

YOUR_CHANNEL_ACCESS_TOKEN = os.environ["YOUR_CHANNEL_ACCESS_TOKEN"]
YOUR_CHANNEL_SECRET = os.environ["YOUR_CHANNEL_SECRET"]
line_bot_api = LineBotApi(YOUR_CHANNEL_ACCESS_TOKEN)
handler = WebhookHandler(YOUR_CHANNEL_SECRET)

@app.route("/")
def hello_world():
    return "hello world!"


@app.route("/callback", methods=['POST'])
def callback():
    signature = request.headers['X-Line-Signature']
    body = request.get_data(as_text=True)
    app.logger.info("Request body: " + body)

    try:
        handler.handle(body, signature)
    except InvalidSignatureError:
        abort(400)
    return 'OK'



@handler.add(MessageEvent, message=TextMessage)
def handle_message(event):
    if 'To go' in event.message.text:
        result1,result2 = jm.jikoku_search('To go')
        line_bot_api.reply_message(
            event.reply_token,
            [
            ImageSendMessage(original_content_url='https://www.photolibrary.jp/mhd5/img237/450-2012011014412960119.jpg',
            preview_image_url='https://www.photolibrary.jp/mhd5/img237/450-2012011014412960119.jpg'),
            TextSendMessage(text=result1),
            TextSendMessage(text=result2)
            ]
        )


    if 'Return' in event.message.text:
        result1,result2 = jm.jikoku_search('Return')
        line_bot_api.reply_message(
            event.reply_token,
            [
            ImageSendMessage
(original_content_url='https://www.photolibrary.jp/mhd5/img237/450-2012011014412960119.jpg',
            preview_image_url='https://www.photolibrary.jp/mhd5/img237/450-2012011014412960119.jpg'),
            TextSendMessage(text=result1),
            TextSendMessage(text=result2)
            ]
        )



if __name__ == "__main__":
    port = int(os.getenv("PORT", 5000))
    app.run(host="0.0.0.0", port=port)

(Supplemental explanation)

・ For the overall structure of LINE Bot, refer to Let's use LINE BOT (CallBack program: reception). It was. -When sending multiple texts with LINEBot, use a list. -When sending images with LINEBot, it is limited to https type and jpeg. I referred to the following site. [Introduction to Python] Send images and sentences from the official account using the Line API python line bot imagemap image transmission I tried using line-bot-sdk-python ・ I used here for the bus illustration.

③ Modify database.py

-Modify database.py when using Heroku's postgresql. -Specifically, describe the process of going to see the environment variable on Heroku called environ and getting the database called DATABASE_URL. -The URL of the connection destination is set in environ. Also, by adding or, we decided to refer to sqlite as a database in the local environment. -If you are connected to heroku, refer to the url of postgresql, and if you are not connected, go to sql.

.py:database.py

#coding: utf-8

#database.py/File that handles initial settings of which database to use, such as sqlite
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base

import datetime
import os

#data_Named db, database.Where py is (os.path.dirname(__file__)), With an absolute path (os.path.abspath）、data_Save the db Save the path.
database_file = os.path.join(os.path.abspath(os.path.dirname(__file__)),'data.db')

#Using database sqlite (engin)、database_data stored in file_Use db and issue sqlite when running with echo (echo=True)
#engine = create_engine('sqlite:///' + database_file,convert_unicode=True,echo=True)
engine = create_engine(os.environ.get('DATABASE_URL') or 'sqlite:///' + database_file,convert_unicode=True,echo=True)
db_session = scoped_session(
                sessionmaker(
                    autocommit = False,
                    autoflush = False,
                    bind = engine
                )
            )

#declarative_Instantiate base
Base = declarative_base()
Base.query = db_session.query_property()


#Function to initialize the database
def init_db():
    #Import models in the assets folder
    import assets.models
    Base.metadata.create_all(bind=engine)

④ Set environment variables in Heroku

・ First, access LINE Develpers, register, and create a new channel (explanation is omitted). -After creating the channel, copy the "access token string" and "channel secret string" described in LINE Developers. -Access Heroku and create a new application. -Initialize git and associate it with Heroku. -Set the "access token string" and "channel secret string" to the Heroku environment variables. -For example, heroku config: set YOUR_CHANNEL_ACCESS_TOKEN = "Channel access token string" -a (app name)

heroku config:set YOUR_CHANNEL_ACCESS_TOKEN="Channel access token string" -a (app name)
heroku config:set YOUR_CHANNEL_SECRET="Channel secret string" -a (app name)

Make sure the environment variables are set properly on heroku.

heroku config

⑤ Create other necessary files

-Create Procfile, runtime.txt, requirements.txt. -Create runtime.txt after checking your own python version.

.txt:runtime.txt

python-3.8.2

web: python main.py

Requirements.txt is described by entering the following in the terminal.

pip freeze > requirements.txt

⑤ Push and deploy to Heroku

-Deploy according to the following procedure. -The commit name is the-first.

git add .
git commit -m'the-first'
git push heroku master
Heroku open

↓ ↓ ↓ ↓ ↓ When you open Heroku and check it with a browser, if "hello world!" Is displayed, the deployment is completed successfully.

⑥ Write the database to postgresql

-Set the Heroku database postgresql and write csv data to the database. -Set postgresql from the resource of the Heroku app.

-Execute the bash command so that you can type the command in the Heroku environment. -After that, execute csv_to_sql.py. -By doing this, postgresql is initialized and csv data is written to postgresql.

heroku run bash

python3 csv_to_sql.py

Make sure it is written properly. Enter the following command.

heroku pg:psql

Command to list the data in the table
select *from (table name);

I was able to confirm that the following was output and that it was properly written to postgresql.

⑦ Set up LINE Developers webhook

Set the URL for the LINE Developers webhook and turn on the use of the webhook (details are omitted). Register as a friend and launch LINE Bot to complete.