def get_todays_entry(db_path):
"""Get the entry from today.
Args:
db_path (str): Path to our database.
Returns:
list(Entry): List of entries that were entered today.
"""
with dbopen(db_path) as db:
todays_entries = db.execute("SELECT rowid, content, mood, sleep, date " \
"FROM entries WHERE date = ?", (datetime.now().date(),)).fetchall()
entries = []
if not todays_entries:
logger.info("You have not added an entry for today yet.")
elif len(todays_entries) > 1:
logger.warning("You somehow have more than one entry today.")
for entry in todays_entries:
entry = EntryTuple._make(entry)
activities = get_activities_from_entry_id(entry.rowid, db_path)
entries.append(
Entry(entry.content, entry.mood, activities, entry.sleep,
entry.date))
else:
entry = EntryTuple._make(todays_entries[0])
activities = get_activities_from_entry_id(entry.rowid, db_path)
entries.append(
Entry(entry.content, entry.mood, activities, entry.sleep,
entry.date))
return entries
def average_mood_per_activity(db_path):
"""Calculate the average mood based on each activity you have done
Args:
db_path (str): Path to our database.
"""
with dbopen(db_path) as db:
all_activities = db.execute(
"SELECT activity FROM activities").fetchall()
activity_to_list_of_moods = {
} # Dict of activity that maps to a list of moods out of 10
for activity in all_activities:
entry_ids = db.execute("""SELECT entry_id FROM entry_activities
INNER JOIN activities ON entry_activities.activity_id = activities.rowid
WHERE activity='{}'""".format(
activity[0])).fetchall()
activity_to_list_of_moods[activity[0]] = \
[db.execute("SELECT mood FROM entries WHERE rowid = {}" \
.format(entry_id[0])).fetchall()[0][0] for entry_id in entry_ids]
avgs = {}
for activity, moods in activity_to_list_of_moods.items():
avgs[activity] = round(sum(moods) / len(moods), 2)
logger.info("\nAVERAGE MOOD BY ACTIVITY")
logger.info(
tabulate(sorted(avgs.items(), key=lambda x: x[1], reverse=True),
headers=["Activity", "Avg Mood"]))
def update_database_to_new_version(db_path):
"""Update our database to the latest version.
Will just add any missing columns to our database.
Args:
db_path (str): Path to our database.
"""
with dbopen(db_path) as db:
missing_columns = set(ENTRY_COLUMNS.keys()) - \
{x[1] for x in db.execute("PRAGMA table_info(entries)").fetchall()}
for column_name in missing_columns:
db.execute("ALTER TABLE entries ADD {} {}".format(
column_name, ENTRY_COLUMNS[column_name]))
def get_activities_from_entry_id(entry_id, db_path):
"""Get all activities with entry id entry_id.
Args:
entry_id (int): entry_id of the entry we want to find.
db_path (str): Path to our database.
Returns:
list(str): List of activities we did on the specified entry.
"""
with dbopen(db_path) as db:
activities = [x[0] for x in db.execute("SELECT activity FROM entry_activities " \
"INNER JOIN activities ON entry_activities.activity_id = activities.rowid " \
"WHERE entry_id=?", (entry_id,)).fetchall()]
return activities
def get_all_entries(db_path):
"""Get all entries from our database
Args:
db_path (str): Path to our database.
Returns:
list(Entry): List of all entries found in our database.
"""
logger.debug("Accessing DB %s", db_path)
with dbopen(db_path) as db:
entries = []
for entry in db.execute(
"SELECT rowid, content, mood, sleep, date FROM entries"
).fetchall():
entry = EntryTuple._make(entry)
activities = get_activities_from_entry_id(entry[0], db_path)
entries.append(
Entry(entry.content, entry.mood, activities, entry.sleep,
entry.date))
return entries
def save_to_database(self, db_path):
"""Save this object to the database
Args:
db_path (str): Path to our database.
Return:
bool: True if we added to database
"""
with dbopen(db_path) as db:
if db.execute("SELECT * FROM ENTRIES WHERE date=?",
(self.date, )).fetchall():
ans = input(
f"You already have an entry for {self.date}. Do you want to replace it [y/n]? "
)
if ans not in ['y', 'Y']:
print("Not adding new entry")
return False
db.execute("DELETE FROM ENTRIES WHERE date=?", (self.date, ))
db.execute("INSERT INTO entries(content, mood, sleep, date) " \
"VALUES(?, ?, ?, ?)", (self.content, self.mood, self.sleep, self.date))
entry_id = db.lastrowid
for activity in self.activities:
activity_ids = db.execute(
"SELECT rowid FROM activities WHERE activity = ?",
(activity, )).fetchall()
if not activity_ids:
# We do not have this tag in our db yet
db.execute("INSERT INTO activities(activity) VALUES(?)",
(activity, ))
activity_id = db.lastrowid
elif len(activity_ids) == 1:
activity_id = activity_ids[0][0]
else:
logger.warning("Found more than one activity of %s",
activity)
return
db.execute(
"INSERT INTO entry_activities(entry_id, activity_id) VALUES(?, ?)",
(entry_id, activity_id))
return True
def get_entries_by_dates(dates, db_path):
"""Get all entries that were posted on a date in dates
Args:
dates (list(str)): List of dates in format yyyy-mm-dd.
db_path (str): Path to our database.
Returns:
list(Entry): List of entries posted on a date in dates.
"""
entries = []
with dbopen(db_path) as db:
for date in dates:
get_entries_query = "SELECT rowid, content, mood, sleep, date FROM 'entries' WHERE date = '{}'" \
.format(date.strip())
for entry in db.execute(get_entries_query).fetchall():
entry = EntryTuple._make(entry)
activities = get_activities_from_entry_id(entry[0], db_path)
entries.append(
Entry(entry.content, entry.mood, activities, entry.sleep,
entry.date))
return entries
def average_mood_per_day(db_path):
"""Calculate the average mood based on the day of the week.
Args:
db_path (str): Path to our database.
"""
days = [
"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
"Saturday"
]
with dbopen(db_path) as db:
day_to_list_of_moods = defaultdict(list)
for entry in db.execute("SELECT mood, date FROM entries").fetchall():
# By default datetime.weekday() has 0 = monday so to have 0 = sunday we add 1 and modulo 7
day_to_list_of_moods[
days[(datetime.strptime(entry[1], "%Y-%m-%d").weekday() + 1) %
7]].append(entry[0])
avgs = {}
for day, moods in day_to_list_of_moods.items():
avgs[day] = round(sum(moods) / len(moods), 2)
logger.info("\nAVERAGE MOOD BY DAY OF WEEK")
logger.info(tabulate(avgs.items(), headers=["Day", "Avg Mood"]))
def set_up_db(db_path):
"""Create tables in database if they do not exist yet
Args:
db_path (str): Path to our database.
"""
create_entries = "CREATE TABLE IF NOT EXISTS " \
"entries(rowid INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, mood INTEGER NOT NULL, sleep INTEGER, date TEXT NOT NULL)"
create_activities = "CREATE TABLE IF NOT EXISTS activities(rowid INTEGER PRIMARY KEY AUTOINCREMENT, activity TEXT NOT NULL)"
create_entry_activities = "CREATE TABLE IF NOT EXISTS " \
"entry_activities(entry_id INTEGER, activity_id INTEGER, " \
"FOREIGN KEY(entry_id) REFERENCES entries(rowid) ON DELETE CASCADE, " \
"FOREIGN KEY(activity_id) REFERENCES activities(rowid) ON DELETE CASCADE)"
create_statements = [
create_entries, create_activities, create_entry_activities
]
if not os.path.exists(db_path):
if not os.path.exists(os.path.dirname(db_path)):
os.makedirs(os.path.dirname(db_path))
logger.debug("Writing %s", db_path)
with dbopen(db_path) as db:
for create_statement in create_statements:
db.execute(create_statement)
def get_entries_by_activity(activity, db_path):
"""Get all entries that we did an activity.
Args:
activity (str): Activity we want to search for.
db_path (str): Path to our database.
Returns:
list(Entry): List of entries that we did this activity.
"""
entries = []
with dbopen(db_path) as db:
get_entries_query = """SELECT entry_id, content, mood, sleep, date FROM 'entries'
INNER JOIN entry_activities ON entries.rowid = entry_activities.entry_id
INNER JOIN activities ON activity_id = activities.rowid
WHERE activity = '{}'
""".format(activity)
for entry in db.execute(get_entries_query).fetchall():
entry = EntryTuple._make(entry)
activities = get_activities_from_entry_id(entry.rowid, db_path)
entries.append(
Entry(entry.content, entry.mood, activities, entry.sleep,
entry.date))
return entries
def delete_from_database(db_path, date):
with dbopen(db_path) as db:
db.execute(f"DELETE FROM entries WHERE date = '{date}'")
print(f"Deleted {db.rowcount} entries")