Python Pyramid Tutorial

Selected Reading

Pyramid - Using SQLAlchemy

Python Pyramid - Using SQLAlchemy

In this chapter, we shall learn how to use a relational database as a back-end with the Pyramid web apppcation. Python can interact with almost every relational database using corresponding DB-API compatible connector modules or drivers. However, we shall use SQLAlchemy pbrary 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.

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 pke 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.

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.

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


pip install sqlalchemy

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

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.sqpte import *
SQLALCHEMY_DATABASE_URL = "sqpte:///./test.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args = {"check_same_thread": False})

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, 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)

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

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

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>

In the Pyramid apppcation 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 {}

In the apppcation 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()

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 finapzed by calpng 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/ )

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


config.add_route( add , /add )

Output

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

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

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

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

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


@view_config(route_name= pst , renderer= templates/markpst.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

The markpst.html template renders the Students pst 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>

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


config.add_route( pst ,  / )

Output

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

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

Update Existing Record

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

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

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

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

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

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/ )

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

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


config.add_route( update ,  /update )

Delete a Record

To delete a record corresponding to a row in the markpst table, follow the Delete pnk in the last column. For example, cpcking 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

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/">Cpck here to refresh the mark pst</a>
</body>
</html>

Obviously, the delete route has to be added in the apppcation 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 markpst.html, myform.html, showform.html and deleted.html files in "testapp emplates" folder. Codes of these files have been given above.

Save the following code as models.py in testapp.


from sqlalchemy.dialects.sqpte 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 = "sqpte:///./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= pst , renderer= templates/markpst.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( pst ,  / )
      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 pnk below the table to add records.

Cpck the "Edit" pnk in the table to update a record.

Cpnk the "Delete" pnk in the table to delete selected record.

Python Pyramid - Using SQLAlchemy

Database Engine

Model Class

Add a New Student Record

Example

Output

Show List of All Records

Example

Output

Update Existing Record

Example

Output

Delete a Record

Example

Output

友情链接