This course is an introduction to CRUD web applications. It uses Vagrant as a development environment along with Python and SQLalchemy.

Working with CRUD

Most web applications have a front end web page that connects to a database. The most common database operations are summarized with CRUD: Create, Read, Update, and Delete.

Most relational databases use SQL to query the database. Python provides an SQLite database, but you can only write SQL queries as strings. This doesn’t allow for any error checking. An alternative to directly coding SQL is using an ORM or an Object-relational Mapping. This additional layer translates native language objects to SQL statements as well as the inverse operation. This is what SQLalchemy does.

The four components for setting up are configuration, class, table, and mapper.

Configuration is a file that imports all relevant modules and creates base objects and initial database creation. This is stuff that doesn’t change much between projects or over the course of a project.

ORM classes represent database tables. They all inherit from a base SQLalchemy class. It will contain table and mapper code. Set __tablename__ to a string that is the name of the table.

Operations with SQLAlchemy:

In this lesson, we performed all of our CRUD operations with SQLAlchemy on an SQLite database. Before we perform any operations, we must first import the necessary libraries, connect to our restaurantMenu.db, and create a session to interface with the database:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from database_setup import Base, Restaurant, MenuItem

engine = create_engine('sqlite:///restaurantMenu.db')
Base.metadata.bind=engine
DBSession = sessionmaker(bind = engine)
session = DBSession()

CREATE

We created a new Restaurant and called it Pizza Palace:

myFirstRestaurant = Restaurant(name = "Pizza Palace")
session.add(myFirstRestaurant)
sesssion.commit()

We created a cheese pizza menu item and added it to the Pizza Palace Menu:

cheesepizza = menuItem(name="Cheese Pizza", description = "Made with all natural ingredients and fresh mozzarella", course="Entree", price="$8.99", restaurant=myFirstRestaurant)
session.add(cheesepizza)
session.commit()

READ

We read out information in our database using the query method in SQLAlchemy:

firstResult = session.query(Restaurant).first()
firstResult.name

items = session.query(MenuItem).all()
for item in items:
    print item.name

UPDATE

In order to update and existing entry in our database, we must execute the following commands:

  1. Find Entry
  2. Reset value(s)
  3. Add to session
  4. Execute session.commit()

We found the veggie burger that belonged to the Urban Burger restaurant by executing the following query:

veggieBurgers = session.query(MenuItem).filter_by(name= 'Veggie Burger')
for veggieBurger in veggieBurgers:
    print veggieBurger.id
    print veggieBurger.price
    print veggieBurger.restaurant.name
    print "\n"

Then we updated the price of the veggie burger to $2.99:

UrbanVeggieBurger = session.query(MenuItem).filter_by(id=8).one()
UrbanVeggieBurger.price = '$2.99'
session.add(UrbanVeggieBurger)
session.commit()

DELETE

To delete an item from our database we must follow the following steps:

  1. Find the entry
  2. Session.delete(Entry)
  3. Session.commit()

We deleted spinach Ice Cream from our Menu Items database with the following operations:

spinach = session.query(MenuItem).filter_by(name = 'Spinach Ice Cream').one()
session.delete(spinach)
session.commit()

Web servers

The web is a network of connected computers. These computer communicate using a series of protocols: TCP, UDP, IP, HTTP(S).

TCP and UDP are used to break data into packets and ensure packets are reliably sent and received between computers. IP is used to identify computers in concert with a DNS or domain name server that translates between an IP address and the actual network id of a computer. The network id is usually assigned by the internet provider. HTTP is used to communicate between a server and a client using requests.