Python Pyramid 简明教程

Python Pyramid - Using SQLAlchemy

在本章中，我们将学习如何使用关系数据库作为 Pyramid Web 应用程序的后端。Python 可以通过相应的与 DB-API 兼容的连接器模块或驱动程序与几乎所有关系数据库进行交互。但是，我们将使用 @ {s9} 库作为 Python 代码和数据库之间的接口（我们将使用 SQLite 数据库，因为 Python 已经为其提供了内置支持）。SQLAlchemy 是一个流行的 SQL 工具包和对象关系映射器。

In this chapter, we shall learn how to use a relational database as a back-end with the Pyramid web application. Python can interact with almost every relational database using corresponding DB-API compatible connector modules or drivers. However, we shall use SQLAlchemy library as an interface between Python code and a database (we are going to use SQLite database as Python has in-built support for it). SQLAlchemy is a popular SQL toolkit and Object Relational Mapper.

对象关系映射是一种编程技术，用于在面向对象编程语言的不同类型系统之间转换数据。通常，像 Python 这种面向对象语言中使用的类型系统包含非标量类型。但是，大多数数据库产品的（例如 Oracle、MySQL 等）数据类型都是基本类型，例如整数和字符串。

Object Relational Mapping is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an Object Oriented language like Python contains non-scalar types. However, data types in most of the database products such as Oracle, MySQL, etc., are of primitive types such as integers and strings.

在 ORM 系统中，每个类都映射到底层数据库中的一个表。ORM 负责处理这些问题，让你专注于对系统逻辑进行编程，而不再需要自己编写乏味的数据库接口代码。

In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system.

为了使用 SQLALchemy，我们需要先使用 PIP 安装程序来安装该库。

In order to use SQLALchemy, we need to first install the library using PIP installer.

pip install sqlalchemy

SQLAlchemy 被设计为使用专为某个特定数据库而构建的 DBAPI 实现来运行。它使用方言系统与各种类型的 DBAPI 实现和数据库通信。所有方言都要求已安装相应的 DBAPI 驱动程序。

SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed.

以下是包含的方言：

The following are the dialects included −

Firebird
Microsoft SQL Server
MySQL
Oracle
PostgreSQL
SQLite
Sybase

Database Engine

由于我们将使用 SQLite 数据库，因此我们需要为名为 @ {s10} 的数据库创建一个数据库引擎。从 @ {s12} 模块导入 @ {s11} 函数。

Since we are going to use SQLite database, we need to create a database engine for our database called test.db. Import create_engine() function from the sqlalchemy module.

from sqlalchemy import create_engine
from sqlalchemy.dialects.sqlite import *
SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args = {"check_same_thread": False})

为了与数据库进行交互，我们需要获取其句柄。会话对象是数据库的句柄。使用 @ {s13} 定义会话类 - 一个可配置的会话工厂方法，绑定到引擎对象。

In order to interact with the database, we need to obtain its handle. A session object is the handle to database. Session class is defined using sessionmaker() - a configurable session factory method which is bound to the engine object.

from sqlalchemy.orm import sessionmaker, Session
session = sessionmaker(autocommit=False, autoflush=False, bind=engine)

接下来，我们需要一个声明式基类，用于在声明式系统中存储类的目录和映射的表。

Next, we need a declarative base class that stores a catalog of classes and mapped tables in the Declarative system.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Model Class

Students ， Base 的子类，映射到数据库中的 students 表。 Students 类中的属性对应于目标表中列的数据类型。请注意，id 属性对应于 book 表中的主键。

Students, a subclass of Base, is mapped to a students table in the database. Attributes in the Students class correspond to the data types of the columns in the target table. Note that the id attribute corresponds to the primary key in the book table.

class Students(Base):
   __tablename__ = 'student'
   id = Column(Integer, primary_key=True, nullable=False)
   name = Column(String(63), unique=True)
   marks = Column(Integer)
Base.metadata.create_all(bind=engine)

create_all() 方法在数据库中创建对应的表。可以使用 SQLite 可视化工具（如 SQLiteStudio. ）进行确认

The create_all() method creates the corresponding tables in the database. It can be confirmed by using a SQLite Visual tool such as SQLiteStudio.

现在，我们为上述数据库中的 student 表定义视图函数，用于执行 CRUD（即添加、显示、修改和删除行）操作。

We shall now define view functions for performing CRUD operations (i.e. add, display, modify and delete rows) on the student table in the above database.

Add a New Student Record

首先，我们将创建一个 HTML 表单模板，以便用户输入学生数据并定义渲染该模板的视图。以下是 myform.html 模板

First, we shall create a HTML form template for the user to enter student data and define a view that renders the template. Here is the myform.html template

Example

<html>
<body>
   <form method="POST" action="http://localhost:6543/add">
   <p>Student Id: <input type="text" name="id"/> </p>
   <p>student Name: <input type="text" name="name"/> </p>
   <p>Percentage: <input type="text" name="percent"/> </p>
   <p><input type="submit" value="Submit"> </p>
</body>
</html>

在 Pyramid 应用程序代码中，定义 index() 视图函数来渲染上述表单。

In the Pyramid application code, define the index() view function to render the above form.

from wsgiref.simple_server import make_server
from pyramid.config import Configurator
from pyramid.response import Response
from pyramid.view import view_config

@view_config(route_name='index', renderer='templates/myform.html')
def index(request):
   return {}

在应用程序配置中，将模式与该视图的 "/new" 模式一起注册，如下所示：

In the application configuration, register the route with the "/new" pattern for this view as −

if __name__ == '__main__':
   with Configurator() as config:
      config.include('pyramid_jinja2')
      config.add_jinja2_renderer(".html")
      config.add_route('index', '/new')
      config.scan()
      app = config.make_wsgi_app()
   server = make_server('0.0.0.0', 6543, app)
   server.serve_forever()

由于上述模板中的 HTML 表单已提交给带 POST 动作的 /add URL，我们需要将该 URL 映射到 add 模式并注册 add() 视图，它将表单数据解析为 Students 类的一个对象。此对象已添加到数据库会话中，并且通过调用其 commit() 方法来完成操作。

As the HTML form in the above template is submitted to /add URL with POST action, we need to map this URL to add route and register add() view that parses the form data into an object of Students class. This object is added to the database session and the operation is finalized by calling its commit() method.

@view_config(route_name='add', request_method='POST')
def add(request):
   id=request.POST['id']
   name=request.POST['name']
   percent=int(request.POST['percent'])
   student=Students(id=id, name=name, percent=percent)
   session.add(student)
   session.commit()
   return HTTPFound(location='http://localhost:6543/')

确保在配置中添加了 add 模式，并将其映射到 /add URL 模式。

Make sure that the add route is added in the configuration, mapped to /add URL pattern.

config.add_route('add','/add')

Output

如果我们启动服务器并在浏览器中打开 http://localhost:6543/new ，则将显示录入表单，如下所示：

If we start the server and open http://localhost:6543/new in the browser, the Entry form will be displayed as follows −

填写表单并按 "submit" 按钮。将调用 add() 视图，并在 students 表中添加新记录。重复此过程几次，以添加一些记录。以下是一些示例数据：

Fill the form and press the "submit" button. The add() view will be called and a new record will be added in the students table. Repeat the process a couple of times to add a few records. Here is a sample data −

Show List of All Records

通过查询模型，可以获得 Students 模型的所有对象（对应于 students 表中的行）。

All the objects of the Students model (corresponding to row in students table) are obtained by querying the model.

rows = session.query(Students).all()

将每一行转换为一个 dict 对象，将所有这些对象追加到一个 dict 对象列表中，并作为上下文返回给 list.html 模板，以便以 HTML 模板的形式显示。showall() 视图函数会执行此过程，该视图函数与 list 模式相关联。

Each row is converted into a dict object, all of them are appended to a list of dict objects, and returned as a context to the list.html template to be displayed in the form of HTML template. The process is performed by the showall() view function, associated with list route.

@view_config(route_name='list', renderer='templates/marklist.html')
def showall(request):
   rows = session.query(Students).all()
   students=[]
   for row in rows:
      students.append({"id":row.id, "name":row.name, "percent":row.percent})
   return{'students':students}

Example

marklist.html 模板将 Students 列表呈现为 HTML 表。它的 HTML/jinja2 脚本如下：

The marklist.html template renders the Students list as a HTML table. Its HTML/jinja2 script is as follows −

<html>
<body>
<table border=1>
   <thead>
      <tr>
         <th>Student ID</th>
         <th>Student Name</th>
         <th>percentage</th>
         <th>Edit</th>
         <th>Delete</th>
      </tr>
   </thead>
   <tbody>
      {% for Student in students %}
         <tr>
         <td>{{ Student.id }}</td> <td>{{ Student.name }}</td>
         <td>{{ Student.percent }}</td>
         <td><a href="/show/{{ Student.id }}">edit</a></td>
         <td><a href="/delete/{{ Student.id }}">delete</a></td>
         </tr>
      {% endfor %}
   </tbody>
</table>
<h3><a href="http://localhost:6543/new">Add new</a></h3>
   </body>
</html>

在配置中添加 list 模式并使用 '/' URL 注册它。

Add the list route in the configuration and register it with '/' URL.

config.add_route('list', '/')

Output

启动服务器后，在浏览器中打开 http://localhost:6543/ 。将显示 students 表中现有记录的列表。

Open http://localhost:6543/ in the browser after starting the server. The list of existing records in the students table will be displayed.

请注意，最后两列中的超链接。例如，“id=1”前的“edit”链接指向 http://localhost:6543/show/1 。这些链接用于执行更新和删除操作。

Notice the hyperlinks in the last two columns. For example, the "edit" link before "id=1" points to http://localhost:6543/show/1. These links are intended to execute update and delete operations.

Update Existing Record

在 /show/1 URL 中，有一个尾部路径参数。它映射到配置中的 'show' 模式。

In the /show/1 URL, there is a trailing path parameter. It is mapped to 'show' route in the configuration.

config.add_route('show', '/show/{id}')

该模式调用 show() 函数。它提取与给定 id 参数对应的记录，用其内容填充 HTML 表单，并允许用户更新 name 和/或 percent 字段的值。

This route invokes the show() function. It fetches the record corresponding to the given id parameter, populates the HTML form with its contents and lets the user to update values of name and/or percent fields.

@view_config(route_name='show', renderer='templates/showform.html')
def show(request):
   id=request.matchdict['id']
   row = session.query(Students).filter(Students.id == id).first()
   student={'id':row.id, 'name':row.name, 'percent':row.percent}
   return {'student':student}

Example

showform.html 模板的 HTML/jinja2 代码如下：

The HTML/jinja2 code of showform.html template is as follows −

<html>
<body>
   <form method="POST" action="http://localhost:6543/update">
   <p>Student Id: <input type="text" name="id" value="{{ student.id }} " readonly/> </p>
   <p>student Name: <input type="text" name="name" value="{{ student.name }}"/> </p>
   <p>Percentage: <input type="text" name="percent" value="{{ student.percent }}"/> </p>
   <p><input type="submit" value="Submit"> </p>
</body>
</html>

Output

让我们使用 id=3 更新记录。单击相应的编辑链接以导航到 http://localhost:6543/show/3

Let us update the record with id=3. Click on corresponding Edit link to navigate to http://localhost:6543/show/3

更改分数文本字段中的值并按提交。该表单被重定向到 /update URL，且它调用了 update() 视图。它获取所提交的数据并更新相应的对象，从而也更新了学生表中的底层行。

Change the value in marks text field and press submit. The form is redirected to /update URL and it invokes update() view. It fetches the submitted data and updates the corresponding object thereby the underlying row in students table is also updated.

@view_config(route_name='update', request_method='POST')
def update(request):
   id=int(request.POST['id'])
   student = session.query(Students).filter(Students.id == id).first()
   student.percent=int(request.POST['percent'])
   session.commit()
   return HTTPFound(location='http://localhost:6543/')

返回语句将浏览器重定向回 “/” URL，该 URL 指向 list() 函数并显示更新的分数表。

The return statement redirects the browser back to the '/' URL, which points to the list() function and shows the updated marklist.

确保在运行之前已添加 update 路由至配置。

Make sure that the update route as added to the configuration before running.

config.add_route('update', '/update')

Delete a Record

要删除与分数表中的行相对应的记录，请按照最后一列中的删除链接进行操作。例如，单击第 3 行中的删除将发出 http://localhost:6543/delete/3 URL 并调用以下视图函数：

To delete a record corresponding to a row in the marklist table, follow the Delete link in the last column. For example, clicking on Delete in 3rd row emits http://localhost:6543/delete/3 URL and invokes following view function −

@view_config(route_name='delete', renderer='templates/deleted.html')
def delete(request):
   id=request.matchdict['id']
   row = session.query(Students).filter(Students.id == id).delete()
   return {'message':'Redcord has been deleted'}

Example

从 URL 中解析的路径参数所对应的对象将被删除，且适当的消息将由以下模板 - deleted.html 呈现：

The object corresponding to the path parameter parsed from the URL is deleted and the appropriate message is rendered by the following template - deleted.html −

<html>
<body>
   <h3>{{ message}}</h3>
   <br><br>
   <a href="http://localhost:6543/">Click here to refresh the mark list</a>
</body>
</html>

显然，必须在应用程序配置注册表中添加 delete 路由。

Obviously, the delete route has to be added in the application config registry.

config.add_route('delete', '/delete/{id}')

Output

记录删除操作的结果如下所示：

The result of record delete action is as shown below −

采取以下步骤来执行上述解释的活动：

Take the following steps to perform the above explained activity −

Create a folder named as testapp in the Pyramid virtual environment
Inside testapp, create the templates folder.
Create a blank init.py inside testapp so that it becomes a package.
Put marklist.html, myform.html, showform.html and deleted.html files in "testapp\templates" folder. Codes of these files have been given above.
Save the following code as models.py in testapp.

from sqlalchemy.dialects.sqlite import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import Session
from sqlalchemy import Column, Integer, String
SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

Base = declarative_base()

class Students(Base):
      __tablename__ = 'student'
   id = Column(Integer, primary_key=True, nullable=False)
   name = Column(String(63), unique=True)
   percent = Column(Integer)

def getsession():
   engine = create_engine(
      SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
   )
   Base.metadata.create_all(bind=engine)
   Session = sessionmaker(bind = engine)
   session = Session()
   return session

Save the following code as views.py in testapp folder.

from pyramid.response import Response
from pyramid.view import view_config
from pyramid.httpexceptions import HTTPFound
from models import Students
from main import session

@view_config(route_name='list', renderer='templates/marklist.html')
def showall(request):
   rows = session.query(Students).all()
   students=[]
   for row in rows:
      students.append({"id":row.id, "name":row.name, "percent":row.percent})
      return{'students':students}

@view_config(route_name='index', renderer='templates/myform.html')
def index(request):
   return {}

@view_config(route_name='add', request_method='POST')
def add(request):
   id=request.POST['id']
   name=request.POST['name']
   percent=int(request.POST['percent'])
   student=Students(id=id, name=name, percent=percent)
   session.add(student)
   session.commit()
   return HTTPFound(location='http://localhost:6543/')

@view_config(route_name='update', request_method='POST')
def update(request):
   id=int(request.POST['id'])
   student = session.query(Students).filter(Students.id == id).first()
   student.percent=int(request.POST['percent'])
   session.commit()
   return HTTPFound(location='http://localhost:6543/')

@view_config(route_name='show', renderer='templates/showform.html')
def show(request):
   id=request.matchdict['id']
   row = session.query(Students).filter(Students.id == id).first()
   student={'id':row.id, 'name':row.name, 'percent':row.percent}
   return {'student':student}

@view_config(route_name='delete', renderer='templates/deleted.html')
def delete(request):
   id=request.matchdict['id']
   row = session.query(Students).filter(Students.id == id).delete()
   return {'message':'Redcord has been deleted'}

Save the following code as main.py in testapp folder.

from wsgiref.simple_server import make_server
from pyramid.config import Configurator
from models import getsession
session=getsession()

if __name__ == '__main__':
   with Configurator() as config:
      config.include('pyramid_jinja2')
      config.add_jinja2_renderer(".html")
      config.add_route('list', '/')
      config.add_route('index', '/new')
      config.add_route('add','/add')
      config.add_route('show', '/show/{id}')
      config.add_route('update', '/update')
      config.add_route('delete', '/delete/{id}')
      config.scan('testapp')
      app = config.make_wsgi_app()
   server = make_server('0.0.0.0', 6543, app)
   server.serve_forever()

Run main.py from the command prompt.

Python main.py

Use http://localhost:6543/ URL in the browser window. A table with only the headings and no records will be displayed.
Follow Add new link below the table to add records.
Click the "Edit" link in the table to update a record.
Clink the "Delete" link in the table to delete selected record.