Can SQLAlchemy add new records and automate the relationship between tables without me manually checking for PK uniqueness?

Question

I am a relative newcomer to SQLAlchemy and have read the basic docs. I'm currently following Mike Driscoll's MediaLocker tutorial and modifying/extending it for my own purpose.

I have three tables (loans, people, cards). Card to Loan and Person to Loan are both one-to-many relationships and modelled as such:

from sqlalchemy import Table, Column, DateTime, Integer, ForeignKey, Unicode
from sqlalchemy.orm import backref, relation
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("sqlite:///cardsys.db", echo=True)
DeclarativeBase = declarative_base(engine)
metadata = DeclarativeBase.metadata

class Loan(DeclarativeBase):
    """
    Loan model
    """

    __tablename__ = "loans"

    id = Column(Integer, primary_key=True)
    card_id = Column(Unicode, ForeignKey("cards.id"))
    person_id = Column(Unicode, ForeignKey("people.id"))
    date_issued = Column(DateTime)
    date_due = Column(DateTime)
    date_returned = Column(DateTime)
    issue_reason = Column(Unicode(50))
    person = relation("Person", backref="loans", cascade_backrefs=False)
    card = relation("Card", backref="loans", cascade_backrefs=False)

class Card(DeclarativeBase):
    """
    Card model
    """

    __tablename__ = "cards"

    id = Column(Unicode(50), primary_key=True)
    active = Column(Boolean)

class Person(DeclarativeBase):
    """
    Person model
    """

    __tablename__ = "people"

    id = Column(Unicode(50), primary_key=True)
    fname = Column(Unicode(50))
    sname = Column(Unicode(50))

When I try to create a new loan (using the below method in my controller) it works fine for unique cards and people, but once I try to add a second loan for a particular person or card it gives me a "non-unique" error. Obviously it's not unique, that's the point, but I thought SQLAlchemy would take care of the behind-the-scenes stuff for me, and add the correct existing person or card id as the FK in the new loan, rather than trying to create new person and card records. Is it up to me to query to the db to check PK uniqueness and handle this manually? I got the impression this should be something SQLAlchemy might be able to handle automatically?

def addLoan(session, data):

    loan = Loan()
    loan.date_due = data["loan"]["date_due"]
    loan.date_issued = data["loan"]["date_issued"]
    loan.issue_reason = data["loan"]["issue_reason"]

    person = Person()
    person.id = data["person"]["id"]
    person.fname = data["person"]["fname"]
    person.sname = data["person"]["sname"]
    loan.person = person

    card = Card()
    card.id = data["card"]["id"]
    loan.card = card

    session.add(loan)
    session.commit()

In the MediaLocker example new rows are created with an auto-increment PK (even for duplicates, not conforming to normalisation rules). I want to have a normalised database (even in a small project, just for best practise in learning) but can't find any examples online to study.

How can I achieve the above?

Answer 1

It's up to you to retrieve and assign the existing Person or Card object to the relationship before attempting to add a new one with a duplicate primary key. You can do this with a couple of small changes to your code.

def addLoan(session, data):

    loan = Loan()
    loan.date_due = data["loan"]["date_due"]
    loan.date_issued = data["loan"]["date_issued"]
    loan.issue_reason = data["loan"]["issue_reason"]

    person = session.query(Person).get(data["person"]["id"])
    if not person:
        person = Person()
        person.id = data["person"]["id"]
        person.fname = data["person"]["fname"]
        person.sname = data["person"]["sname"]
    loan.person = person

    card = session(Card).query.get(data["card"]["id"])
    if not card:
        card = Card()
        card.id = data["card"]["id"]
    loan.card = card

    session.add(loan)
    session.commit()

There are also some solutions for get_or_create functions, if you want to wrap it into one step.

If you're loading large numbers of records into a new database from scratch, and your query is more complex than a get (the session object is supposed to cache get lookups on its own), you could avoid the queries altogether at the cost of memory by adding each new Person and Card object to a temporary dict by ID, and retrieving the existing objects there instead of hitting the database.