Using SQLAlchemy in python to control database

SQLAlchemy is an open-source python module that let developers easily create and manipulate databases with python. SQL language is not required because everything is already encapsulated inside the module. It also conveniently has a “session” feature that provides a function similar to git – all changes can be staged and committed altogether.

An introductory tutorial can be found here.

The idea is called Object Relational Programming(ORM). SQLAlchemy lets you use object oriented programming language such as python to operate on databases. For example, for a Student table in a database, it can have several attributes: id, name, and school. id and name can be represented by primitive types but school may be another object. In the actual database, there is a link from the student to the school that he or she belongs to. SQLAlchemy transforms this relationship into object references, which makes it easy to update tables with python code that can be run on a server.

Here are some basic code segments that can be used:

#import and basic setup
import os
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()

The above piece of code import necessary modules. Column, ForeignKey etc are not default python classes, so they must be imported. A declarative_base is a class that contains a database table, a mapper which matches the table with python class, and a class object. An engine stores data in the local directory.

class Restaurant(Base):
    __tablename__ = 'restaurant'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)


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(8))
    course = Column(String(250))
    restaurant_id = Column(Integer, ForeignKey('restaurant.id'))
    restaurant = relationship(Restaurant)

As you can see, it is very easy to create classes in python/tables in database. “__tablename__” always needs to be provided. Other elements are declared like normal classes. String(n) means this String can have as many as n characters. When nullable is true, the column can never be empty.

At the end, engine needs to be started:

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

Base.metadata.create_all(engine)

After this, a restaurantmenu.db file will be created at local directory and fully functional. Hooray!

That’s pretty much it. I’ll update when I learn more. Code for this post is from Udacity.
 

Leave a Reply

Your email address will not be published. Required fields are marked *