def __init__(self):
self.conn = Connection()
class DBUtil(object):
def __init__(self):
self.conn = Connection()
def fix_string(self, s):
return str(s).replace("'", "\\'")
def register_customer(self, data):
query = """
INSERT INTO customers (name, surname, login, password, email) VALUES (%s);
"""
params = ", ".join([(" '%s' ") % x for x in data])
query = query % params
res = self._run_query(query)
if res:
return res
def login_customer(self, login, password):
query = """
SELECT * FROM customers WHERE login = '******' and password = '******'
"""
query = query % (login, password)
return self._run_query(query, True)
def _run_query(self, query, flag=False):
cur = self.conn.cursor()
try:
cur.execute(query)
if flag:
rows = cur.fetchall()
except psycopg2.Error as e:
self.conn.rollback()
return e
finally:
self.conn.commit()
cur.close()
self.conn.close()
if flag:
return rows
def get_category(self, name):
query = """
SELECT * FROM categories WHERE name = '%s';
"""
query = query % name
return self._run_query(query, True)
def create_category(self, name):
query = """
INSERT INTO categories (name) VALUES ('%s');
"""
query = query % name
return self._run_query(query)
def add_item(self, data, categories):
query = """
INSERT INTO items (shop_id, name, description, price, image_url) VALUES
(%s) RETURNING id;
"""
params = ", ".join([(" '%s' ") % x for x in data])
query = query % params
id_item = self._run_query(query, True)[0][0]
for category in categories:
if not id_item:
continue
id_cat = self.get_category(category)[0][0]
query = """
INSERT INTO items_to_categories (category_id, item_id) VALUES
(%s, %s);
"""
query = query % (id_cat, id_item)
self._run_query(query)
def get_item(self, name, shop_id):
query = """
SELECT * FROM items WHERE name = '%s' and shop_id = %s ;
"""
query = query % (name, shop_id)
return self._run_query(query, True)
def get_items_by_shop_id(self, shop_id):
query = """
SELECT * FROM items WHERE shop_id = %s ;
"""
query = query % (shop_id)
return self._run_query(query, True)
def add_shop(self, name, lat, lon, descr, image_url):
query = """
INSERT INTO shops (name, latitude, longitude, position, description,
image_url) VALUES ('%s', %s, %s, point(%s, %s), '%s', '%s');
"""
name = self.fix_string(name)
query = query % (name, lat, lon, lat, lon, descr, image_url)
return self._run_query(query)
def get_shop_by_name(self, name, lat, lon):
query = """
SELECT * FROM shops WHERE name = '%s' and latitude <= %s and latitude >= %s
and longitude <= %s and longitude >= %s;
"""
eps = 0.000005
lat_d = float(lat) - eps
lat_u = float(lat) + eps
lon_d = float(lon) - eps
lon_u = float(lon) + eps
query = query % (name, lat_u, lat_d, lon_u, lon_d)
return self._run_query(query, True)
def search_items_by_name(self, name):
query = """
SELECT * FROM items WHERE name %% '%s';
"""
query = query % name
return self._run_query(query, True)
def search_shops_by_name(self, name):
query = """
SELECT * FROM shops WHERE name %% '%s';
"""
query = query % name
return self._run_query(query, True)
def search_offers_by_name(self, name):
query = """
SELECT * FROM offers WHERE name %% '%s';
"""
query = query % name
return self._run_query(query, True)
def search_items_by_description(self, descr_query):
query = """
SELECT * FROM items WHERE description @@ '%s';
"""
query = query % descr_query
return self._run_query(query, True)
def search_shops_by_description(self, descr_query):
query = """
SELECT * FROM shops WHERE description @@ '%s';
"""
query = query % descr_query
return self._run_query(query, True)
def search_offers_by_description(self, descr_query):
query = """
SELECT * FROM offers WHERE description @@ '%s';
"""
query = query % descr_query
return self._run_query(query, True)
# items = [item_name, item_name]
def add_offer(self, data, items, shop_id):
query = """
INSERT INTO offers (shop_id, name, description, percent, end_time) VALUES
(%s) RETURNING id;
"""
params = ", ".join([(" '%s' ") % x for x in data])
query = query % params
id_offer = self._run_query(query, True)[0][0]
for item in items:
if not id_offer:
continue
id_item = self.get_item(item, shop_id)[0][0]
query = """
INSERT INTO offers_to_items (offer_id, item_id) VALUES
(%s, %s);
"""
query = query % (id_offer, id_item)
self._run_query(query)
def add_offer_to_store(self, data, items_ids, shop_id):
query = """
INSERT INTO offers (shop_id, name, description, percent, end_time) VALUES
(%s) RETURNING id;
"""
params = ", ".join([(" '%s' ") % x for x in data])
query = query % params
id_offer = self._run_query(query, True)[0][0]
for item in items_ids:
if not id_offer:
continue
query = """
INSERT INTO offers_to_items (offer_id, item_id) VALUES
(%s, %s);
"""
query = query % (id_offer, item)
self._run_query(query)
def get_shop_id_and_name_by_login(self, login):
query = """
SELECT shops.id, shops.name FROM shops INNER JOIN merchants ON
shops.id = merchants.shop_id WHERE merchants.login = '******';
"""
query = query % login
return self._run_query(query, True)
def get_shops_around(self, latitude, longitude, distance, limit):
query = """
SELECT *
FROM
(SELECT id, name, latitude, longitude, description, image_url,
round((position <@> point(%s, %s))::numeric, 3) as distance
FROM shops
ORDER BY position <-> point(%s, %s)
) tmp
WHERE tmp.distance < %s
LIMIT %s;
"""
query = query % (latitude, longitude, latitude, longitude, distance, limit)
return self._run_query(query, True)
def search_item_in_zone(self, text, lat, lon, dist):
query = """
SELECT * FROM
(SELECT items.id, items.name, shops.name, shops.latitude, shops.longitude,
items.description, items.price, items.image_url,
round((position <@> point(%s, %s))::numeric, 3) as distance
FROM shops INNER JOIN items on shops.id=items.shop_id
WHERE items.name %% '%s' or items.description @@ '%s'
or shops.name %% '%s' or shops.description @@ '%s'
ORDER BY position <-> point(%s, %s)
) tmp
WHERE tmp.distance < %s;
"""
query = query % (lat, lon, text, text, text, text, lat, lon, dist)
return self._run_query(query, True)
def get_items_by_category(self, category_name):
query = """
SELECT items_to_categories.item_id FROM items_to_categories INNER JOIN categories ON
items_to_categories.category_id = categories.id WHERE categories.name = '%s';
"""
query = query % category_name
return self._run_query(query, True)
def get_item_by_id(self, it_id):
query = """
SELECT items.id, items.name, shops.name, shops.latitude, shops.longitude,
items.description, items.price, items.image_url
FROM items INNER JOIN shops on shops.id = items.shop_id WHERE items.id = %s;
"""
query = query % it_id
return self._run_query(query, True)
