python - 数据未插入到 python、SQLAlchemy、postgresql 和 pgAdmin4 表中
问题描述
我正在尝试使用 python、sqlalchemy、postgresql 和 pgadmin4 的组合将以下数据插入到两个表中。主键应根据脚本的规范自动填充到两个表中。
运行应用程序.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()
运行.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)
但是在执行 run_app.py 时会出现以下错误:
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".
有什么方法可以让表自动生成主键并用相应的数据填充它们?
解决方案
为了从新的 Publication 记录中捕获新生成的 id 字段,请将 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, 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()
推荐阅读
- python - 如何在不抓取文档本身的情况下从网页获取文件的下载链接?
- python-2.7 - 用于在树莓派上自动更改 crontab 启动的 Python 脚本
- apache-kafka - java.lang.IllegalArgumentException,同时将 SASL_PLAINTEXT 身份验证添加到 kafka 代理
- linux - 当从 hdi 工作节点运行时,hdfs 是否知道 umi 安全上下文?
- python - 我不断收到我的列表索引超出范围的错误
- android - 有没有办法使用 LiveData 观察者等动态管理片段流导航?
- maven - 从命令行或 pom 文件附加到子 pom 中的父 pom 属性
- javascript - 了解程序中的执行流程
- javascript - icremeant 不能正常工作,调用另一个数组,里面有另一个值
- google-cast - CAF 接收器是否支持 Live DAI?