def count():
connection = connect_db.connect()
cur = connection.cursor()
query = "SELECT COUNT(*) FROM {table} where pubsub_name is NOT NULL"
cur.execute(query.format(table=connect_db.get_table()))
count = cur.fetchone()
count = str(count)
# Remove the tuple aspect of the number
count = count.replace("(", "").replace(",", "").replace(")", "")
return count
def delete_entry(self):
sub_name = self.sub_name.text()
dates = self.date.text()
on_sale = self.on_sale.text()
sub_name = sub_name.replace(" ", "-").lower()
price = self.price.text()
image = self.image.text()
connection = connect_db.connect()
cur = connection.cursor()
print("Deleting data on " + sub_name + "!")
cur.execute("DELETE FROM " + connect_db.get_table() +
" WHERE pubsub_name LIKE '%" + sub_name + "%'")
connect_db.close(connection)
def on_sale_check():
connection = connect_db.connect()
cur = connection.cursor()
"""
Queries if there are no none entires
"""
query = "SELECT pubsub_name, dates, on_sale, price, image FROM {table} where pubsub_name is NOT NULL"
cur.execute(query.format(table=connect_db.get_table()))
sub_name = []
last_on_sale = []
on_sale = []
price = []
image = []
records = cur.fetchall()
"""
Loops through all the columns and rows
"""
for i in range(len(records)):
sub_name.append(records[i][0])
last_on_sale.append(records[i][1])
on_sale.append(records[i][2])
price.append(records[i][3])
image.append(records[i][4])
sub_name = [x for x in sub_name if x is not None]
original_name = [w.replace("-", " ") for w in sub_name]
data = {}
"""
Creates a primary catagory
"""
data["All_subs".lower()] = []
"""
Create a default JSON structure
"""
for i in range(len(records)):
data["All_subs".lower()].append({
"name": original_name[i],
"on_sale": on_sale[i],
"image": image[i],
"last_on_sale": last_on_sale[i],
"price": price[i],
"query_name": sub_name[i],
})
response = jsonify(data["All_subs".lower()])
response.headers.add("Access-Control-Allow-Origin", "*")
return response
def sub_runner_checker(subname):
try:
connection = connect_db.connect()
cur = connection.cursor()
# Checks to see if the name exist in the record, then grabs a random row from that column limiting it to one.
try:
command = "SELECT pubsub_name, dates, on_sale, price, image FROM {table} WHERE pubsub_name = '{name}' ORDER BY dates DESC LIMIT 1"
query = cur.execute(
command.format(table=connect_db.get_table(), name=subname)
)
except:
return (
"Unfortunately, we do not have deal data available on "
+ subname.replace("-", " ")
+ f" sub at this time."
)
# Fetches us all the rows so we can grab data from each
records = cur.fetchall()
for row in records:
last_on_sale = row[1]
on_sale = row[2]
price = row[3]
image = row[4]
print(records)
# Creates a dictionary
data = {}
# Creates a primary catagory
data["sub_names"] = []
# Create a default JSON structure
data["sub_names"].append(
{
"sub_name": subname.lower(),
"last_sale": last_on_sale,
"status": on_sale,
"price": price,
"image": image,
}
)
sub_info = json.dumps(data["sub_names"], indent=2)
return sub_info
except:
return abort(404)
def random_subs():
try:
connection = connect_db.connect()
cur = connection.cursor()
# Checks to see if the name exist in the record, then grabs a random row from that column limiting it to one.
command = "SELECT pubsub_name, dates, on_sale, price, image FROM {table} WHERE pubsub_name is NOT NULL ORDER BY random() DESC LIMIT 1"
query = cur.execute(command.format(table=connect_db.get_table()))
# Fetches us all the rows so we can grab data from each
records = cur.fetchall()
for row in records:
subname = row[0]
last_on_sale = row[1]
on_sale = row[2]
price = row[3]
image = row[4]
# Creates a dictionary
data = {}
# Creates a primary catagory
data["random_sub"] = []
# Create a default JSON structure
data["random_sub"].append({
"sub_name": subname.lower(),
"last_sale": last_on_sale,
"status": on_sale,
"price": price,
"image": image,
})
sub_info = jsonify(data["random_sub"])
sub_info.headers.add("Access-Control-Allow-Origin", "*")
return sub_info
except:
return abort(404)
def all_subs_data():
connection = connect_db.connect()
cur = connection.cursor()
query = (
"SELECT pubsub_name FROM {table} WHERE pubsub_name is not NULL ORDER BY on_sale"
)
cur.execute(query.format(table=connect_db.get_table()))
records = cur.fetchall()
data = {}
"""
Creates a primary catagory
"""
data["All_subs".lower()] = []
"""
Create a default JSON structure
"""
for sub in records:
data["All_subs".lower()].append({"name": sub[0]})
response = jsonify(data["All_subs".lower()])
response.headers.add("Access-Control-Allow-Origin", "*")
return response
def add_entry(self):
sub_name = self.sub_name.text()
dates = self.date.text()
on_sale = self.on_sale.text()
sub_name = sub_name.replace(" ", "-").lower()
original = self.sub_name.text().lower()
price = self.price.text()
image = self.image.text()
connection = connect_db.connect()
cur = connection.cursor()
# Checks to see if that row exist
exist_query = (
"select exists(select 1 from {table} where pubsub_name ='{sub}' limit 1)"
)
exist_check = cur.execute(
exist_query.format(table=connect_db.get_table(), sub=sub_name))
count = cur.fetchone()[0]
# If our data returns true, update the status and dates
if count == True:
print("There exist a version of " + sub_name + " now updating!")
update_string = "Update {table} SET on_sale = '{on_sale}', dates = '{dates}', price = '{price}', image = '{image}' WHERE pubsub_name = '{sub}'"
update_query = cur.execute(
update_string.format(
table=connect_db.get_table(),
on_sale=on_sale,
dates=dates,
price=price,
image=image,
sub=sub_name,
))
# Sends an email out if a sub is now on sale
if on_sale == "True":
print("Sub is on sales")
mailgun.send_email(original, dates)
with open("webhook.json") as webhook_data:
data = json.load(webhook_data)
webhook = DiscordWebhook(url=data["webhook"])
embed = DiscordEmbed(
title="New sub on sale!",
description=":tada: A sub is on sale!\n" + sub_name +
" is on sale from: " + dates + ", for the price of " +
price)
embed.set_image(url=image)
# add embed object to webhook
webhook.add_embed(embed)
response = webhook.execute()
else:
print("This sub doesn't exist, now adding!")
# Inserts the data into each column
cur.execute(
"INSERT INTO " + connect_db.get_table() +
"(pubsub_name, dates, on_sale, price, image) VALUES (%s, %s, %s, %s, %s)",
(sub_name, dates, on_sale, price, image),
)
if on_sale == "True":
print("Sub is on sale")
mailgun.send_email(original, dates)
with open("webhook.json") as webhook_data:
data = json.load(webhook_data)
webhook = DiscordWebhook(url=data["webhook"])
embed = DiscordEmbed(
title="New sub on sale!",
description=":tada: A sub is on sale!\n" + sub_name +
" is on sale from: " + dates + ", for the price of " +
price)
embed.set_image(url=image)
# add embed object to webhook
webhook.add_embed(embed)
response = webhook.execute()
connect_db.close(connection)