def create_db_table():
try:
connection = db_connect.connect_to_db()
cursor = connection.cursor()
cursor.execute(TABLE_SQL)
cursor.close()
connection.commit()
except (Exception, Error) as error:
print(f'Failed to create table: {error}')
finally:
db_connect.close_db(connection)
def update_last_sent(id):
connection = db_connect.connect_to_db()
cursor = connection.cursor()
try:
cursor.execute(
'''UPDATE twinspirations SET last_sent = %s WHERE id = %s''',
("now", id))
connection.commit()
except (Exception, Error) as error:
print("Error while updating last_sent", error)
finally:
# closing database connection.
db_connect.close_db(connection)
def update_acknowledged_followers_in_db(followers):
# Updates acknowledged_followers with all new followers
connection = db_connect.connect_to_db()
try:
for follower in followers:
add_acknowledged_follower(connection, follower)
except (Exception, Error) as error:
print("Failed to update acknowledged followers in db", error)
finally:
# closing database connection.
db_connect.close_db(connection)
def get_twinspiration():
connection = db_connect.connect_to_db()
cursor = connection.cursor()
twinspiration = ""
try:
cursor.execute(
'''SELECT id,twinspiration FROM twinspirations ORDER BY last_sent LIMIT 1;'''
)
twinspirations = cursor.fetchall()
except (Exception, Error) as error:
print("Error while fetching data from DB", error)
finally:
# closing database connection.
db_connect.close_db(connection)
return twinspirations[0]
def update_follower_count(count):
# Updates the follower_counts table with the current number of followers and the current timestamp
connection = db_connect.connect_to_db()
cursor = connection.cursor()
try:
insert_query = '''INSERT INTO follower_counts (COUNT, CREATED_AT) VALUES (%s,%s);'''
record_to_insert = (count, "now")
cursor.execute(insert_query, record_to_insert)
record_count = cursor.rowcount
if record_count:
connection.commit()
print("Updated follower count to ", count)
except (Exception, Error) as error:
print("Error updating count: ", error)
finally:
db_connect.close_db(connection)
def get_acked_followers():
# Returns a list of follower ids for already acknowledged followers
connection = db_connect.connect_to_db()
cursor = connection.cursor()
follower_records = []
get_acked_followers_query = '''SELECT "follower_id" FROM acknowledged_followers order by date_sent desc;'''
try:
cursor.execute(get_acked_followers_query)
follower_records = cursor.fetchall()
except (Exception, Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
db_connect.close_db(connection)
return collect_follower_ids(follower_records)