def save_to_db(self):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
# run a select query against the table to see if any record exists that has the username
cursor.execute('SELECT name FROM user WHERE name=?', (self.name, ))
result = cursor.fetchone()
if result:
raise UserNameIsInUseError(
'This username is in use. Please select another one.')
else:
try:
#add user data
cursor.execute(
'INSERT INTO user (user_id, name, password_hash) VALUES (?, ?, ?)',
(self.user_id, self.name, self.password_hash))
session['name'] = self.name
session['user_id'] = self.user_id
session['url'] = 0
except:
# cursor.execute('CREATE TABLE user (user_id text PRIMARY KEY, name text, password_hash text)')
raise UserNotFoundError('The table `user` does not exist')
finally:
sqlite.close_connection(connection)
def find_latest_lesson_position(user_id):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
cursor.execute(
'SELECT * FROM user_activity u JOIN word w ON w.word_id = u.word_id WHERE user_id =? ORDER BY "timestamp" DESC LIMIT 1',
(user_id, ))
result = cursor.fetchone()
sqlite.close_connection(connection)
if result is not None:
return result[2], result[5]
return None
def update_timestamp(self):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
cursor.execute(
'UPDATE user_activity SET "timestamp" =? WHERE word_id =? AND user_id =? ',
(
int(round(time.time() * 1000)),
self.word_id,
self.user_id,
))
sqlite.close_connection(connection)
return
def get_latest_word_id(user_id):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by user_id
cursor.execute(
'SELECT * FROM user_activity WHERE user_id =? ORDER BY "timestamp" DESC LIMIT 1',
(user_id, ))
result = cursor.fetchone()
sqlite.close_connection(connection)
if result is not None:
return result[2]
return None
def is_duplicate(self):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by
cursor.execute(
'SELECT * FROM user_activity WHERE user_id =? AND word_id =?', (
self.user_id,
self.word_id,
))
result = cursor.fetchone()
sqlite.close_connection(connection)
return result is not None
def find_by_word(cls, word_id):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by lesson_id
cursor.execute('SELECT * FROM audio WHERE word_id =?', (word_id,))
result = cursor.fetchall()
sqlite.close_connection(connection)
if result is not None:
return [cls(row[1], row[2], row[0]) for row in result]
else:
print("The word_id does not exist")
return None
def get_by_user_id(cls, user_id):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by user_id
cursor.execute('SELECT * FROM user WHERE user_id =?', (user_id, ))
result = cursor.fetchone()
sqlite.close_connection(connection)
if result is not None:
return cls(result[1], result[2], result[0])
else:
print("The user_id does not exist")
return None
def get_by_username(cls, name):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by name
cursor.execute('SELECT * FROM user WHERE name =?', (name, ))
result = cursor.fetchone()
sqlite.close_connection(connection)
if result is not None:
return cls(result[1], result[2], result[0])
else:
print('This username does not exist')
return None
def user_words(user_id):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get by user_id
cursor.execute(
'SELECT * FROM user_activity u JOIN word w ON w.word_id = u.word_id WHERE user_id =? ORDER BY "timestamp" DESC',
(user_id, ))
result = cursor.fetchall()
print(result)
sqlite.close_connection(connection)
if result is not None:
return [row for row in result]
else:
print("No words were found")
return None
def get_words(cls):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#get all words
cursor.execute('SELECT * FROM word')
result = cursor.fetchall()
sqlite.close_connection(connection)
if result is not None:
return [
cls(row[1], row[2], row[3], row[4], row[0]) for row in result
]
else:
print("No words were found")
return None
def save_to_db(self):
#define connection and cursor
connection = sqlite.get_connection()
cursor = connection.cursor()
#add user data
try:
cursor.execute(
'INSERT INTO user_activity (word_id, user_id, timestamp, user_activity_id) VALUES (?, ?, ?, ?)',
(self.word_id, self.user_id, self.timestamp,
self.user_activity_id))
except:
cursor.execute(
'CREATE TABLE user_activity (user_activity_id text PRIMARY KEY, timestamp integer, word_id text, user_id text, FOREIGN KEY(word_id) REFERENCES word(word_id), FOREIGN KEY(user_id) REFERENCES user(user_id))'
)
raise UserActivityNotFoundError(
'The table `user_activity` did not exist, but it was created. Run the command again.'
)
finally:
sqlite.close_connection(connection)