Flask 简明教程

Flask – SQLAlchemy

在 Flask Web 应用中使用原始 SQL 在数据库上执行 CRUD 操作可能会很繁琐。相反， SQLAlchemy ，一个 Python 工具包是一个强大的 OR Mapper ，它为应用程序开发人员提供了 SQL 的全部功能和灵活性。Flask-SQLAlchemy 是 Flask 扩展，它为 Flask 应用程序添加了对 SQLAlchemy 的支持。

Using raw SQL in Flask web applications to perform CRUD operations on database can be tedious. Instead, SQLAlchemy, a Python toolkit is a powerful OR Mapper that gives application developers the full power and flexibility of SQL. Flask-SQLAlchemy is the Flask extension that adds support for SQLAlchemy to your Flask application.

使用原始 SQL 在数据库上执行 CRUD 操作可能会很繁琐。相反， SQLAlchemy ，一个 Python 工具包是一个强大的 OR Mapper ，它为应用程序开发人员提供了 SQL 的全部功能和灵活性。Flask-SQLAlchemy 是 Flask 扩展，它为 Flask 应用程序添加了对 SQLAlchemy 的支持。

What is ORM (Object Relation Mapping)?

大多数编程语言平台都是面向对象的。另一方面，RDBMS 服务器中的数据以表格的形式存储。对象关系映射是一种将对象参数映射到底层 RDBMS 表结构的技术。ORM API 提供了执行 CRUD 操作的方法，而无需编写原始 SQL 语句。

Most programming language platforms are object oriented. Data in RDBMS servers on the other hand is stored as tables. Object relation mapping is a technique of mapping object parameters to the underlying RDBMS table structure. An ORM API provides methods to perform CRUD operations without having to write raw SQL statements.

在本章中，我们将学习 Flask-SQLAlchemy 的 ORM 技术并构建一个小 Web 应用程序。

In this section, we are going to study the ORM techniques of Flask-SQLAlchemy and build a small web application.

Step 1 − 安装 Flask-SQLAlchemy 扩展。

Step 1 − Install Flask-SQLAlchemy extension.

pip install flask-sqlalchemy

Step 2 − 您需要从该模块导入 SQLAlchemy 类。

Step 2 − You need to import SQLAlchemy class from this module.

from flask_sqlalchemy import SQLAlchemy

Step 3 − 现在创建一个 Flask 应用程序对象，并将 URI 设置为要使用的数据库。

Step 3 − Now create a Flask application object and set URI for the database to be used.

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'

Step 4 − 然后创建一个 SQLAlchemy 类的对象，并将应用程序对象作为参数。该对象包含 ORM 操作的辅助函数。它还提供了一个父级 Model 类，用户定义的模型就是使用此类声明的。在下面的代码段中，创建了一个 students 模型。

Step 4 − Then create an object of SQLAlchemy class with application object as the parameter. This object contains helper functions for ORM operations. It also provides a parent Model class using which user defined models are declared. In the snippet below, a students model is created.

db = SQLAlchemy(app)
class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))
   addr = db.Column(db.String(200))
   pin = db.Column(db.String(10))

def __init__(self, name, city, addr,pin):
   self.name = name
   self.city = city
   self.addr = addr
   self.pin = pin

Step 5 − 要创建/使用 URI 中提到的数据库，请运行 create_all() 方法。

Step 5 − To create / use database mentioned in URI, run the create_all() method.

db.create_all()

Session 对象 SQLAlchemy 管理 ORM 对象的所有持久性操作。

The Session object of SQLAlchemy manages all persistence operations of ORM object.

以下会话方法执行 CRUD 操作 −

The following session methods perform CRUD operations −

  1. db.session.add(model object) − inserts a record into mapped table

  2. db.session.delete(model object) − deletes record from table

  3. model.query.all() − retrieves all records from table (corresponding to SELECT query).

您可以使用过滤器属性将过滤器应用于检索的记录集。例如，为了检索学生表中具有 city = ’Hyderabad’ 的记录，请使用以下语句 −

You can apply a filter to the retrieved record set by using the filter attribute. For instance, in order to retrieve records with city = ’Hyderabad’ in students table, use following statement −

Students.query.filter_by(city = ’Hyderabad’).all()

有了这么多的背景，现在我们应该为我们的应用程序提供视图函数，以便添加学生数据。

With this much of background, now we shall provide view functions for our application to add a student data.

应用程序的入口点是 show_all() 函数，绑定到 ‘/’ URL。学生表的记录集作为参数发送到 HTML 模板。模板中的服务器端代码在 HTML 表格形式中呈现记录。

The entry point of the application is show_all() function bound to ‘/’ URL. The Record set of students table is sent as parameter to the HTML template. The Server side code in the template renders the records in HTML table form.

@app.route('/')
def show_all():
   return render_template('show_all.html', students = students.query.all() )

模板 (‘show_all.html’) 的 HTML 脚本如下 −

The HTML script of the template (‘show_all.html’) is like this −

<!DOCTYPE html>
<html lang = "en">
   <head></head>
   <body>
      <h3>
         <a href = "{{ url_for('show_all') }}">Comments - Flask
            SQLAlchemy example</a>
      </h3>

      <hr/>
      {%- for message in get_flashed_messages() %}
         {{ message }}
      {%- endfor %}

      <h3>Students (<a href = "{{ url_for('new') }}">Add Student
         </a>)</h3>

      <table>
         <thead>
            <tr>
               <th>Name</th>
               <th>City</th>
               <th>Address</th>
               <th>Pin</th>
            </tr>
         </thead>

         <tbody>
            {% for student in students %}
               <tr>
                  <td>{{ student.name }}</td>
                  <td>{{ student.city }}</td>
                  <td>{{ student.addr }}</td>
                  <td>{{ student.pin }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
   </body>
</html>

上面页面包含一个超链接，指向 ‘/new’ URL 映射 new() 函数。单击后，它将打开“学生信息”表单。数据在 POST 方法中发布到同一 URL。

The above page contains a hyperlink to ‘/new’ URL mapping new() function. When clicked, it opens a Student Information form. The data is posted to the same URL in POST method.

new.html

<!DOCTYPE html>
<html>
   <body>
      <h3>Students - Flask SQLAlchemy example</h3>
      <hr/>

      {%- for category, message in get_flashed_messages(with_categories = true) %}
         <div class = "alert alert-danger">
            {{ message }}
         </div>
      {%- endfor %}

      <form action = "{{ request.path }}" method = "post">
         <label for = "name">Name</label><br>
         <input type = "text" name = "name" placeholder = "Name" /><br>
         <label for = "email">City</label><br>
         <input type = "text" name = "city" placeholder = "city" /><br>
         <label for = "addr">addr</label><br>
         <textarea name = "addr" placeholder = "addr"></textarea><br>
         <label for = "PIN">City</label><br>
         <input type = "text" name = "pin" placeholder = "pin" /><br>
         <input type = "submit" value = "Submit" />
      </form>
   </body>
</html>

当 HTTP 方法被检测为 POST 时，表单数据被添加到学生表中，并且应用程序返回到主页，显示已添加的数据。

When the http method is detected as POST, the form data is added in the students table and the application returns to homepage showing the added data.

@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['city'] or not request.form['addr']:
         flash('Please enter all the fields', 'error')
      else:
         student = students(request.form['name'], request.form['city'],
            request.form['addr'], request.form['pin'])

         db.session.add(student)
         db.session.commit()

         flash('Record was successfully added')
         return redirect(url_for('show_all'))
   return render_template('new.html')

下面是应用程序的完整代码 (app.py) 。

Given below is the complete code of application (app.py).

from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
app.config['SECRET_KEY'] = "random string"

db = SQLAlchemy(app)

class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))
   addr = db.Column(db.String(200))
   pin = db.Column(db.String(10))

def __init__(self, name, city, addr,pin):
   self.name = name
   self.city = city
   self.addr = addr
   self.pin = pin

@app.route('/')
def show_all():
   return render_template('show_all.html', students = students.query.all() )

@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['city'] or not request.form['addr']:
         flash('Please enter all the fields', 'error')
      else:
         student = students(request.form['name'], request.form['city'],
            request.form['addr'], request.form['pin'])

         db.session.add(student)
         db.session.commit()
         flash('Record was successfully added')
         return redirect(url_for('show_all'))
   return render_template('new.html')

if __name__ == '__main__':
   db.create_all()
   app.run(debug = True)

从 Python shell 运行脚本，并在浏览器中输入 http://localhost:5000/ 。

Run the script from Python shell and enter http://localhost:5000/ in the browser.

flask sqlalchemy example

单击 ‘Add Student’ 链接以打开 Student information 表单。

Click the ‘Add Student’ link to open Student information form.

add student

填写并提交表单。主页将重新显示已提交的数据。

Fill the form and submit. The home page reappears with the submitted data.

我们可以看到如下所示的输出。

We can see the output as shown below.

flask sqlalchemy example output