--Connect to phpMyAdmin with the AWS public IP / phpmyadmin / set earlier" --Create a db called test --Click ontest, select SQL` at the top and enter it
SQL statement
CREATE TABLE players (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(32),
level INT,
job_id INT
);
CREATE TABLE jobs (
id INT NOT NULL PRIMARY KEY,
job_name VARCHAR(10),
vitality INT,
strength INT,
agility INT,
intelligence INT,
luck INT
);
INSERT
INTO players(id,name,level,job_id)
VALUES
(1,"Taki",12,6),
(2,"Ken",7,2),
(3,"Rin",1,1),
(4,"Yu",3,3),
(5,"Claire",10,4),
(6,"show",5,2),
(7,"Cherry Blossoms",7,1),
(8,"Jack",5,4),
(9,"Lock",12,6),
(10,"Jun",1,NULL);
INSERT
INTO jobs(id, job_name, vitality, strength, agility, intelligence, luck)
VALUES
(1,"Warrior",8,8,4,4,3),
(2,"Thieves",3,3,8,5,7),
(3,"hunter",5,5,7,5,4),
(4,"Wizard",3,2,6,8,6),
(5,"Monk",5,5,3,7,5),
(6,"Brave",10,10,10,10,10);
--Success if there are players, jobs in the place of structure
Terminal
#Connect to AWS first
$ ssh -i ~/.ssh/FirstKey.pem ec2-user@(Public IP)
#Check the module
$$ pip freeze
#PyMySQL installation
$$ pip install PyMySQL
--Don't disconnect AWS yet
--First, create a py file
Terminal
#py file creation
$$ vi /var/www/html/db_test.py
db_test.py
#Press i to write mode, esc →:Save with wq
from flask import Flask, render_template
import pymysql
app = Flask(__name__)
def getConnection():
return pymysql.connect(
host='localhost',
db='test',
user='root',
password='Same password as phpMySQL',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor
)
@app.route('/')
def select_sql():
connection = getConnection()
message = "Hello world"
sql = "SELECT * FROM players"
cursor = connection.cursor()
cursor.execute(sql)
players = cursor.fetchall()
cursor.close()
connection.close()
return render_template('view.html', message = message, players = players)
--This py statement is the same as the one I did in the previous Flask to db. --Next, create a template file
Terminal
#First, create templates to put the template file
$$ mkdir /var/www/html/templates
##Create a template file in this
$$ vi /var/www/html/templates/view.html
view.html
<!--Writing is the same as before-->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
<h1>Hello SQL</h1>
<p>{{ message }}</p>
{% for player in players %}
<p>{{ player }}</p>
{% endfor %}
</body>
</html>
Terminal
#Move py files
$$ python3 /var/www/html/db_test.py
#When finished ctrl+c
--Connect to the public IP address set in http: // AWS: 5000 and succeed if it is displayed nicely.
――If you go this far, just write a script!
Recommended Posts