Python + Flask for CRUD operations on MySQL database

Python+Flask performs CRUD operations on MySQL database and displays data on the page#

1. Connecting Python to MySQL Database#

1. Install pymysql driver#

PyMySQL is a library used to connect to MySQL server in Python 3.x versions, while mysqldb is used in Python 2.

pip3 install pymysql

2. Test data for database table#

You can use Navicat or the command line to create a table
Insert image description here

3. Connect to the database#

import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password="123456",database="test")
# Use cursor() method to get the operation cursor 
cursor = db.cursor()
# SQL statement
sql = "select * from info"
try:
    # Execute SQL statement
    cursor.execute(sql)
    # One operation in the query, get all records
    result = cursor.fetchall()
    # Print table data
    for row in result:
        id = row[0]
        name = row[1]
        age = row[2]
        print(id,name,age)
except:
    print("Error!")
# Close database
db.close()

Insert image description here

2. Flask+Python operations on database data#

Flask related knowledge points: Flask framework study notes

1. Query data#

This part of the operation is divided into two py files, one is specifically for database operations, and the other is specifically for Flask operations.

sql_lianjie.py defines a class to operate on the database

import pymysql
class Mysql(object):
    def __init__(self):
        try:
            self.db = pymysql.connect(host="localhost",user="root",password="123456",database="test")
            # Cursor object
            self.cursor = self.db.cursor()
            print("Connection successful!")
        except:
            print("Connection failed!")
    # Query data function
    def getdata(self):
        sql = "select * from info"
        # Execute SQL statement
        self.cursor.execute(sql)
        # Get all records
        results = self.cursor.fetchall()
        return results
    # Close
    def __del__(self):
        self.db.close()

sql_flask.py uses Flask routing to display data
Don't forget to import the class for database operations

from flask import Flask,render_template,request
import pymysql
# Import database operation class
from sql_lianjie import Mysql
app = Flask(__name__)

@app.route("/info",methods=['GET','POST'])
def info():
    # Call
    db = Mysql()
    results = db.getdata()
    return render_template("sql_select.html",results=results)
    
if __name__ == "__main__":
    app.run(app.run(debug=True,port=5000,host='127.0.0.1'))

sql_select.html This page displays data on the web (only shows part of the code)

<body>
<div>
    <h4>Query Data</h4>
    <table border="1" width="30%" weight="30%">
        <thead>
        <tr>
            <th>id</th>
            <th>name</th>
            <th>age</th>
        </tr>
        </thead>
        <tbody>
            {% for result in results %}
                <tr>
                    <td>{{ result[0]}}</td>
                    <td>{{ result[1]}}</td>
                    <td>{{ result[2]}}</td>
                    <td><a href="/delete?id={{ result[0] }}"><button>Delete</button></a></td>
                    <td><a href="/submit_insert"><button>Insert</button></a></td>
                    <td><a href="/submit_update"><button>Update</button></a></td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</div>

Result: Open the webpage: http://localhost:5000/info

2. Insert data#

sql_lianjie.py

def insertdata(self,results):
        sql = "insert into info(name,age)values('%s','%s')" % (results['name'],results['age'])
        sql1 = "ALTER TABLE info DROP id"
        sql2 = "ALTER TABLE info ADD id int NOT NULL FIRST"
        sql3 = "ALTER TABLE info MODIFY COLUMN id int NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(id)"
        try:
            self.cursor.execute(sql)
            self.cursor.execute(sql1)
            self.cursor.execute(sql2)
            self.cursor.execute(sql3)
            self.db.commit()
        except:
            # If an error occurs, roll back, it is recommended to use this way so that errors will not affect the table data
            self.db.rollback()
        return

sql_flask.py

@app.route("/submit_insert")
def submit_insert():
    return render_template("sql_insert.html")
    
@app.route("/insert",methods=['GET','POST'])
def insert():
    if request.method == "POST":
        results = request.form
        db = Mysql()
        db.insertdata(results)
        return render_template("result_insert.html",results=results)

sql_insert.html

<div>
    <h4>You can insert data on this page</h4>
    <!-- Change the method attribute in the form tag to change the data request method -->
    <form action="http://localhost:5000/insert" method="POST">
        name:<input type="text" name="name" autocomplete="off"><br/><br/>
        age:<input type="text" name="age" autocomplete="off"><br/><br/>
        <button onclick="myFunction()">Submit</button>       <a href="/info"><button>View Data</button></a>
        <p id="demo"></p>
    </form> 
    <script>
        function myFunction(){
            var txt;
            confirm("Successfully inserted data!");
            txt = "You have successfully inserted data! Click to view data to see the updated data";
            document.getElementById("demo").innerHTML = txt;
        }
    </script>
</div>

result_insert.html

<body>
   You have successfully inserted data! Click to view data to see the updated data!
   <br>
   The inserted name is: {{results['name']}}<br>
   The inserted age is: {{results['age']}}<br>
   <br>
   <a href="/info"><button>View Data</button></a>
</body>

Result: On the info page, clicking the insert button will redirect to the insert data page, where you can enter the data to be inserted (id does not need to be entered as it is set to auto-increment)

Clicking the submit button will show that the data has been successfully inserted

Clicking the view data button will successfully show the inserted data

3. Update data#

sql_lianjie.py

def updatedata(self,results):
        sql = "update info set name='%s',age='%s' where id='%s'" % (results['name'],results['age'],results['id'])
        sql1 = "ALTER TABLE info DROP id"
        sql2 = "ALTER TABLE info ADD id int NOT NULL FIRST"
        sql3 = "ALTER TABLE info MODIFY COLUMN id int NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(id)"
        try:
            self.cursor.execute(sql)
            self.cursor.execute(sql1)
            self.cursor.execute(sql2)
            self.cursor.execute(sql3)
            self.db.commit()
        except:
            # If an error occurs, roll back, it is recommended to use this way so that errors will not affect the table data
            self.db.rollback()
        return

sql_flask.py

@app.route("/submit_update")
def submit_update():
    return render_template("sql_update.html")
    
@app.route("/update",methods=['GET','POST'])
def update():
    if request.method == "POST":
        results = request.form
        db = Mysql()
        db.updatedata(results)
        return render_template("result_update.html",results=results)

sql_update.html

<div>
    <h4>Modify your data on this page</h4>
    <!-- Change the method attribute in the form tag to change the data request method -->
    <form action="http://localhost:5000/update" method="POST">
        Please enter the id you need to modify:<input type="text" name="id" autocomplete="off"><br/>
        Please enter the modified name:<input type="text" name="name" autocomplete="off"><br/>
        Please enter the modified age:<input type="text" name="age" autocomplete="off"><br/>
        <input type="submit" value="Submit">
    </form> 
    <a href="/info"><button>View Data</button></a>
</div>

result_update.html

<body>
   You have successfully modified the data!
   <br>
   Click to view data to see the updated data!
   <br>
   The modified id is: {{results['id']}}<br>
   The modified name is: {{results['name']}}<br>
   The modified age is: {{results['age']}}<br>
   <br>
   <a href="/info"><button>View Data</button></a>
</body>

Result reference insert data effect

4. Delete data#

sql_lianjie.py

def deletedata(self,id):
        sql = "delete from info where id=" + str(id)
        sql1 = "ALTER TABLE info DROP id"
        sql2 = "ALTER TABLE info ADD id int NOT NULL FIRST"
        sql3 = "ALTER TABLE info MODIFY COLUMN id int NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(id)"
        try:
            # Execute SQL statement
            self.cursor.execute(sql)
            self.cursor.execute(sql1)
            self.cursor.execute(sql2)
            self.cursor.execute(sql3)
            # Commit data
            self.db.commit()
        except:
            # If an error occurs, roll back, it is recommended to use this way so that errors will not affect the table data
            self.db.rollback()
        return

sql_flask.py

@app.route("/delete")
def delete():
    id = request.args.get("id")
    db = Mysql()
    db.deletedata(id)
    return render_template("result_delete.html",id=id)

result_delete.html

<body>
   You have successfully deleted the data with id:{{id}}!
   <br>
   Click to view data to see the updated data!
   <br>
   <br>
   <a href="/info"><button>View Data</button></a>
</body>

In the update, modify, and delete operations, the purpose of sql1-3 is to automatically update the sorting of the id when the data in the table changes. If these three lines are not added, when the middle data is deleted, the ids of those middle data cannot be automatically reordered.
Code:
Delete the original primary key: ALTER TABLE news DROP NewsID;
Add a new primary key: ALTER TABLE news ADD NewsID int NOT NULL FIRST;
Set the new primary key: ALTER TABLE news MODIFY COLUMN NewsID int NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(NewsID);