How can I convince Pygal to pull data from 2 tables in an SQLAlchemy database?
These are my models:
class PlannedPost(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(30), nullable=False, default='planned')
category = db.Column(db.String(30), nullable=False, default=None)
name = db.Column(db.String(30), nullable=True)
planned_amount = db.Column(db.Float, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
date_period = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
comments = db.Column(db.Text, nullable=True)
def __repr__(self):
return f"Post('{self.title}, '{self.category}'\
, '{self.name}', '{self.planned_amount}'\
, '{self.date_period}', '{self.comments}')"
class ActualPost(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(30), nullable=False, default='actual')
category = db.Column(db.String(30), nullable=False, default=None)
actual_amount_name = db.Column(db.String(30), nullable=True)
actual_amount = db.Column(db.Float, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
comments = db.Column(db.Text, nullable=True)
def __repr__(self):
return f"ActualPost('{self.title}, '{self.category}'\
, '{self.actual_amount_name}', '{self.actual_amount}'\
, '{self.date_posted}', '{self.comments}')"
And this is what the route I dreamed up, which I know is wrong because it seems that Pygal can use only lists.
@posts.route("/graphing")
def graphing():
planned = PlannedPost.query
actual = ActualPost.query
graph = pygal.Bar()
graph.title = 'Planned Values vs Actual Values'
graph.x_labels = planned.title
graph.add('Planned', [planned.planned_amount])
graph.add('Actual', [actual.actual_amount])
graph_data = graph.render_data_uri()
return render_template('graphing.html', graph_data=graph_data)
The answer to the question is "yes". One can visualise data from a database using Pygal.
See this question for code example: Python Pygal chart pulling data from database not matching values to labels