Python Pyramid - Using SQLAlchemy

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.

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.

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 library 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.sqlite import *
SQLALCHEMY_DATABASE_URL = "sqlite:///./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.

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 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 {}

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

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

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 −

Student Details

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 −

Student Database

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

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>

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

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

Output

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

Add New

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

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}')

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. Click on corresponding Edit link to navigate to http://localhost:6543/show/3

Percentage

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 list() function and shows the updated marklist.

Updated Marklist

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

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>

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 −

Record

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.

Advertisements