(For myself) Flask_AWS_3 (Check if SQL is working)

item

  1. Create a table with db
  2. PyMySQL installation
  3. Operation check

1. Make a table with db

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

2. PyMySQL installation

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

3. Operation check

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

4. At the end

――If you go this far, just write a script!

Recommended Posts

(For myself) Flask_AWS_3 (Check if SQL is working)
Python> Run-time argument> Check if -d is attached
python> Check if code is printable> Use ord () / all (c in string.printable for c in hello)
Check if the string is a number in python
Check if it is Unix in the scripting language
Check if the LAN cable is disconnected on Linux
Check if it is Unix in the scripting language