Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A little SQLAlchemy challenge. See blog post at https://blog.miguelgrinberg.com/post/nested-queries-with-sqlalchemy-orm for details!
#!/usr/bin/env python
# Before you run this script make sure Flask-SQLAlchemy is installed in
# your virtual environment
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' # in-memory
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
example_data = [
(1, '2018-01-01'),
(1, '2018-01-05'),
(3, '2018-01-07'),
(1, '2018-02-06'),
(3, '2018-01-31'),
(2, '2018-02-01'),
(3, '2018-02-01'),
(3, '2018-01-20'),
(2, '2018-02-07'),
]
class Order(db.Model):
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
customer_id = db.Column(db.Integer, index=True)
order_date = db.Column(db.String, index=True)
def load_data():
db.create_all()
for row in example_data:
order = Order(customer_id=row[0], order_date=row[1])
db.session.add(order)
db.session.commit()
def main():
load_data()
# TODO: create a query that returns the rows of the table sorted by
# customer_id, with the most recent customer first, according to
# last order. The entries of a customer need to be sorted with the
# most recent order first.
# See blog post for details!
query = Order.query
# print results
for row in query:
print(row.id, row.customer_id, row.order_date)
if __name__ == '__main__':
main()
@Amir1Mohammad

This comment has been minimized.

Copy link

Amir1Mohammad commented Nov 4, 2019

I have question . if I want to sort customers by order counter ? For example, anyone who has the highest number of orders comes first and the smaller the order the less priority.
Or if I want to have the average number of orders in the last month? Then what should I do?
Thanks for your helping ...

@miguelgrinberg

This comment has been minimized.

Copy link
Owner Author

miguelgrinberg commented Nov 4, 2019

This is the topic of the blog article referenced in the description of this gist. Have a look at the article.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.