LoginSignup
1
0

More than 3 years have passed since last update.

@su-pleiadesinもくもくOnline勉強会

SQLAlchemy(Python)でMySQLのカラムdatetimeから日付範囲のデータを取得する方法

Posted at

目的

  • SQLAlchemy(Python)を活用してMySQLのテーブルの日付範囲からデータを取得する

経緯

  • 自身が運営する「学び続ける人が集まる」勉強コミュニティで勉強記録（入退室）をDiscordBOT経由でcsv形式で記録していたものをDBを使う際ORMとしてSQLAlchemyを選択肢、週や月間の勉強集計をする際に日付で勉強記録を取得する必要があった
  • 勉強コミュニティ「もくもくオンライン勉強会」の紹介

方法

  • 以下では、「今週月曜日」〜「今日」までの日付で記録を取得したもの
  • 自作パッケージを使っているので、細かい所気になる方は以下のURLから確認ください 
from datetime import date, datetime, timedelta
from sqlalchemy import func as F, extract, and_

from mo9mo9db.dbtables import Studytimelogs


today = date.today()
# 今週月曜日のdatetime
startrange_dt = today - timedelta(days=today.weekday()) + timedelta(days=0)
endrange_dt = today
session = Studytimelogs.session()


obj = session.query(Studytimelogs.study_dt).filter(
    and_(extract('year', Studytimelogs.study_dt) == startrange_dt.year, 
         extract('month', Studytimelogs.study_dt) == startrange_dt.month, 
         extract('day', Studytimelogs.study_dt) >= startrange_dt.day),
    and_(extract('year', Studytimelogs.study_dt) == endrange_dt.year, 
         extract('month', Studytimelogs.study_dt) == endrange_dt.month, 
         extract('day', Studytimelogs.study_dt) <= endrange_dt.day)).all()


_= [print(i) for i in obj ]
# ...
# (datetime.datetime(2021, 4, 5, 23, 50, 25),)
# (datetime.datetime(2021, 4, 5, 23, 56, 54),)
# (datetime.datetime(2021, 4, 5, 23, 57, 24),)
# (datetime.datetime(2021, 4, 6, 0, 41, 28),)
# (datetime.datetime(2021, 4, 5, 23, 59, 59),)
# (datetime.datetime(2021, 4, 6, 1, 14, 12),)
# (datetime.datetime(2021, 4, 5, 23, 59, 59),)
# (datetime.datetime(2021, 4, 6, 1, 14, 15),)
# (datetime.datetime(2021, 4, 6, 1, 14, 33),)
# (datetime.datetime(2021, 4, 6, 1, 14, 35),)
# (datetime.datetime(2021, 4, 6, 1, 26, 51),)
# (datetime.datetime(2021, 4, 5, 23, 59, 59),)
# (datetime.datetime(2021, 4, 6, 6, 32, 35),)
# (datetime.datetime(2021, 4, 6, 6, 54, 50),)
# (datetime.datetime(2021, 4, 6, 6, 59, 15),)
# (datetime.datetime(2021, 4, 6, 8, 8, 17),)
# (datetime.datetime(2021, 4, 6, 8, 11, 37),)
# (datetime.datetime(2021, 4, 6, 8, 16, 59),)
# (datetime.datetime(2021, 4, 6, 8, 26, 23),)
# (datetime.datetime(2021, 4, 6, 8, 29, 7),)
# (datetime.datetime(2021, 4, 6, 8, 38, 42),)
# (datetime.datetime(2021, 4, 6, 8, 43, 10),)
# (datetime.datetime(2021, 4, 6, 8, 48, 44),)
# (datetime.datetime(2021, 4, 6, 8, 57, 9),)
# (datetime.datetime(2021, 4, 6, 9, 22, 16),)
# (datetime.datetime(2021, 4, 6, 9, 33, 14),)
# (datetime.datetime(2021, 4, 6, 9, 48, 40),)
# (datetime.datetime(2021, 4, 6, 9, 48, 48),)
# (datetime.datetime(2021, 4, 6, 9, 48, 52),)
# (datetime.datetime(2021, 4, 6, 9, 48, 55),)
# (datetime.datetime(2021, 4, 6, 9, 52, 1),)
# (datetime.datetime(2021, 4, 6, 9, 53, 29),)
# (datetime.datetime(2021, 4, 6, 9, 55, 54),)
# (datetime.datetime(2021, 4, 6, 9, 59, 32),)
# (datetime.datetime(2021, 4, 6, 10, 1, 55),)
# (datetime.datetime(2021, 4, 6, 10, 50, 29),)
# (datetime.datetime(2021, 4, 6, 10, 55, 8),)
# (datetime.datetime(2021, 4, 6, 11, 1, 47),)
# (datetime.datetime(2021, 4, 6, 11, 12, 46),)
# (datetime.datetime(2021, 4, 6, 11, 30, 15),)
# (datetime.datetime(2021, 4, 6, 12, 3, 32),)
# (datetime.datetime(2021, 4, 6, 12, 5, 54),)
# (datetime.datetime(2021, 4, 6, 12, 6, 30),)
# (datetime.datetime(2021, 4, 6, 12, 8, 51),)
# (datetime.datetime(2021, 4, 6, 12, 31, 47),)
# (datetime.datetime(2021, 4, 6, 12, 32, 42),)
# (datetime.datetime(2021, 4, 6, 12, 33, 1),)
# (datetime.datetime(2021, 4, 6, 12, 33, 1),)
# (datetime.datetime(2021, 4, 6, 12, 45),)
# (datetime.datetime(2021, 4, 6, 12, 46, 38),)
# (datetime.datetime(2021, 4, 6, 13, 0, 11),)
# (datetime.datetime(2021, 4, 6, 13, 10, 27),)
# (datetime.datetime(2021, 4, 6, 13, 47, 42),)
# (datetime.datetime(2021, 4, 6, 13, 51, 58),)
# (datetime.datetime(2021, 4, 6, 13, 55, 42),)
# (datetime.datetime(2021, 4, 6, 13, 55, 46),)
# (datetime.datetime(2021, 4, 6, 13, 56, 49),)
# (datetime.datetime(2021, 4, 6, 14, 6, 17),)
# (datetime.datetime(2021, 4, 6, 14, 7, 32),)
# (datetime.datetime(2021, 4, 6, 14, 12, 43),)
# (datetime.datetime(2021, 4, 6, 14, 38, 15),)
# (datetime.datetime(2021, 4, 6, 14, 42, 37),)
# (datetime.datetime(2021, 4, 6, 14, 48, 58),)
# (datetime.datetime(2021, 4, 6, 14, 55, 37),)
# (datetime.datetime(2021, 4, 6, 15, 2, 17),)
# (datetime.datetime(2021, 4, 6, 16, 0, 7),)
# (datetime.datetime(2021, 4, 6, 16, 3, 9),)
# (datetime.datetime(2021, 4, 6, 16, 7, 55),)
# (datetime.datetime(2021, 4, 6, 16, 13, 25),)
# (datetime.datetime(2021, 4, 6, 16, 18, 20),)
# (datetime.datetime(2021, 4, 6, 16, 23, 28),)
# (datetime.datetime(2021, 4, 6, 16, 56, 21),)
# (datetime.datetime(2021, 4, 6, 17, 0, 12),)
# (datetime.datetime(2021, 4, 6, 17, 5, 54),)
# (datetime.datetime(2021, 4, 6, 17, 7, 52),)
# (datetime.datetime(2021, 4, 6, 17, 9, 35),)
# (datetime.datetime(2021, 4, 6, 17, 19, 24),)
# (datetime.datetime(2021, 4, 6, 17, 29, 25),)
# (datetime.datetime(2021, 4, 6, 17, 30, 32),)
# (datetime.datetime(2021, 4, 6, 17, 33, 46),)
# (datetime.datetime(2021, 4, 6, 17, 42, 12),)
# (datetime.datetime(2021, 4, 6, 17, 50),)
# (datetime.datetime(2021, 4, 6, 18, 53, 2),)
# (datetime.datetime(2021, 4, 6, 19, 36, 24),)
# (datetime.datetime(2021, 4, 6, 19, 42, 35),)
# (datetime.datetime(2021, 4, 6, 19, 44, 47),)
# (datetime.datetime(2021, 4, 6, 19, 44, 49),)
# (datetime.datetime(2021, 4, 6, 19, 44, 53),)
# (datetime.datetime(2021, 4, 6, 19, 44, 54),)
# (datetime.datetime(2021, 4, 6, 19, 50, 23),)
# (datetime.datetime(2021, 4, 6, 20, 5, 31),)
# (datetime.datetime(2021, 4, 6, 20, 5, 57),)
# (datetime.datetime(2021, 4, 6, 20, 19, 8),)
# (datetime.datetime(2021, 4, 6, 20, 48, 47),)
# (datetime.datetime(2021, 4, 6, 21, 6, 21),)
# (datetime.datetime(2021, 4, 6, 21, 19, 32),)
# (datetime.datetime(2021, 4, 6, 21, 23, 55),)
# (datetime.datetime(2021, 4, 6, 21, 50, 43),)
# (datetime.datetime(2021, 4, 6, 22, 3, 16),)
# (datetime.datetime(2021, 4, 6, 22, 7, 16),)
# (datetime.datetime(2021, 4, 6, 22, 14, 57),)
# (datetime.datetime(2021, 4, 6, 22, 37, 23),)
# (datetime.datetime(2021, 4, 6, 22, 48, 11),)
# (datetime.datetime(2021, 4, 6, 22, 56, 8),)
# (datetime.datetime(2021, 4, 6, 22, 57),)
# (datetime.datetime(2021, 4, 6, 23, 6),)
# (datetime.datetime(2021, 4, 6, 23, 29, 51),)
# (datetime.datetime(2021, 4, 6, 23, 53, 8),)
# (datetime.datetime(2021, 4, 7, 2, 19, 53),)
# (datetime.datetime(2021, 4, 6, 23, 59, 59),)
# (datetime.datetime(2021, 4, 7, 6, 29, 42),)
# (datetime.datetime(2021, 4, 7, 6, 30, 7),)
# (datetime.datetime(2021, 4, 7, 6, 30, 14),)
# (datetime.datetime(2021, 4, 7, 6, 30, 52),)
# (datetime.datetime(2021, 4, 7, 6, 31, 14),)
# (datetime.datetime(2021, 4, 7, 8, 3, 26),)
# (datetime.datetime(2021, 4, 7, 8, 33, 46),)
# (datetime.datetime(2021, 4, 7, 8, 39, 26),)
# (datetime.datetime(2021, 4, 7, 8, 42, 4),)
# (datetime.datetime(2021, 4, 7, 9, 0, 15),)
# (datetime.datetime(2021, 4, 7, 9, 20, 19),)
# (datetime.datetime(2021, 4, 7, 9, 35, 6),)
# (datetime.datetime(2021, 4, 7, 9, 35, 8),)
# (datetime.datetime(2021, 4, 7, 9, 47, 18),)
# (datetime.datetime(2021, 4, 7, 9, 48, 43),)
# (datetime.datetime(2021, 4, 7, 10, 4, 34),)
# (datetime.datetime(2021, 4, 7, 10, 42, 8),)
# (datetime.datetime(2021, 4, 7, 10, 51, 29),)
# (datetime.datetime(2021, 4, 7, 11, 1, 33),)
# (datetime.datetime(2021, 4, 7, 11, 9, 2),)
# (datetime.datetime(2021, 4, 7, 11, 25, 41),)
# (datetime.datetime(2021, 4, 7, 11, 34, 10),)
# (datetime.datetime(2021, 4, 7, 11, 47, 20),)
# (datetime.datetime(2021, 4, 7, 12, 0, 59),)
# (datetime.datetime(2021, 4, 7, 12, 26, 28),)
# (datetime.datetime(2021, 4, 7, 12, 42, 35),)
# (datetime.datetime(2021, 4, 7, 13, 0, 43),)
# (datetime.datetime(2021, 4, 7, 13, 26, 20),)
# (datetime.datetime(2021, 4, 7, 13, 27, 12),)
# (datetime.datetime(2021, 4, 7, 13, 49, 51),)
# (datetime.datetime(2021, 4, 7, 14, 41, 48),)
# (datetime.datetime(2021, 4, 7, 14, 54, 21),)
# (datetime.datetime(2021, 4, 7, 14, 55, 55),)
# (datetime.datetime(2021, 4, 7, 14, 59, 59),)
# (datetime.datetime(2021, 4, 7, 15, 11, 33),)
# (datetime.datetime(2021, 4, 7, 15, 39, 44),)
# (datetime.datetime(2021, 4, 7, 16, 3),)
# (datetime.datetime(2021, 4, 7, 16, 21),)
# ...

お願い

  • よりスマートな方法があれば是非コメントで教えていただきたいです。
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0