Migrating/Updating my TinyDB MAC API – Part 2 – SQLAlchemy

On By dustin

In my previous posts I’ve laid out:

  • My database setup
  • How to yank mac addresses
  • General Project Organization

Now its time to start interacting with my database using SQLAlchemy. For this project I primarily referenced FastAPI’s excellent guide on working with SQL. Their guide provides an excellent walk-through and has served as my reference point for both SQLAlchemy and FastAPI projects. If you’re struggling with either, I recommend you check out their guide. In the meantime, I’ll provide a quick overview of the components I used as well as show the core select and insert statements my project uses.

The first worth discussing is models.py. This module is composed of classes that correspond to the my database’s individual tables. I represent all three tables here, and my datetime_mac_junction table is simply represented as such:

class DatetimeMacJunction(Base):
    __tablename__ = "datetime_mac_junction"
    mac_address = Column(VARCHAR, primary_key=True)
    datetime_id = Column(DATE, primary_key=True)

The actual action takes places in the crud.py module. This module is where I compose the various SQL/ SQLAlchemy statements my project requires. The first I’d like to highlight here involves inserting mac addresses currently online (discovered via net_cmds.py):

def insert_dt_macs(db: Session, datetime, mac_list):
    for m in mac_list:
        mac_insert = models.DatetimeMacJunction(datetime_id=datetime, mac_address=m)
        db.add(mac_insert)
        db.commit()
        db.refresh(mac_insert)

The insert_dt_macs requires three total parameters and is fairly straightforward because it references the class we created in models.py. The function simply loops over our list and inserts individual macs into our junction table. Below is a quick description of each of the parameters:

  • db: corresponds to information regarding the database (hostname, credentials, etc)
  • datetime: The datetime that the mac address list was created.
  • mac_list: A list of mac addresses discovered via the net_cmds function

The next function I’d like to highlight, select_recent_macs, will display what mac addresses were most recently online:

def select_recent_macs(db: Session):
    most_recent = select_recent_datetime(db)
    recent_raw = most_recent.__repr__()
    return db.query(models.DatetimeMacJunction).filter(models.DatetimeMacJunction.datetime_id == recent_raw).all()

Again, this function is fairly straightforward but does call another function that returns the datetime for the most recent entry into our table. We then reference this datetime when querying the datetime_mac_junction table so that we only see entries that match this datetime.

I’m getting a bit ahead of myself…but eventually these two functions support our API. The insert function will be triggered either at scheduled intervals or when a new device is added. Meanwhile the select_recent_macs function will eventually support our “/current_macs” API endpoint as shown below: