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.