Data is not being inserted into tables python, SQLAlchemy, postgresql, and pgAdmin4

Question

I am trying to insert the following data into two tables using a combination of python, sqlalchemy, postgresql, and pgadmin4. The primary keys should auto-populate in both tables based on the specifications of the script.

run_app.py

from run import db, Book, Publication

p1 = Publication("Oxford Publications")
p2 = Publication("Paramount Press")
p3 = Publication("Oracle Books Inc")
db.session.add_all([p1,p2,p3])

b1 = Book("Miky's Delivery Service", "William Dobelli", 3.9, "ePub", "broom-145379.svg", 123, 1)
b2 = Book("The Secret Life of Walter Kitty", "Kitty Stiller", 4.1, "Hardcover", "cat-150306.svg", 133, 1)
b3 = Book("The Empty Book of Life", "Roy Williamson", 4.2, "eBook", "book-life-34063.svg", 153, 1)
b4 = Book("Life After Dealth", "Nikita Kimmel", 3.8, "Paperback", "mummy-146868.svg", 175, 2)
b5 = Book("The Legend of Dracula", "Charles Rowling", 4.6, "Hardcover", "man-37603.svg", 253, 2)
b6 = Book("Taming Dragons", "James Vonnegut", 4.5, "MassMarket Paperback", "dragon-23164.svg", 229, 2)

db.session.add_all([b1,b2,b3,b4,b5,b6])
db.session.commit()

run.py

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)
app.config.update(

    SECRET_KEY=########',
    SQLALCHEMY_DATABASE_URI='postgresql://postgres:########@localhost/catalog_db',
    SQLALCHEMY_TRACK_MODIFICATIONS=False
)
db = SQLAlchemy(app)

# PUBLICATION TABLE
class Publication(db.Model):
    __tablename__ = 'publication'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return 'The Publisher is {}'.format(self.name)


# BOOKS TABLE
class Book(db.Model):
    __tablename__ = 'book'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False, index=True)
    author = db.Column(db.String(350))
    avg_rating = db.Column(db.Float)
    format = db.Column(db.String(50))
    image = db.Column(db.String(100), unique=True)
    num_pages = db.Column(db.Integer)
    pub_date = db.Column(db.DateTime, default=datetime.utcnow())

    # ESTABLISH RELATIONSHIP
    pub_id = db.Column(db.Integer, db.ForeignKey('publication.id'))

    def __init__(self, title, author, avg_rating, book_format, image, num_pages, pub_id):

        self.title = title
        self.author = author
        self.avg_rating = avg_rating
        self.format = book_format
        self.image = image
        self.num_pages = num_pages
        self.pub_id = pub_id

    def __repr__(self):
        return '{} by {}'.format(self.title, self.author)


if __name__ == '__main__':
    db.create_all()
    app.run(debug=True)

However when executing run_app.py the follow error occurs:

psycopg2.errors.ForeignKeyViolation: insert or update on table "book" violates foreign key constraint "book_pub_id_fkey"
DETAIL:  Key (pub_id)=(1) is not present in table "publication".

Any way to make the tables auto-generate the primary keys and have them filled with the corresponding data?

Answer 1

In order to capture the newly generated id field from the new Publication records, change run_app.py to:

from run import db, Book, Publication

p1 = Publication("Oxford Publications")
p2 = Publication("Paramount Press")
p3 = Publication("Oracle Books Inc")
db.session.add_all([p1,p2,p3])

b1 = Book("Miky's Delivery Service", "William Dobelli", 3.9, "ePub", "broom-145379.svg", 123, p1.id)
b2 = Book("The Secret Life of Walter Kitty", "Kitty Stiller", 4.1, "Hardcover", "cat-150306.svg", 133, p1.id)
b3 = Book("The Empty Book of Life", "Roy Williamson", 4.2, "eBook", "book-life-34063.svg", 153, p1.id)
b4 = Book("Life After Dealth", "Nikita Kimmel", 3.8, "Paperback", "mummy-146868.svg", 175, p2.id)
b5 = Book("The Legend of Dracula", "Charles Rowling", 4.6, "Hardcover", "man-37603.svg", 253, p2.id)
b6 = Book("Taming Dragons", "James Vonnegut", 4.5, "MassMarket Paperback", "dragon-23164.svg", 229, p2.id)

db.session.add_all([b1,b2,b3,b4,b5,b6])
db.session.commit()