· 6 years ago · Oct 22, 2019, 05:00 PM
1# coding: utf-8
2import sqlite3
3from tkinter import *
4from tkinter import messagebox
5import urllib.request
6from bs4 import BeautifulSoup
7
8def extract():
9 html_doc = urllib.request.urlopen("https://www.ulabox.com/campaign/productos-sin-gluten#gref").read()
10 soup = BeautifulSoup(html_doc, 'html.parser')
11
12 articles = soup.find_all('article')
13
14 products = []
15
16 for a in articles:
17 product = []
18
19 hgroup = a.find('hgroup')
20 product_brand = hgroup.find('h4').find('a').string
21 product_name = hgroup.find('h3').find('a').string
22 product_link = hgroup.find('h3').find('a').get('href')
23 product_price = a.find('span', class_='delta').string + a.find('span', class_='milli').string[0:3]
24 product_sale = a.find('del', class_='product-item__price product-item__price--old product-grid-footer__price--old nano | flush--bottom')
25
26 product.append(product_brand.strip())
27 product.append(product_name.strip())
28 product.append(product_link)
29 product.append(float(product_price.replace(',','.')))
30
31 if product_sale is not None:
32 product.append(float(product_sale.string[0:4].replace(',','.')))
33
34 products.append(product)
35
36 return products
37
38
39def crearBDDD():
40 productos = extract()
41
42
43 bdd = sqlite3.connect('bddproductos.db') # conectando a la base de datos
44 cursor = bdd.cursor()
45
46 cursor.execute("""DROP TABLE if exists producto""") # si existe la tabla 'producto' la elimina
47 # creamos la tabla producto: marca, nombre, link a la descripcion del producto y precio/s (si est en oferta tiene de un precio).
48 cursor.execute(
49 """CREATE TABLE producto (marca text not null, nombre text not null, link text not null, precio real not null, oferta real)""")
50
51 for producto in productos:
52 marca = producto[0]
53 nombre = producto[1]
54 link = producto[2]
55 precio = producto[3]
56 oferta = None
57 if len(producto) == 5:
58 oferta = producto[4]
59
60 cursor.execute("""INSERT INTO producto (marca, nombre, link , precio, oferta) values(?,?,?,?,?)"""
61 , (marca, nombre, link, precio, oferta))
62
63 bdd.commit() # guardar el resultado de las operaciones realizadas en la BDD
64
65 cursor = bdd.execute("SELECT COUNT(*) FROM PRODUCTO") # numero de filas guardadas
66 messagebox.showinfo("Terminado", "Base de datos creada correctamente. Se han guardado " + str(
67 cursor.fetchone()[0]) + " elementos")
68
69 bdd.close()
70
71
72def show_list(elements, tk):
73 # Scrollbar
74 scrollbar = Scrollbar(tk)
75 scrollbar.pack(side=RIGHT, fill=Y)
76 # Listbox widget
77 mylist = Listbox(tk, yscrollcommand=scrollbar.set)
78 mylist.pack(fill=BOTH, expand=1)
79 scrollbar.config(command=mylist.yview)
80 # Add elements to listbox
81 for item in elements:
82 mylist.insert(END, "Marca: " + item[0])
83 mylist.insert(END, "Nombre: " + item[1])
84 mylist.insert(END, "Link: " + item[2])
85 mylist.insert(END, "Precio normal: " + str(item[3]))
86 if item[4] != None:
87 mylist.insert(END, "Precio de oferta: " + str(item[4]))
88 mylist.insert(END, "")
89
90def search_brand(query, tk): #TODO: Hacer que busque en la DB
91
92 bdd = sqlite3.connect('bddproductos.db')
93 cursor = bdd.cursor()
94
95 cursor.execute("SELECT * FROM PRODUCTO WHERE MARCA = '"+query+"'")
96
97 productos_marca = cursor.fetchall()
98
99
100 show_list(productos_marca, tk)
101
102
103
104def clear_window(tk):
105 ls = tk.pack_slaves()
106 for l in ls:
107 l.destroy()
108
109def show_main_buttons():
110 store_products_btn = Button(root, text="Almacenar productos", command=crearBDDD)
111 store_products_btn.pack()
112
113 show_brand_btn = Button(root, text="Mostrar marca", command=show_brand)
114 show_brand_btn.pack()
115
116 search_deals_btn = Button(root, text="Buscar ofertas", command=search_deals)
117 search_deals_btn.pack()
118
119
120
121
122def get_brands():
123
124 bdd = sqlite3.connect('bddproductos.db')
125 bdd.text_factory=str
126 cursor = bdd.cursor()
127
128 lista_marcas = cursor.execute("SELECT MARCA FROM PRODUCTO")
129
130 list = []
131
132 for m in lista_marcas:
133
134 list.append(m[0])
135
136 print(list)
137
138 return list
139
140
141def show_brand():
142 new_window = Toplevel()
143 new_window.title("Buscar por marca")
144 new_window.geometry("800x600")
145 frame = Frame(new_window)
146 frame.pack()
147 brands = get_brands() #TODO: Cambiar por brands = get_brands(), hay que crear el mtodo
148 spinbox = Spinbox(frame, values=brands)
149 spinbox.pack(side="left")
150 results_frame = Frame(new_window)
151 results_frame.pack(fill=BOTH, expand=1)
152
153 def search_brand_caller():
154 clear_window(results_frame)
155 search_brand(spinbox.get(), results_frame)
156
157
158 b = Button(frame, text="Buscar", command=search_brand_caller)
159 b.pack(side="right")
160
161def search_deals(): #TODO
162 new_window = Toplevel()
163 new_window.title("Ofertas")
164 new_window.geometry("800x600")
165
166
167 bdd = sqlite3.connect('bddproductos.db')
168 cursor = bdd.cursor()
169
170 cursor.execute("SELECT * FROM PRODUCTO WHERE OFERTA NOT NULL")
171
172 prods_oferta = cursor.fetchall()
173
174 show_list(prods_oferta, new_window)
175
176
177
178
179if __name__ == '__main__':
180 root = Tk()
181 root.title("Ulabox Scrapper")
182 root.geometry("300x150")
183
184 show_main_buttons()
185
186 get_brands()
187
188 root.mainloop()