Can SQLAlchemy add new records and automate relationships between tables without me, manually checking the uniqueness of a PC?

Question

I am a relative newbie to SQLAlchemy and have read the basic docs. I am currently following the Mike Driscoll MediaLocker tutorial and modifying / expanding it for my own purpose.

I have three tables (loans, people, cards). A card for a loan and a person for a loan are one-to-many relationships and are modeled 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 credit (using the method below in my controller), it works great for unique cards and people, but as soon as I try to add a second credit for a specific person or card, it gives me "-unique". Obviously, this is not the only point, but I thought SQLAlchemy would take care of the backstage things for me and add the correct existing person or card ID as FK to the new loan, instead of trying to create a new person and card. Is it up to me to query db to verify PK uniqueness and handle it manually? I got the impression that this should be something that SQLAlchemy can 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 lines are created using the automatic PK increment (even for duplicates that do not comply with normalization rules). I want to have a normalized database (even in a small project, only for the best learning practice), but can not find examples online to study.

How can I achieve the above?

Answer 1

You need to get and assign an existing Person or Card object for the relationship before trying to add a new one with a duplicate primary key. You can do this with a few 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 flip them one step.

If you load a large number of records into a new database from scratch, and your query is more complex than get (the session object must cache get search queries yourself), you can avoid queries in general due to memory by adding each new Person and Card object to temporary dict by ID and instead deleting existing objects instead of hitting the database.