(For myself) Flask_6 (Open db from python, Mysql basic (phpMyAdmin))

――This time, I didn't touch Flask at all while cheating with Flask.

item

1. Open db from python
2. Create db with phpMyAdmin
3. Try to retrieve the data from db

1. Open db from python

--Suppose you have a table called players in a database called mydb in phpMyAdmin ↓

python

import pymysql

print("Running")

connection = pymysql.connect(
    host="localhost",
    db="mydb",
    user="root",
    password="",
    charset="utf8",
    cursorclass=pymysql.cursors.DictCursor
)

sql = "SELECT * FROM players"
cursor = connection.cursor()
cursor.execute(sql)
players = cursor.fetchall()

cursor.close()
connection.close()

for player in players:
    print(player["name"])

(Slightly long and difficult to read)

--This prints the name column in players. --ʻImport Make MySQL available in python with pymysql... ---connection = pymysql.connect ()puts the connection information to SQL inconnection. <br> <br> --The environment to run MySQL is decided by host =" localhost "(If it is your own PC, you can use local server, AWS server) <br> I feel that it will come out if you look up the host name etc. --Select the db in that MySQL withdb =" mydb " --ʻUser = "root"will connect the root user to MySQL --password = "", charset = "utf8", No password, read the character code with utf-8 --Check out the Dictionary format, which can be read in Dictionary format with cursorclass = pymysql.cursors.DictCursor!

--In sql =" SELECT * FROM players ", put the SQL you want to execute in the variable sql. --With cursor = connection.cursor (), put the connection information put in connection into cursor by .cursor () ... --In .execute (sql) ofcursor.execute (sql), execute the command entered in sql in the db connected by the connection information entered in cursor. --cursor.close (), connection.close () This area is closing something that was moving --After that, the read information is put in player, and the data with the key (in the dictionary format) with name in it is printed.

Terminal

$ cd <Directory name where the working file is located>
$ python <Working directory name>

If the execution result is normal in the terminal etc., it is completed

2. Create db with phpMyAdmin

--I will omit the installation and may write it someday

――It's OK if you do this normally --It seems that ʻutf8 general ci` is a good character code, but ...

3. Try to retrieve data from db

sql

--Extract all data
SELECT * FROM players;

--Get only some columns
SELECT name, level FROM players;

--Get only some rows
SELECT * FROM players WHERE level >= 7;

--Combine multiple conditions
SELECT * FROM players WHERE level >= 7 AND job_id <> 6;

--Combine condition specification and column selection
SELECT name, level FROM players WHERE level >= 7;

-** About fetching all data ** --Specify all columns with SELECT * (* makes it all) --In FROM players;, you say "where?" "From players" -** About getting only some columns ** --You can select the column to retrieve by entering the column name after SELECT (here name and level). -** About getting only some rows ** --You can retrieve any line by putting WHERE and conditional expression afterFROM ~~ --WHERE can apply multiple conditional expressions using ʻAND and ʻOR

List of usable conditional expressions

a = b
a < b
a > b
a <= b
a >= b
a <> b --That a and b are different

4. At the end

――It's about time to make a WEB application by renting AWS or preparing the environment! !! !! !!