项目思路:我想将从新闻站点抓取的数据存储在Postgres数据库中，然后使用Flask框架查询这些数据并将其显示在站点中。
要抓取的数据类型:标题，链接，图片，日期(将日期保存为字符串，因为每个网站的日期格式不同)，新闻文章类型(政治，经济和体育新闻)。
我在数据库中创建一对多关系(一种类型有许多新闻数据)。
我使用了BeautifulSoup和请求库来抓取
models.py
import os
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Type(db.Model):
__tablename__ = 'Type'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
type_name = db.Column(db.String(80))
news = db.relationship('News', backref='type_news', lazy='dynamic')
def __init__(self, type_name):
self.type_name = type_name
def __repr__(self):
return f'<Type {self.id}>'
class News(db.Model):
__tablename__ = 'news'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(200))
link = db.Column(db.String(200))
date = db.Column(db.String(200))
img = db.Column(db.String(300))
type_id = db.Column(db.Integer, db.ForeignKey('type.type_id'))
type = db.relationship('Type')
def __init__(self, title, link, date, img):
self.title = title
self.link = link
self.date = date
self.img = img
def __repr__(self):
return f'<News {self.id}>'
config.py
from os import environ, path
db_host = environ.get('DB_HOST', default='localhost')
db_name = environ.get('DB_NAME', default='NewsSqlDB')
db_password = environ.get('DB_PASSWORD', default='securepass')
db_port = environ.get('DB_PORT', default='5432')
db_user = environ.get('DB_USERNAME', default='postgres')
# Database
SQLALCHEMY_DATABASE_URI = f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
SQLALCHEMY_TRACK_MODIFICATIONS = False
initpy
import os
from flask import Flask
def create_app(config_filename=None):
app = Flask(__name__, instance_relative_config=False)
app.config.from_mapping(
SECRET_KEY=os.getenv('SECRET_KEY') or b'x01xebxab/x026xd3xc4?xfdx02x1bx058x81`xe6['
b'xd6x8bRnxec5',
)
if config_filename is None:
app.config.from_pyfile('config.py', silent=True)
else:
app.config.from_mapping(config_filename)
from .models import db
db.init_app(app)
return app
数据抓取后的样子:这是我抓取网站后保存数据的方法(每种类型我都保存了很多数据)
data = {
'Type': 'Sport',
'Title': ['any title about sport', 'any title about sport', 'any title about sport', ...],
'Link': ['https://www.ninanews.com/Website/News/Details?key=923825', ...],
'Date': ['الجمعة 27 , آب 2021 19:08', ...],
'Image':
['https://images.weserv.nl/url=ninanews.net/Uploads/Images/2021/8/923825c67
3d87e-ef7c-4d68-ad58-1981d5681284.jpg&w=200&h=150&t=absolute', ...],
}
scrape.py
data = {}
for i in [2, 4, 5]:
url = f'https://www.ninanews.com/Website/News/List?key={i}'
data["Type"] = get_url_type(url)[0]
data["Title"] = get_title(i, pages)
data["Link"] = get_link(i, pages)
data["Date"] = get_date(i, pages)
data["Image"] = get_images(i, pages)
news = News(title=data['Title'], link=data['Link'], date=data['Date'], img=data['Image'])
type_ = Type(type_name=data['Type'])
type_.news.append(news)
db.session.add(type_)
db.session.add(news)
db.session.commit()
app.py
from News import create_app
from News.models import db, News, Type
app = create_app()
@app.route('/')
def hello_world():
type_ = Type.query.order_by(Type.type_name).all()
titles = News.query.order_by(News.title).all()
return f"Titles: {type_}" # Titles: []
if __name__ == '__main__':
with app.app_context():
import News.scrape_ninanews
db.create_all()
app.run()
我的问题:我不能保存这些数据到postgresql数据库(flask_sqlalchemy)，所以我可以在我的网站上显示它。我看到这个错误:
[SQL: INSERT INTO news (title, link, date, img, type_id) VALUES (%(titl
e)s, %(link)s, %(date)s, %(img)s, %(type_id)s) RETURNING news.id]
[parameters: {'title': ['العراق يحرز المركز الثاني في بطولة العالم بالق
وة البدنية', 'باير ليفركوزن يتصدر الدوري الألماني', 'منتخبنا الوطني يغا
در إلى كوريا الجنوبية', 'اتحاد ... (336 characters truncated) ... كأس
السوبر', 'الجوية يخطف الدولي علاء عباس', 'غدا.. طائرة خاصة تنقل منتخبنا
إلى كوريا الجنوبية', 'منتخبنا الأولمبي يخسر أمام منتخب ليبيا الأول ودي
اً'], 'link': ['https://www.ninanews.com/Website/News/Details?key=92420
2', 'https://www.ninanews.com/Website/News/Details?key=924200', 'https:
//www.ninanews.com/Web ... (480 characters truncated) ... site/News/Det
ails?key=923773', 'https://www.ninanews.com/Website/News/Details?key=92
3757', 'https://www.ninanews.com/Website/News/Details?key=923755'], 'da
te': ['السبت 28 , آب 2021 21:14', 'السبت 28 , آب 2021 20:53', 'السبت 28
, آب 2021 20:12', 'السبت 28 , آب 2021 19:34', 'السبت 28 , آب 2021 17:1
3', 'الجمعة 2 ... (72 characters truncated) ... :13', 'الجمعة 27 , آب 2
021 14:29', 'الجمعة 27 , آب 2021 14:19', 'الجمعة 27 , آب 2021 14:12', '
الجمعة 27 , آب 2021 13:37', 'الجمعة 27 , آب 2021 13:34'], 'img': ['http
s://images.weserv.nl/?url=ninanews.net/Uploads/Images/2021/8/924202-e4e
b72b7-f1d5-46f3-ae45-543e35073a79.jpg&w=200&h=150&t=absolute', 'https:/
/i ... (1229 characters truncated) ... =absolute', 'https://images.wese
rv.nl/?url=ninanews.net/Uploads/Images/2021/8/923755-48f28451-08c1-452b
-9de5-14bb07e842fa.jpg&w=200&h=150&t=absolute'], 'type_id': 1}]
(Background on this error at: https://sqlalche.me/e/14/9h9h)
News表的列长度为200。您尝试插入的一些字段的数据是>200. 您可以将长度设置为更大的值，然后重试。