All,
Python 3.6 under windows 10 - I am using flask_sqlalchemy and finding it
quite good with taking a lot of the heavy lifting of writing SQL statements.
I have a question in relation to SQL relationships and how would this be
done using SQL or flask_sqlalchemy or sqlalchemy.
I have three tables which are related to each other.
Table 1 has many records related to table 2.
Table 2 has many relationships to table 3.
Below is the model used by flask_sqlalchemy to make things clearer:
class Categories(db.Model):
# one to many relationships to SubCategories
id = db.Column(db.Integer, primary_key=True)
category = db.Column(db.String(64), index=True, unique=True)
subcategories = db.relationship('SubCategories', backref='categories',
lazy='dynamic')
def __repr__(self):
return '<Categories {}>'.format(self.category)
class SubCategories(db.Model):
# Many to one relationship to Categories.
# One to many relationship with Transactions.
id = db.Column(db.Integer, primary_key=True)
subcategory = db.Column(db.String(80), index=True, unique=True)
category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
transactions = db.relationship('Transactions',
backref='sub_categories', lazy='dynamic')
class Transactions (db.Model):
# Many to one relationship to Sub_categories
# Many to one relationships with Accounts.
id = db.Column(db.Integer, primary_key=True)
transactiondate = db.Column(db.Date, index=True, nullable=False)
description = db.Column(db.String(80), nullable=False)
amount = db.Column(db.Float, nullable=False)
subcategory_id = db.Column(db.Integer,
db.ForeignKey('sub_categories.id'))
account_no = db.Column(db.Integer,
db.ForeignKey('accounts.account_number'))
Thus, when I have a query from the view:
records = Transactions.query.order_by(Transactions.subcategory_id,
Transactions.transactiondate.desc())
page = request.args.get('page', 1, type=int)
records = records.paginate(page, app.config['POSTS_PER_PAGE'], False)
next_url = url_for('index', page=records.next_num) if records.has_next
else None
prev_url = url_for('index', page=records.prev_num) if records.has_prev
else None
return render_template('index.html', title='Budget Program Main Page',
records = records.items, tables = tables, account = 'all Transactions',
prev_url = prev_url, next_url = next_url, form = form, sort_form =
sort_form)
Template HTML code which displays the category and sub_category text values
based upon the above query.
{% for row in records %}
<tr>
<td>{{row.accounts.account_name}}</td>
<td>{{row.account_no}}</td>
<td>{{row.transactiondate}}</td>
<td>{{row.description}}</td>
<td>{{row.amount}}</td>
<td>{{row.sub_categories.categories.category}}</td>
<td>{{row.sub_categories.subcategory }}</td>
</tr>
{% endfor %}
What I cannot do, is use sub_categories.categories.category or
sub_categories.subcategory in the query statements to sort the transaction
table by category or sub_category . For example the following does not work
sort_by = Transactions.sub_categories.categories.category
records = Transactions.query.order_by(sort_by.desc())
sqlalchemy complains that it cannot find the object .categories. Thus I do
not know how to sort on the category table which is a child of
sub_categories which is a child of transactions. How would this be done in
SQL?
I hope this is the right place for this question.
Sean
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
