def get_all_entries():
with sqlite3.connect("./dailyjournal.db") as con:
con.row_factory = sqlite3.Row
cursor = con.cursor()
cursor.execute("""
SELECT
e.id,
e.date,
e.topics,
e.entry,
e.moodId,
i.id instructor_id,
i.first_name,
i.last_name,
i.expertise,
m.id mood_id,
m.label
FROM Entries e
JOIN Instructors i ON i.id = e.instructorId
JOIN Moods m ON m.id = e.moodId
""")
entries = []
data = cursor.fetchall()
for row in data:
instructor = Instructor(row['instructor_id'],row['first_name'], row['last_name'], row['expertise']).__dict__
mood = Mood(row['mood_id'], row['label']).__dict__
entries.append(Entry(row['id'], row['date'], row['topics'], row['entry'], mood, instructor).__dict__)
return json.dumps(entries)
def get_single_entry(id):
with sqlite3.connect("dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
e.id,
e.date,
e.concept,
e.entry,
e.mood_id,
e.instructor_id,
m.label
FROM entries e
JOIN moods m
ON e.mood_id = m.id
WHERE e.id = ?
""", (id, ))
data = db_cursor.fetchone()
entry = Entry(data["id"], data["date"], data["concept"], data["entry"],
data["mood_id"], data["instructor_id"])
mood = Mood(data["mood_id"], data["label"])
entry.mood = mood.__dict__
return json.dumps(entry.__dict__)
def get_single_entry(id):
with sqlite3.connect("./dailyjournal.db") as con:
con.row_factory = sqlite3.Row
c = con.cursor()
c.execute('''
SELECT
e.id,
e.date,
e.topics,
e.entry,
e.moodId,
i.id instructor_id,
i.first_name,
i.last_name,
i.expertise,
m.id mood_id,
m.label
FROM Entries e
JOIN Instructors i ON i.id = e.instructorId
JOIN Moods m ON m.id = e.moodId
WHERE e.id = ?
''', (id, ))
row = c.fetchone()
instructor = Instructor(row['instructor_id'],row['first_name'], row['last_name'], row['expertise']).__dict__
mood = Mood(row['mood_id'], row['label']).__dict__
entry = Entry(row['id'], row['date'], row['topics'], row['entry'], mood, instructor).__dict__
return json.dumps(entry)
def get_all_moods():
# Open connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# SQL query to get the moods data
db_cursor.execute("""
SELECT
m.id,
m.label
FROM Mood m
""")
# Initialized empty list to hold moods
moods = []
# Convert rows of data to Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
mood = Mood(row['id'], row['label'])
moods.append(mood.__dict__)
return json.dumps(moods)
def get_all_moods():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
m.id,
m.label
FROM Moods m
""")
# Initialize an empty list to hold all mood representations
moods = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create a Mood instance from the current row
mood = Mood(
row['id'], #Doesn't seem to run without its 3 positional arguments but this returns only 1 animal
row['label'])
moods.append(mood.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(moods)
def get_all_moods():
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
m.id,
m.label
FROM Moods m
""")
moods = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
mood = Mood(row['id'], row['label'])
moods.append(mood.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(moods)
def get_single_entry(id):
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Use a ? parameter to inject a variable's value
# into the SQL statement.
db_cursor.execute("""
SELECT
e.id,
e.date,
e.topic,
e.journal_entry,
e.mood_id,
m.mood
FROM Journal_Entry e
JOIN Mood m
ON m.id = e.mood_id
WHERE e.id = ?
""", ( id, ))
# Load the single result into memory
# we define data and that is why we pass data in our entry = Entry section below
data = db_cursor.fetchone()
# Create an entry instance from the current row
# sets up init and passes in all the paramaters
entry = Journal_Entry(data['id'], data['date'], data['topic'],
data['journal_entry'], data['mood_id'])
mood = Mood(data['id'], data['mood'] )
entry.mood = mood.__dict__
return json.dumps(entry.__dict__)
def get_all_entries():
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.mood_id,
m.id moodId,
m.label
FROM entries e
JOIN Mood m ON e.mood_id = m.id
""")
entries = []
dataset = db_cursor.fetchall()
for row in dataset:
entry = Entry(row['id'], row['concept'], row['entry'], row['date'],
row['mood_id'])
mood = Mood(row['moodId'], row['label'])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
return json.dumps(entries)
def get_all_moods():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
m.id,
m.label
FROM mood m
""")
# Initialize an empty list to hold all animal representations
moods = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an mood instance from the current row.
# Note that the database fields are specified in
# exact order of the parameters defined in the
# Mood class above.
mood = Mood(row['id'], row['label'])
moods.append(mood.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(moods)
def get_single_entry(id):
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Use a ? parameter to inject a variable's value
# into the SQL statement.
db_cursor.execute(
"""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.moodId,
m.label
FROM entry e
JOIN mood m
ON m.id = e.moodId
WHERE e.id = ?
""", (id, ))
# Load the single result into memory
data = db_cursor.fetchone()
# Create an entry instance from the current row
entry = Entry(data['id'], data['concept'], data['entry'], data['date'],
data['moodId'])
mood = Mood(data['moodId'], data['label'])
entry.mood = mood.__dict__
return json.dumps(entry.__dict__)
def get_single_entry(id):
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.mood_id,
m.id moodId,
m.label
FROM entries e
JOIN Mood m ON e.mood_id = m.id
WHERE e.id = ?
""", (id, ))
data = db_cursor.fetchone()
entry = Entry(data['id'], data['concept'], data['entry'], data['date'],
data['mood_id'])
mood = Mood(data['moodId'], data['label'])
entry.mood = mood.__dict__
return json.dumps(entry.__dict__)
def __build_expanded_entry_from_row(self, row):
entry = Entry(row['id'], row['date'], row['entry'], row['moodId'])
mood = Mood(row['moodId'], row['mood_value'], row['mood_label'])
entry.mood = mood.__dict__
return entry.__dict__
def get_all_entries():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.mood_id,
m.label mood_label
FROM Entry e
JOIN Mood m
ON m.id = e.mood_id
""")
# Initialize an empty list to hold all entry representations
entries = []
# returns everything that matches the query
# Convert rows of data into a Python list
# fetchall() returning a easier version of the rows that come back
# appending entry dictionary to entry list
# fetchall() fetches all the data
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an entry instance from the current row.
# The database fields are specified in
# exact order of the parameters defined in the Entry class.
# use bracket notation to get the value of the keys, pass in parameters for class
entry = Entry(row['id'], row['concept'], row['entry'], row['date'],
row['mood_id'])
# Create a Mood instance from the current row
mood = Mood(row['mood_id'], row['mood_label'])
# .__dict__ : is a dictionary or other mapping object used to store an object’s (writable) attributes.
# Add the dictionary representation of the mood and customer to the animal
entry.mood = mood.__dict__
# Adds the dictionary representation of the entry to the list
entries.append(entry.__dict__)
# Use `json` package to properly serialize list as JSON
# pass dumps a list of dictionaries
return json.dumps(entries)
def get_all_entries(value=""):
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
if len(value) > 0:
db_cursor.execute(
"""
SELECT
e.id,
e.date,
e.entry,
e.mood_id,
e.concepts,
m.label
FROM entries e
JOIN Moods m ON e.mood_id = m.id
WHERE e.entry LIKE ?
""", ('%' + value + '%', ))
else:
db_cursor.execute("""
SELECT
e.id,
e.date,
e.entry,
e.mood_id,
e.concepts,
m.label
FROM entries e
JOIN Moods m ON e.mood_id = m.id
""")
# Initialize an empty list to hold all location representations
entries = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
entry = Entry(row['id'], row['date'], row['entry'], row['mood_id'],
row['concepts'])
mood = Mood("", row['label'])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(entries)
def agregarMood(idPersona, happy, sad, neutro):
try:
query=Mood(
idPersona=idPersona,
happy=happy,
sad=sad,
neutro=neutro
)
db.session.add(query)
db.session.commit()
return "mood added. mood id={}".format(query.id)
except Exception as e:
return(str(e))
def _get_all(self, cursor):
cursor.execute("""
SELECT
m.id,
m.label,
m.value
FROM Moods m
""")
results = cursor.fetchall()
moods = [ (Mood(**mood)).__dict__ for mood in results ]
return moods
def get_all_moods():
with sqlite3.connect("./dailyjournal.db") as con:
con.row_factory = sqlite3.Row
c = con.cursor()
c.execute("""
SELECT
m.id,
m.label
FROM Moods m
""")
moods = []
dataset = c.fetchall()
for row in dataset:
moods.append(Mood(row['id'], row['label']).__dict__)
return json.dumps(moods)
def get_all_entries():
with sqlite3.connect("./db/dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
a.id,
a.concept,
a.entry,
a.date,
a.moodId,
m.label mood_label
FROM entries a
JOIN moods m ON a.moodId = m.id
""")
entries = []
dataset = db_cursor.fetchall()
for row in dataset:
entry = Entry(row['id'], row['concept'], row['entry'], row['date'],
row['moodId'])
mood = Mood(row['moodId'], row['mood_label'])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
db_cursor.execute(
"""
SELECT
t.id,
t.name,
e.entry_id
FROM entry_tag e
JOIN tags t ON t.id = e.tag_id
WHERE e.entry_id = ?
""", (row['id'], ))
tagset = db_cursor.fetchall()
tags = []
for tag in tagset:
each_tag = Tag(tag['id'], tag['name'])
tags.append(each_tag.__dict__)
entry.tags = tags
return json.dumps(entries)
def get_all_entries():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
a.id,
a.date,
a.concept,
a.entry,
a.mood_id,
a.instructor_id,
m.id mood_id,
m.label
FROM entries a
JOIN moods m on m.id = a.mood_id
""")
# Initialize an empty list to hold all entry representations
entries = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an entry instance from the current row.
# Note that the database fields are specified in
# exact order of the parameters defined in the
# entry class above.
entry = Entry(row['id'], row['date'], row['concept'],
row['entry'], row['mood_id'],
row['instructor_id'])
mood = Mood(row['id'], row['label'])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(entries)
def get_all_entries():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.mood_id,
m.label mood_label
FROM Entries e
JOIN Moods m
ON m.id = e.mood_id
""")
# Initialize an empty list to hold all entry representations
entries = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an entry instance from the current row
entry = Entry(row['id'], row['concept'], row['entry'], row['date'],
row['mood_id'])
# Create a Mood instance from the current row
mood = Mood(row['mood_id'], row['mood_label'])
# Add the dictionary representation of the mood to the animal
entry.mood = mood.__dict__
# Add the dictionary representation of the entry to the list
entries.append(entry.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(entries)
def get_single_mood(id):
with sqlite3.connect("./db/dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
m.id,
m.label
FROM moods m
WHERE m.id = ?
""", (id, ))
data = db_cursor.fetchone()
mood = Mood(data['id'], data['label'])
return json.dumps(mood.__dict__)
def get_single_entry(id):
with sqlite3.connect('../../dailyjournal.db') as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.moodId,
m.label
FROM entries e
JOIN moods m
ON e.moodId = m.id
WHERE e.id = ?
""", (id, ))
entries = []
dataset = db_cursor.fetchall()
for row in dataset:
entry = Entry(row['id'], row['concept'],
row['entry'], row['date'], row['moodId'])
mood = Mood(row['moodId'], row['label'])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
return json.dumps(entries)
def delete_entry(id):
with sqlite3.connect('../../dailyjournal.db') as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
DELETE FROM entries
WHERE id = ?
""", (id, ))
def get_all_moods():
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.mood
FROM moods e
""")
entries = []
dataset = db_cursor.fetchall()
for row in dataset:
mood = Mood(row['id'], row['mood'])
entries.append(mood.__dict__)
return json.dumps(entries)
def get_all_entries():
with sqlite3.connect("dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT DISTINCT
e.id,
e.date,
e.concept,
e.entry,
e.mood_id,
e.instructor_id,
m.label,
i.first_name,
i.last_name
FROM entries e
JOIN moods m
ON m.id = e.mood_id
LEFT JOIN instructors i
ON i.id = e.instructor_id
""")
entries = []
dataset = db_cursor.fetchall()
for row in dataset:
entry = Entry(row["id"], row["date"], row["concept"], row["entry"],
row["mood_id"], row["instructor_id"])
mood = Mood(row["mood_id"], row["label"])
entry.mood = mood.__dict__
instructor = Instructor(row["instructor_id"], row["first_name"],
row["last_name"])
entry.instructor = instructor.__dict__
entries.append(entry.__dict__)
return json.dumps(entries)
def get_all_moods():
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
m.id,
m.name
FROM Moods m
""")
moods = []
dataset = db_cursor.fetchall()
for row in dataset:
mood = Mood(row['id'], row['name'])
moods.append(mood.__dict__)
return json.dumps(moods)
def get_single_entry(id):
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Use a ? parameter to inject a variable's value
# into the SQL statement.
# tables to grab FROM
db_cursor.execute(
"""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.mood_id,
m.label mood_label
FROM Entry e
JOIN Mood m
ON m.id = e.mood_id
WHERE e.id = ?
""", (id, ))
# Load the single result into memory
data = db_cursor.fetchone()
# Create an entry instance from the current row
entry = Entry(data['id'], data['concept'], data['entry'], data['date'],
data['mood_id'])
# Create a mood instance from the current row
mood = Mood(data['mood_id'], data['mood_label'])
# .__dict__ : is a dictionary or other mapping object used to store an object’s (writable) attributes.
# Add the dictionary representation of the location to the entry
entry.mood = mood.__dict__
return json.dumps(entry.__dict__)
def get_entries_by_word(word):
with sqlite3.connect("dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
e.id,
e.date,
e.concept,
e.entry,
e.mood_id,
e.instructor_id,
m.label
FROM entries e
JOIN moods m
ON e.mood_id = m.id
WHERE e.entry LIKE '%'||?||'%'
""", (word, ))
dataset = db_cursor.fetchall()
entries = []
for row in dataset:
entry = Entry(row["id"], row["date"], row["concept"], row["entry"],
row["mood_id"], row["instructor_id"])
mood = Mood(row["mood_id"], row["label"])
entry.mood = mood.__dict__
entries.append(entry.__dict__)
return json.dumps(entries)
def get_single_mood(id):
with sqlite3.connect("./dailyjournal.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Use a ? parameter to inject a variable's value
# into the SQL statement.
db_cursor.execute(
"""
SELECT
m.id,
m.label
FROM mood m
WHERE m.id = ?
""", (id, ))
# Load the single result into memory
data = db_cursor.fetchone()
# Create an mood instance from the current row
mood = Mood(data['id'], data['label'])
return json.dumps(mood.__dict__)
def get_single_mood(id):
with sqlite3.connect('../../dailyjournal.db') as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
m.id,
m.label
FROM moods m
WHERE m.id = ?
""", (id, ))
moods = []
dataset = db_cursor.fetchall()
for row in dataset:
mood = Mood(row['id'], row['label'])
moods.append(mood.__dict__)
return json.dumps(moods)
def get_all_entries():
# Open a connection to the database
with sqlite3.connect("./dailyjournal.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
e.id,
e.concept,
e.entry,
e.date,
e.moodId,
m.label
FROM entry e
JOIN mood m
ON m.id = e.moodId
""")
# Initialize an empty list to hold all entry representations
entries = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an entry instance from the current row.
# Note that the database fields are specified in
# exact order of the parameters defined in the
# Entry class above.
entry = Entry(row['id'], row['concept'], row['entry'], row['date'],
row['moodId'])
mood = Mood(row['moodId'], row['label'])
entry.mood = mood.__dict__
db_cursor.execute(
"""
SELECT
t.id,
t.name
FROM Entry_Tag et
JOIN Tag t
ON t.id = et.tag_id
WHERE et.entry_id = ?
""", (entry.id, ))
tag_data = db_cursor.fetchall()
tags = []
for tag in tag_data:
tag = Tag(tag['id'], tag['name'])
tags.append(tag.__dict__)
entry.tags = tags
entries.append(entry.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(entries)
