Aug
09
Python Pyramid – Using SQLAlchemy ”; Previous Next 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. 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 − 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 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>