#!/usr/bin/python3
# -*- coding: utf-8 -*-
from datetime import datetime
import psycopg2
class DataBaseAPI:
def __init__(self):
self.database = psycopg2.connect(host="test",
database="test",
user="test",
password="test"
)
def checkIfExists(self, store, link):
try:
cur = self.database.cursor()
sql = f"SELECT DISTINCT link FROM public.store_items WHERE store='{store.title()}' AND visible='yes' AND link='{link}';"
cur.execute(sql)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return False
if cur.rowcount:
return True
else:
return False
def addProduct(self, product):
if self.checkIfExists(store=product["store"], link=product["url"]) is False:
link = product["url"],
store = product["store"],
name = product["name"],
image = product["image"]
visible = "yes"
cur = self.database.cursor()
sql = f"INSERT INTO public.store_items (store, name, link, image, visible, added_date) VALUES ('{store}', '{name}', '{link}', '{image}', '{visible}', '{datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')}');"
cur.execute(sql)
# # Commit the changes to the database
self.database.commit()
# Close communication with the PostgreSQL database
cur.close()
return True
else:
return False
def getAllLinks(self, store):
cur = self.database.cursor()
sql = f"SELECT DISTINCT link FROM public.store_items WHERE store='{store.title()}' AND visible='yes';"
cur.execute(sql)
cur.close()
return [link[0] for link in cur.fetchall()]
def getBlackList(self, store):
cur = self.database.cursor()
sql = f"SELECT DISTINCT link FROM public.store_items WHERE store='{store.title()}' AND visible='no';"
cur.execute(sql)
cur.close()
return [link[0] for link in cur.fetchall()]
def getAllKeywords(self, filtered_or_unfiltered):
cur = self.database.cursor()
sql = f"SELECT DISTINCT keyword FROM public.keywords WHERE filter_type='{filtered_or_unfiltered}';"
cur.execute(sql)
cur.close()
return [keyword[0] for keyword in cur.fetchall()]
def getAllSupremeKeywords(self, filtered_or_unfiltered):
cur = self.database.cursor()
sql = f"SELECT DISTINCT keyword FROM public.keywords_supreme WHERE filter_type='{filtered_or_unfiltered}';"
cur.execute(sql)
cur.close()
return [keyword[0] for keyword in cur.fetchall()]
EDIT:
Thanks for comments!
The database is used for shopping. What I mean by that is that I scrape a website that I love (Flowers etc) and then I collect each item that has been updated on the website and add it to my database if the product has not already been added in the database before (Reason why I have a checkIfExists function)
I also have different getLinks, getKeywords functions and the reason I have
getLinks = Is to see if there has been added new page in the website that is not in my DB
getBlackList = Incase they re-add same product and I don't want it to be too spammy with my logs then I can blacklist it inside the database.
getAllKeywords = Sometimes I want to filter for specific flowers where I have a function that checks whats the name of the product and then compare if those keywords matches from my database
I think thats pretty much it. Just a simple Add and Get database :)
