(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 ↓
db.png

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 スクリーンショット 2020-06-22 6.45.08.png

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

Recommended Posts

(For myself) Flask_6 (Open db from python, Mysql basic (phpMyAdmin))
(For myself) Flask_7 (Open database from Flask)
(For myself) Flask_AWS_1 (Install PHP, MySQL, phpMyAdmin, Python in AWS virtual environment)
Touch MySQL from Python 3
Use MySQL from Python
Python memo (for myself): Array
Use MySQL from Anaconda (python)
Python Tkinter notes (for myself)
Access Oracle DB from Python
(For myself) Flask_8 (Add / Edit / Delete in database with python)
Basic Python grammar for beginners
Problems and solutions when asked for MySQL db in Python 3
[Python] Organize the basic structure of Flask apps (Aim for de-copying)
Next, use Python (Flask) for Heroku!
Tips for calling Python from C
Load Mac Python import MySQL db
Receive textual data from mysql with python
(For myself) Flask_5 (Add to txt file)
Connect to Docker's MySQL container from Flask
INSERT into MySQL with Python [For beginners]
Get exchange rates from open exchange rates in Python
"Python AI programming" starting from 0 for windows
Note for Pyjulia calling Julia from Python
Connecting from python to MySQL on CentOS 6.4
Debug for mysql connection with python mysql.connector
AtCoder cheat sheet in python (for myself)
Python> Output numbers from 1 to 100, 501 to 600> For csv
(For myself) Django_1 (Basic / Hello World / Template)
(For myself) Flask_3 (form, POST and GET)
[Competitive programming] [Python3] Required knowledge, for myself