[The most complete network! ! It is not easy to sort out the full text of 10,000 words. If it is helpful, please like + bookmark, thank you very much! ! 】
The goal of this blog is to enter data on the web page and then store the data in the database. But just “storing” is not enough to meet the demand, so we also need to be able to take out the data for comparison and analysis of the data.
We decompose the task into 4 steps: the first step uses python to connect to the database, the second step uses python to obtain webpage data, and the combination of steps 1 and 2 realizes reading data from the webpage and storing it in the database. The third step is to use python to reversely read the data from the database, and the fourth step is to display the data on the webpage using front-end technology.
This article will teach in detail from these 4 steps:
Read the webpage data and store it in the database, the effect is as follows:
The read database data is displayed on the web page, the effect is as follows:
Module 1. Realize reading webpage data and storing it in the database:
1. Read python data into the database:
1.1 Specific operation:
The function of the following code is: use python to connect to the database, and then write 3 pieces of student information into the database.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:111111@localhost:3306/flask_sql'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
db = SQLAlchemy(app) #create database object
#Create a student class, note that the following are definitions, the student table must be created in the database first
class Students(db. Model):
__tablename__ = 'students'
id = db.Column(db.Integer, primary_key=True)
name = db. Column(db. String(16))
stu_number = db.Column(db.String(32), unique=True)
def __repr__(self):
return '<User: %s %s %s>' % (self.name, self.id, self.stu_number)
def create_table():
with app.app_context(): #This paragraph must be added, otherwise an error will be reported
db. drop_all()
db. create_all()
# insert student data
stu1 = Students(name='Xiao Ming', stu_number='1918101')
stu2 = Students(name='Xiaohong', stu_number='1918102')
stu3 = Students(name='Xiaohua', stu_number='1918103')
db.session.add_all([stu1, stu2, stu3])
db.session.commit()
create_table()
@app.route('/')
def index():
return 'Hello flask!'
if __name__ == '__main__':
app.run(debug=True)
‘mysql + pymysql://username:password@hostname/ip address:port number/database name’
In the above case: user name: root, password: 111111 (set when building the database), host name: localhost, port number: 3306, database name: flask_sql
Here is the effect:
1.2 Possible problems:
If you are using a friend of pycharm, the following problems may occur during the process of importing the package:
The solution is as follows:
Search for flask-sqlalchemy and click Install package, after the download is complete!
If the following error occurs:
Download pymys in the same way:
Recommend 2 high-quality blogs:
Flask study notes (4): Flask and database connection
Web server (Flask framework) connects to MySQL database and writes data detailed tutorial
2. Read webpage data into Python:
The main function of the code: After compiling and running, clicking the connection will pop up the html content in the E:/demo/login/login.html directory, and then click the OK button to output the user name and password entered by the user under the python compiler.
Here is the code for python flask:
from flask import Flask, request
app = Flask(__name__)
@app.route('/')
def index():
return open('E:/demo/login/login.html').read() #Here you need to modify the address of the open file and replace it with the address of your own html file
@app.route('/login', methods=['POST'])
def login():
username = request.form.get('aaa') #aaa is the name in the username tag
password = request.form.get('bbb') #bbb is the name in the password tag
print('Username:', username)
print('password:', password)
return 'login successful'
if __name__ == '__main__':
app.run(debug=True)
The code of login.html below:
<!DOCTYPE html>
<html>
<head>
<title>Login Page</title>
</head>
<body>
<h1>Login page</h1>
<form method="POST" action="/login">
<label for="username">Account:</label>
<input type="text" id="username" name="aaa"><br><br>
<label for="password">Password:</label>
<input type="password" id="password" name="bbb"><br><br>
<input type="submit" value="login">
</form>
</body>
</html>
The following is the effect: enter the account number 201314 on the web page, enter the password 520520, click Login, and then you will see the output user name and password.
3. Store webpage data in the database:
Inspired by the above two examples, the basic bridge has been built. Next, we only need to make a little improvement to store the data read from the web page into the database.
First create a new table Login in the mysql database
from flask import Flask, request, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:111111@localhost:3306/flask_sql'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
db = SQLAlchemy(app)
class Login(db.Model):
__tablename__ = 'login'
username = db.Column(db.String(20), primary_key=True)
password = db.Column(db.Integer)
def __repr__(self):
return '<User: %s %s>' % (self. username, self. password)
@app.route('/')
def index():
return open('E:/demo/login/login.html').read()
@app.route('/login', methods=['POST'])
def login():
user = request. form. get('aaa')
word = request. form. get('bbb')
create_table(user, word)
return 'login successful'
def create_table(user, word):
with app.app_context():
db. drop_all()
db. create_all()
user1 = Login(username=user, password=word)
db.session.add_all([user1])
db.session.commit()
if __name__ == '__main__':
app.run(debug=True)
Enter the account number Pan Binjie, password 520, and then the login is successful, enter the database to check and find that all the data written on the web page has been stored in the database.
Module 2, filter database data output to web page:
1. Implement database data reading into Python:
Program purpose: read the data of the database to the output panel of Pycharm for output.
The following figure shows the contents of the login table under the flask_sql database:
The following is the code, you can try it yourself:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:111111@localhost:3306/flask_sql' #Database setting format see above
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Login(db.Model):
__tablename__ = 'login' #login table
username = db.Column(db.String(20), primary_key=True) #Set two variables
password = db.Column(db.String(20))
@app.route('/')
def index():
logins = Login.query.all() #logins get all data
for login in logins:
print(login.username, login.password) # output one by one
return '!!The data is all printed!!'
if __name__ == '__main__':
app.run(debug=True)
The picture below is the output effect, it is amazing!
The above is to output all the contents of a table in the database. If we only want to select some fields for output, how can we do it?
For example, we want to extract the username whose password is 45632:
We only need to modify the following code and add the filter_by statement:
logins = Login.query.filter_by(password=45632).all() #Get the data of password=45632
2. Output the database data to the webpage:
Now that the data in the database can be output to the output column of the compiler, it only needs to call a little skill to output it to the web page.
The data in the database is still as follows:
Here is the python code:
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:111111@localhost:3306/flask_sql' #database name flask_sql, see above for other information
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Login(db.Model):
__tablename__ = 'login' #login table
username = db.Column(db.String(20), primary_key=True) #define two variables
password = db.Column(db.String(20))
@app.route('/')
def index():
logins = Login.query.all() #Perhaps all the data of the Login class is stored in the Logins variable
return render_template('login.html', logins=logins) #Assign all the data of the logins variable to the template variable, and use the login.html interface to display
if __name__ == '__main__':
app.run(debug=True)
Below is the html code:
<!DOCTYPE html>
<html>
<head>
<title>User Login Information</title>
<style>
table {
border-collapse: collapse;
}
th, td {
border: 1px solid black;
padding: 5px;
}
</style>
</head>
<body>
<h1>User login information</h1>
<table>
<tr>
<th>Username</th>
<th>Password</th>
</tr>
{% for login in logins %}
<tr>
<td>{<!-- -->{ login. username }}</td>
<td>{<!-- -->{ login.password }}</td>
</tr>
{% end for %}
</table>
</body>
</html>
Add html knowledge:
1.
represent the same row of data.
2. {% for login in logins %} content {% endfor %}: It is a loop syntax provided by the jinja2 template engine, which means that the content is iteratively looped multiple times.
3.
is the variable interpolation syntax in the template, and the engine will replace the middle content with a specific value.
The following figure is the output effect, which directly outputs the data in the database to the web page:
If you want to selectively output data, you only need to change the original statement to:
logins = Login.query.filter_by(password=45632).all() #Get the data of password=45632
That’s it! Isn’t it very simple, like + bookmark! !
Module 3: Implement database modification and query operations controlled by the web page
The above operations can realize the transmission and display of data between the web page and the database, but there are still two unsolved problems: one is how to modify the database on the web page, and the other is how to query the information in the database on the web page?
1. Query the database content through the webpage:
Imagine a scenario. In the login interface, a user enters the account number and password. If the account number does not match the corresponding password, the login is refused. If the account number is not in the database, the login is also refused. We will implement this function below:
Idea: We first read the data into the variables of the compiler on the web page, and then substitute the variables into the database for searching. If found, return found, otherwise return not found.
Below is the database:
The following is the result of the operation: if the entered account is in the database, the statement of successful login is returned, otherwise, the statement of failed login is returned.
main.py file
from flask import Flask, request, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:111111@localhost:3306/flask_sql' #Database setting format see above
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Login(db.Model):
username = db.Column(db.String(50),primary_key=True)
password = db.Column(db.String(50))
@app.route('/')
def index():
return open('E:/demo/login/login.html').read() #Here you need to modify the address of the open file and replace it with the address of your own html file
@app.route('/login', methods=['POST'])
def login():
username = request.form.get('aaa') #aaa is the name in the username tag
password = request.form.get('bbb') #bbb is the name in the password tag
login_exists = Login.query.filter_by(username=username).first() is not None #Changed statement 1
return render_template('test.html', login_exists=login_exists) #Changed statement 2
if __name__ == '__main__':
app.run(debug=True)
test.html
<!DOCTYPE html>
<html>
<body>
<table>
{% if login_exists %}
<tr>Congratulations on successfully logging in. </tr>
{% else %}
<tr>I apologize for the login failure.</tr>
{% endif %}
</table>
</body>
</html>
login.html
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<h1>Login</h1>
<form method="POST" action="/login">
<label for="username">username:</label>
<input type="text" id="username" name="aaa"><br><br>
<label for="password">password:</label>
<input type="password" id="password" name="bbb"><br><br>
<input type="submit" value="submit">
</form>
</body>
</html>
2. Modify the database content through the webpage:
Additional instructions:
If you can’t find the template file, you can create a template yourself in the root directory of the project:
Put the html file in the template:
At this time, if you run the program again, you will find that the parameters of render_template have passed successfully:
The following is the code for storing the webpage data of the book information interface into the database:
importbooks.py
from flask import Flask, request, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql + pymysql://root:lxmysql123@localhost:3306brary_management_system'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
db = SQLAlchemy(app)
class Books(db.Model):
__tablename__ = 'books'
bar_code = db.Column(db.String(100))
book_name = db.Column(db.String(100),primary_key=True)
book_author = db.Column(db.String(100))
book_publisher = db.Column(db.String(100))
ISBN = db.Column(db.Integer)
book_price = db.Column(db.Integer)
book_introduction = db. Column(db. String(500))
def __repr__(self):
return '<User: %s %s %s %s %s %s %s>' % (self.bar_code, self.book_name,self.book_author,self.book_publisher,self.ISBN,self.book_price,self.book_introduction )
@app.route('/')
def index():
return open('importbooks.html').read()
@app.route('/importbooks', methods=['POST'])
def books():
code = request. form. get('aaa')
name = request. form. get('bbb')
author = request. form. get('ccc')
publisher = request. form. get('ddd')
ISBN = request. form. get('eee')
price = request. form. get('fff')
introduction = request. form. get('zzz')
create_table(code, name, author, publisher, ISBN, price, introduction)
return 'login successful'
def create_table(code, name, author, publisher, ISBN, price, introduction):
with app.app_context():
db. create_all()
user1 = Books(bar_code=code, book_name=name,book_author=author,book_publisher=publisher,ISBN=ISBN,book_price=price,book_introduction=introduction)
db.session.add_all([user1])
db.session.commit()
if __name__ == '__main__':
app.run(debug=True)
html
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
@import "personinfo.css";
</style>
</head>
<body>
<div class="mm">
<button onclick='window.open("../personinfo/personinfo.html")' type="button" id="add">Back to Homepage</button>
<button onclick='window.open("../login/login.html")' type="button" id="add">Login</button>
</div>
<div class="int">
<p>Book information entry</p>
</div>
<form method="post" action="/importbooks">
<div class="int">
<label for="numbers">Book Barcode:</label>
<input type="text" id="numbers" class="required" name="aaa">
</div>
<div class="int">
<label for="book">Book Title:</label>
<input type="text" id="book" class="required" name="bbb">
</div>
<div class="int">
<label for="name">Author:</label>
<input type="text" id="name" class="required" name="ccc">
</div>
<div class="int">
<label for="w">Publisher:</label>
<input type="text" id="w" class="required" name="ddd">
</div>
<div class="int">
<label for="ISBN">ISBN number:</label>
<input type="text" id="ISBN" class="required" name="eee">
</div>
<div class="int">
<label for="price">Pricing:</label>
<input type="text" id="price" class="required" name="fff">
</div>
<div class="int">
<label for="introduce">Introduction:</label>
<input type="text" id="introduce" class="required" name="zzz">
</div>
<div class="oo">
<input type="submit" value="">
</div>
</form>
</body>
</html>