def get_most_recent_bookmark_created_at_datetime(connection) -> Optional[datetime]:
'''
Get created_at of most recent created bookmark or
else return None if one is not available
'''
logger.info("Fetching 'created_at' of most recent bookmark from database")
with DatabaseConnection.Cursor(connection) as cursor:
if cursor:
try:
sql_select_query = """SELECT created_at FROM bookmark ORDER BY created_at DESC LIMIT 1"""
cursor.execute(sql_select_query)
record = cursor.fetchone()
if record:
return record[0].replace(tzinfo=None)
except (Exception, psycopg2.Error) as error:
logger.error(
"Failed selecting record from bookmark table {}".format(error))
return None
def save_in_db(connection, bookmarks: List[Bookmark]) -> None:
'''
Save bookmarks, tags and bookmark tag mappings in database
'''
tag_records_set: Set[str] = set()
bookmark_records: List[Tuple[str, str, str, str, str]] = []
bookmark_tag_mapping_records: List[Tuple[str, str]] = []
len_bookmarks = len(bookmarks)
if len_bookmarks > 0:
logger.info(f"Saving {len_bookmarks} bookmarks in database")
else:
logger.info("No new bookmarks to save in database")
return
# convert models into tuple form to be used in sql execute statement
for bookmark in bookmarks:
# 200 is length of title column in database
bookmark_title = (
bookmark.title[:197] + '...') if len(bookmark.title) > 200 else bookmark.title
bookmark_records.append((bookmark._id, bookmark.created_at, bookmark.updated_at,
bookmark.link, bookmark_title))
bookmark_tag_mapping_records.extend(
[(mapping.bookmark_id, mapping.tag_id) for mapping in bookmark.mappings])
# use a set to make a list of non-duplicate tags
tag_records_set.update([tag._id for tag in bookmark.tags])
tag_records = [(tag,) for tag in tag_records_set]
with DatabaseConnection.Cursor(connection) as cursor:
try:
sql_bookmark_upsert_query = """
INSERT INTO bookmark (_id, created_at, updated_at, link, title)
VALUES (%s,%s,%s,%s,%s)
ON CONFLICT (_id) DO UPDATE SET updated_at = EXCLUDED.updated_at, link = EXCLUDED.link, title = EXCLUDED.title
"""
cursor.executemany(sql_bookmark_upsert_query, bookmark_records)
connection.commit()
except (Exception, psycopg2.Error) as error:
logger.error("Failed inserting records in bookmark table: {}".format(
error.__str__().strip()))
try:
sql_tag_insert_query = """
INSERT INTO tag (_id)
VALUES (%s)
ON CONFLICT (_id) DO NOTHING
"""
cursor.executemany(sql_tag_insert_query, tag_records)
connection.commit()
except (Exception, psycopg2.Error) as error:
logger.error("Failed inserting record in tag table: {}".format(
error.__str__().strip()))
try:
sql_tag_mapping_record_insert_query = """
INSERT INTO bookmark_tag_mapping (bookmark_id, tag_id)
VALUES (%s,%s)
ON CONFLICT (bookmark_id, tag_id) DO NOTHING
"""
cursor.executemany(
sql_tag_mapping_record_insert_query, bookmark_tag_mapping_records)
connection.commit()
except (Exception, psycopg2.Error) as error:
logger.error(
"Failed inserting records in bookmark tag mapping table: {}".format(error.__str__().strip()))
