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?
