Python Flask Mysql 訪問者の登録

プロジェクト作成

$ mkdir app2
app2ディレクトリ内に作成
index.py 作成
templatesディレクトリ作成
 その中に index.html hello.html insert.html select.html delete.html 作成

index.py

from flask import Flask
from flask import render_template
from flask import request
import datetime

import mysql.connector as mydb
# コネクションの作成
connector = mydb.connect(
    host='192.168.10.71',
    user='aaaaa',
    password='bbbbb',
    database='wordpress_db',
    charset="utf8"
)

app = Flask(__name__)

@app.route('/')
def main():
    ipad=request.remote_addr
    dt_now = datetime.datetime.now()
    props = {'title': 'Flask & Mysql - index', 'msg': 'Welcome to Visitor Page.'}
    html = render_template('index.html', props=props,ipadress=ipad,dt_now=dt_now)
    return html

@app.route('/hello')
def hello():
    props = {'title': 'Flask & Mysql - hello', 'msg': 'Hello Visitor.'}
    html = render_template('hello.html', props=props)
    return html

@app.route('/insert')
def insert():
    vip= request.remote_addr
    vdt= datetime.datetime.now()
    props = {'title': 'Visitor Insert Result', 'msg': 'Visitor Insert Result'}
    val = (vip, vdt)
    cursor = connector.cursor()
    sql = "INSERT INTO wp_homonsha VALUES (%s,%s)"
    cursor.execute(sql,val)
    kensuu = cursor.rowcount
    html = render_template('insert.html', props=props, kensuu=kensuu)
    connector.commit()
    cursor.close()
    #connector.close()
    return html
    
@app.route('/select')
def select():
    ipad=request.remote_addr
    dt_now = datetime.datetime.now()
    props = {'title': 'Visitor List', 'msg': 'Visitor List'}
    cursor = connector.cursor()
    sql = "SELECT * from wp_homonsha"
    cursor.execute(sql)
    users = cursor.fetchall()
    html = render_template('select.html', props=props, users=users)
    cursor.close()
    #connector.close()
    return html
        
@app.route('/delete')
def delete():
    ipad=request.remote_addr
    dt_now = datetime.datetime.now()
    props = {'title': 'Visitor Delete Result', 'msg': 'Visitor Delete Result'}
    cursor = connector.cursor()
    sql = "delete from wp_homonsha Where ip=" + "'" + ipad + "'"
    cursor.execute(sql)
    kensuu = cursor.rowcount
    html = render_template('delete.html', props=props, kensuu=kensuu)
    connector.commit()
    cursor.close()
    #connector.close()
    return html
    
@app.errorhandler(404)
def not_found(error):
    return redirect(url_for('main'))

if __name__ == '__main__':
    #app.run(debug=True)
    app.run(host="192.168.10.71")

index.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>{{props.title}}</title>
    </head>
    <body bgcolor="#daefef">
        <div id="contents">
            <p>{{props.msg}}</p>
            <p>訪問者：[　{{ipadress}}　]</p>
            <p>日　時：[　{{dt_now}}　]</p>
        </div>
        <hr>
        <ul>
            <li><a href="/hello">Hello Visitor</a></li>
            <li><a href="/insert">[　登　録　]</a></li>
            <li><a href="/select">[　照　会　]</a></li>
            <li><a href="/delete">[　消　去　]</a></li>
        </ul>
    </body>
</html>

hello.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>{{props.title}}</title>
    </head>
    <body bgcolor="#daefef">
        <div id="contents">
            <p>{{props.msg}}</p>
        </div>
        <hr>
        <ul>
            <li><a href="/">Home</a></li>
        </ul>
    </body>
</html>

insert.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>{{props.title}}</title>
    </head>
    <body bgcolor="#daefef">
        <div id="contents">
            <p>{{props.msg}}</p>
            <hr>
            <p>{{kensuu}} 件　登録しました</p>            
        </div>
        <hr>
        <a href="/">Home</a>
    </body>
</html>

select.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>{{props.title}}</title>
    </head>
    <body bgcolor="#daefef">
        <div id="contents">
            <p>{{props.msg}}</p>
            <hr>
            <table class="table table-bordered" border='1' id="dataTable" width="350" cellspacing="0">
                <tr>
                    <th align="center" bgcolor="pink">訪　問　者</th>
                    <th align="center" bgcolor="pink">日　　　時</th>
                </tr>
                {% for user in users %}
                <tr>
                    <td align="center" bgcolor="white">{{user[0]}}</td>
                    <td align="center" bgcolor="white">{{user[1]}}</td>
                </tr>
                {% endfor %}
            </table>
        </div>
        <hr>
        <a href="/">Home</a>
    </body>
</html>

delete.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>{{props.title}}</title>
    </head>
    <body bgcolor="#daefef">    	
        <div id="contents">
            <p>{{props.msg}}</p>
            <hr>
            <p>{{kensuu}} 件　消去しました</p>       
        </div>
        <hr>
        <a href="/">Home</a>
    </body>
</html>

PAGE TOP