Create, Read, Update, Delete. CRUD! Working with data inside a database.

In the previous post, we created our database. Now it's time to populate it with some data. We created two tables, Restaurant and MenuItem to store the name of the restaurant and the menu item details respectively. In this post we will be performing the basic operations that are performed on the database inside a data-driven web application.

Creating Data

Using sqlalchemy, creating data is as easy as creating new objects in python.

So let's create another python script to fill some data into our empty database. Let's call it 'create_data.py'. Next, we need to add some dependencies, classes from our database_setup.py file. Type out the following lines of code. The description for each line of code is give in the comments

# We must first import the following dependencies:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Next we import the classes from the database_setup.py file we created in the previous blog

from database_setup import Base, Restaurant, MenuItem

# Then we connect our database to the program

engine = create_engine('sqlite:///restaurantmenu.db')

# Then we bind the 'Base' class to the 'engine'
Base.metadata.bind = engine

# Next I will create a sessionmaker object
# This establishes a link of communication between our code executions and the engine we just created

DBSession = sessionmaker(bind = engine)
session = DBSession()

SQLAlchemy executes database operations via an interface, called a session. A session allows us to write down all the commands that we we want to execute but does not send them to the database until we call the commit() method.

Now we are set to create our first restaurant and menu item.

# Creating Data

myFirstRestaurant = Restaurant(name="Balli Food Point")
session.add(myFirstRestaurant)
session.commit()

noodles = MenuItem(name = "Noodles",description = "Yummy spicy noodles with the$
session.add(noodles)
session.commit()

 

Reading Data

 

We can query all data objects from the tables using the query() method on the session object to read data. Run the following code in a python script and see what happens:

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

# Read data

print(session.query(MenuItem).all()) # Query all objects from MenuItem table
print(session.query(Restaurant).all()) # Query all objects from Restaurant table

firstResult = session.query(MenuItem).first() # Query first row from MenuItem
print(firstResult.name) 
print(firstResult.description)
print(firstResult.price)

You should get an output similar to this:

devastating_dj@hp-2000-notebook:~/Blog$ python read_data.py
[<database_setup.MenuItem object at 0x7f4a80b42210>]
[<database_setup.Restaurant object at 0x7f4a80b33890>]
Noodles
Yummy spicy noodles with the goodness of veggies
Rs 40

SQLAlchemy has an extensive collection of query that we can perform on our database.

 

Updating Existing Data

To update existing data inside database you need to follow four simple steps:

1. Find Entry
2. Reset Values
3. Add to session
4. session.commit()

 These steps can be implemented as follows (create a python script and bind the code to the database and add the following):

# Find entry
results = session.query(Restaurant).filter_by(name = "Peppy Pizza") #This returns a list that we iterate over
for result in results:
        print(result.name)
        print(result.id)
# Search by id
query = session.query(Restaurant).filter_by(id = 1).one()

# Reset values
query.name = "New Peppy Pizza Place"

# Add to session
session.add(query)

session.commit()

 

Deleting Data

 

Just like while updating data we first find the entry that we want to update, in deleting we do the same except calling the add() method we use the the delete method on the session object.

# Search by id
query = session.query(Restaurant).filter_by(id = 1).one()

session.delete(query)

session.commit()

Congratulations! You have learned how to use an ORM to create a database and execute CRUD operations, setting the foundation for our interactive menu application. 

Next, we will create a web server that will be able to perform these CRUD operations based on the request from the user.

Setting up and configuring the database

We need to create a database before we can perform any of the CRUD operations. So let's create one! With SQLAlchemy we can write a single python file to setup and configure our database. To follow along you need to make sure that you have installed Python, SQLAlchemy and SQLite on your linux machine.

Click here to know how to install Python(if not already installed)
Click here to know how to install SQLAlchemy(if not installed already)
Click here to know how install SQLite(if not installed already)

If you are a windows user then you have to use a virtual machine. You can use either VMware or VirtualBox, or any other (it doesn't really matter which one you use). Click here to setup a virtual machine for linux on windows using VirtualBox.

Creating a database with SQLAlchemy

Creating a  database with sqlalchemy has four major coding components.

• Configuration - Here we import all the necessary modules
• Class - Code which we will use to represent our data in python
• Table - Representing the specific data in our database
• Mapper - Connects the columns of our table to the class that represents it

Okay, time to create our database. Open your favorite text editor and make the following imports:

 #Configuration  
   
 import sys  
   
 from sqlalchemy import Column, ForeignKey, Integer, String  
   
 from sqlalchemy.ext.declarative import declarative_base  
   
 from sqlalchemy.orm import relationship  
   
 from sqlalchemy import create_engine  
   
 Base = declarative_base() # Creating an instance of declarative_base() in order for our class to inherit all the features of sqlalchemy  
   

Next, we need to write our class code. Our class code is the object oriented representation of our tables in the database. We need to make our classes extend the base class we just created. But let's make it a bit meaningful by working on a use case. Suppose we want to create a database to store various food items of different restaurants. For that we will be creating two tables, one for storing the restaurant information and the other for storing the details of every food item.

 Let's create two classes, 'restaurant' and 'menu_item', that will correspond to the two tables that we want to create in our database. Next, we create a table representation inside each of our classes. We use a special variable starting and ending with a double underscore (__tablename__) to let sqlalchemy know the variables that we will use to refer to our tables. Next we will write our mapper code. Our mapper code will create variables that will be used to create columns in our database. Here is the code for the remaining three coding components:

 # Class
  
 class Restaurant(Base): 
 
 # Table  

     __tablename__ = 'restaurant'  
     # Mapper  
     name = Column(  
     String(80), nullable = False)  
     id = Column(  
     Integer, primary_key = True)
  
 class MenuItem(Base):  

     __tablename__ = 'menu item'  
     name = Column(String(80), nullable = False)  
     id = Column(Integer, primary_key = True)  
     description = Column(String(250))  
     price = Column(String(250))  
     restuarant_id = Column(Integer, ForeignKey('restaurant.id'))  
     restaurant = relationship(Restaurant)  

Finally, at the end of our file we need to add two more lines of code:

 # We create an instance of create_engine class and point to the database that we will use
  
 engine = create_engine('sqlite:///restaurantmenu.db')  
 Base.metadata.create_all(engine) # This will create new tables inside the database  

Save the file with the name database_setup.py and run it from the terminal:

 root@kali:~/Documents/Project# python database_setup.py   

If everything went well, you should see a file named 'restaurantmenu.db' inside your working directory.
Congratulations! You just created an empty database using sqlalchemy. In the next post we will fill it with some data.