How to Use SQLalchemy to Talk to SQL Databases

by: George El., April 2019, Reading time: 4 minutes

In this post I will describe briefly sqlalchemy. SqlAlchemy is a package that allows the user to talk to sql databases. Except for performing raw sql queries, it provides an Object Relational Mapper, ORM. An ORM maps classes to tables in the database, and objects to table rows. So instead of running sql queries on the tables, you perform actions on the objects, and the ORM takes care of translating these actions to sql queries. For example, when you instantiate an object of the the class, lets say person, it will insert a row in the table person. If you edit an object, the ORM will send an update sql query to the database. If you delete an object, the ORM will send a delete SQL query to the database. You don’t need to write any sql code yourself. However knowledge of sql is required if you want to see what sql queries your code is translated to and if it is the most efficient way to do it.

In the following code, I will use sqlite database, which is a simple file database, but you can use whatever database you want. The important lines in the code below are the engine where you specify the database, the base class, where your classes will inherit from, and the session which is used for running queries to the database.

from sqlalchemy import Column, Integer, String, ForeignKey, Table, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///routers.db')
base = declarative_base()
session = sessionmaker(bind=engine)()

The next step is to create the classes that will represent tables in the database.

The class Router will create a table called Routers and will have a one to many relationship with a table called Interfaces. In order to achieve this we must use router_id as a foreign key in the Interfaces tables. In addition, if I want to access the interfaces of a router using router.interfaces I have to create a relationship between the two tables using the relationship class. In this class I also specify that when I delete a router, all interfaces will be deleted too (cascade=all).

class Router(base):
    __tablename__ = "Routers"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    interfaces = relationship("Interface", cascade="all, delete, delete-orphan")

    def __repr__(self):
        return "Router:" + self.name 


class Interface(base):
    __tablename__ = "Interfaces"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    router_id = Column(Integer, ForeignKey('Routers.id'))

    def __repr__(self):
        return "Interface " + self.name

the following line will create the tables in the database

base.metadata.create_all(engine) 

the next step is to create the rows in the tables. I will do that by creating objects of the classes

rtr1 = Router(name="rtr1")
interface1 = Interface(name="gi0/1")
interface2 = Interface(name="gi0/2")
rtr1.interfaces.append(interface1)
rtr1.interfaces.append(interface2)

in order to save these changes to database, I have to call session.add() and session.commit()

session.add(rtr1)
session.commit()
print (rtr1)
print (rtr1.interfaces)

the output is

Router:rtr1
[Interface gi0/1, Interface gi0/2]

To delete the router I use

session.delete(rtr1)

this will also delete all interfaces that are related to this router.

to execute a query I use

session.query(Router).filter_by(name='rtr1')

there are many ways to query the database, which we will cover in a future post

Now I will add another class called RouterModel which will store the router model, e.g 3901, and will create a relationship between this class and the Router class. So the two classes will be like this

class RouterModel(base):
    __tablename__ = "RouterModels"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    routers = relationship("Router",backref="model",cascade="all, delete, delete-orphan")

    def __repr__(self):
        return "Model " + self.name
class Router(base):
    __tablename__ = "Routers"
    id = Column(Integer, primary_key=True)
    hostname = Column(String(50))
    interfaces = relationship("Interface", backref="router", cascade="all, delete, delete-orphan")
    model_id = Column(Integer, ForeignKey("RouterModels.id"))

    def __repr__(self):
        return "Router:" + self.hostname + " "+ str(self.model)+" "+ str(self.interfaces)

In the RouterModel I added a backref=“model”. This creates a property calles model on the Router class, and allowes me to check the model of the router, just by typing router.model. I also added the self.interfaces on the repr method, so that I can see the interfaces when I print the router. Lets see how we will use this new class.

model1 = RouterModel(name="3901")
rtr1 = Router(hostname="rtr1", model = model1)
interface1 = Interface(name="gi0/1")
interface2 = Interface(name="gi0/2")
rtr1.interfaces.append(interface1)
rtr1.interfaces.append(interface2)
session.add(rtr1)
session.commit()
print (rtr1)

Now the output will be

Router:rtr1 Model 3901 [Interface gi0/1, Interface gi0/2]

I can add a few more routers

rtr2 = Router(hostname="rtr2", model= model1)
rtr3 = Router(hostname="rtr3", model= model1)
session.add(rtr2)
session.add(rtr3)
session.commit()

and then query for this model and print the routers

model3901 = session.query(RouterModel).filter_by(name='3901').first() 
print("3901 routers:"+ str(model1.routers))

the output will be

3901 routers: 
[Router:rtr1 Model 3901 [Interface gi0/1, Interface gi0/2], 
Router:rtr2 Model 3901 [], 
Router:rtr3 Model 3901 []]

this is possible because I created a relationship between Router and RouterModel. The three tables in the database look like this (right click, open in new tab for bigger img): google cert

comments powered by Disqus