/Users/song/codelearn/Querying With Dates in Flask-SQLAlchemy/datequeries/app.py

from datetime import date, datetime, timedelta
from faker import Faker 
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy 
from sqlalchemy import func

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Transactions(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer)
    date_posted = db.Column(db.Date)
    datetime_posted = db.Column(db.DateTime)

@app.route('/')
def index():
    # transactions = Transactions.query.all()


    #  ------------------------------------------------------------------------
    # 1. 检测某一特定日期
    # from datetime import date, datetime, timedelta
    # transaction_date = date(2020, 5, 25)
    # transactions = Transactions.query.filter_by(date_posted=transaction_date).all()

    #  ------------------------------------------------------------------------
    # 2.func.date()   相当于一个加工函数,将数据表的日期进行处理
    # func.date(Transactions.datetime_posted) 
    # transaction_date = date(2021, 8, 25)
    # transactions = Transactions.query.filter(func.date(Transactions.datetime_posted) == transaction_date).all()

    #  ------------------------------------------------------------------------
    # 3. fileter一般用来过滤比较复杂的东西,而filter_by 用来过滤比较直接简单明确的内容,筛选一段时间之内的汇报
    # (Transactions.date_posted.between(first_date, last_date))
    # first_date = date(2019, 6, 13)
    # last_date = date(2019, 6, 16)
    # transactions = Transactions.query.filter(Transactions.date_posted.between(first_date, last_date)).all()

    # first_date = date(2019, 6, 13)
    # last_date = date(2019, 6, 16)
    # transactions = Transactions.query.filter(Transactions.datetime_posted.between(first_date, last_date)).all()


    #  ------------------------------------------------------------------------
    # 一周之内
    #transactions = Transactions.query.filter(Transactions.date_posted > date.today() - timedelta(weeks=1)).all()

    # 一月之内
    #transactions = Transactions.query.filter(Transactions.datetime_posted > datetime.now() - timedelta(days=30)).all()


    #  ------------------------------------------------------------------------
    #  按照天数进行归类,将某一天的所有的回话的数量累加起来
    # transactions = db.session.query(Transactions.date_posted, func.sum(Transactions.amount)).group_by(Transactions.date_posted).all()
    # print(transactions)

    #  ------------------------------------------------------------------------
    # 统计某一年的会话数量
    # transactions = db.session.query(func.strftime('%Y', Transactions.date_posted), func.sum(Transactions.amount)).group_by(func.strftime('%Y', Transactions.date_posted)).all()
    # print(transactions)

    # 统计某年某月的回话数量
    transactions = db.session.query(func.strftime('%Y-%m', Transactions.date_posted), func.sum(Transactions.amount)).group_by(func.strftime('%Y-%m', Transactions.date_posted)).all()
    print(transactions)
    
    return render_template('index.html', transactions=transactions)

# db.create_all()

# fake = Faker()

# for _ in range(10):
#     transaction_date = fake.date_time_between(start_date='-3y')

#     db.session.add(
#         Transactions(
#             amount=fake.random_int(),
#             date_posted=transaction_date.date(),
#             datetime_posted=transaction_date
#         )
#     )

# db.session.commit()

if __name__ == '__main__':
    app.run(debug=True)  # 运行app```
# `/Users/song/codelearn/Querying With Dates in  Flask-SQLAlchemy/datequeries/filter_requirements.py`

```py

with open('./requirements.txt', "r", encoding='utf-8') as f:  # 打开文件
    lines = f.readlines()

new_lines = []
for line in lines:
    new_lines.append(line.split('==')[0])

with open('./requirements_new.txt', "w", encoding='utf-8') as f:  # 打开文件
    for line in new_lines:
        f.write(line)
        f.write('\n')
   

/Users/song/codelearn/Querying With Dates in Flask-SQLAlchemy/datequeries/templates/index.html

<h1>Transactions</h1>
<h3>Amount - Date Posted - Datetime Posted</h3>

{% for transaction in transactions %}
    <p>{{ transaction.amount }} - {{ transaction.date_posted }} - {{ transaction.datetime_posted }}</p>
{% endfor %}```