· 6 years ago · Oct 22, 2019, 02:10 PM
1import re
2import sys
3import time
4import sqlite3
5from Producto import Producto
6import urllib.request as urllib
7from bs4 import BeautifulSoup
8
9
10def createTableProducts():
11 conn = sqlite3.connect('productos.db')
12 print ("Opened database successfully")
13 conn.execute('''DROP TABLE IF EXISTS PRODUCTOS;''')
14 conn.execute('''CREATE TABLE IF NOT EXISTS PRODUCTOS
15 (MARCA CHAR(255),
16 NOMBRE CHAR(255),
17 LINK TEXT,
18 PRECIO_ACTUAL REAL,
19 PRECIO_ANTERIOR REAL);''')
20 print ("Table created successfully")
21 conn.close()
22
23def saveProducts(productos):
24 conn = sqlite3.connect('productos.db')
25 print("Guardando en base de datos...")
26 for producto in productos:
27 conn.execute("insert into PRODUCTOS values (?, ?, ?, ?, ?)", (producto.marca, producto.nombre, producto.link, float(producto.precios[0]), float(producto.precios[1])))
28 conn.commit()
29 conn.close()
30
31def getProducts():
32 conn = sqlite3.connect('productos.db')
33 for row in conn.execute('SELECT * FROM PRODUCTOS'):
34 print(row)
35 conn.close()
36
37
38def getHTML(url):
39 try:
40 f = urllib.urlopen(url)
41 return f.read()
42 f.close()
43 except urllib.error.HTTPError as e:
44 print("Ocurrió un error")
45 print(e.code)
46 return ""
47 except urllib.error.URLError as e:
48 print("Ocurrió un error")
49 print(e.reason)
50 return ""
51
52
53# Print iterations progress
54def printProgressBar (iteration, total, prefix = '', suffix = '', decimals = 1, length = 100, fill = '█', printEnd = "\r"):
55 """
56 Call in a loop to create terminal progress bar
57 @params:
58 iteration - Required : current iteration (Int)
59 total - Required : total iterations (Int)
60 prefix - Optional : prefix string (Str)
61 suffix - Optional : suffix string (Str)
62 decimals - Optional : positive number of decimals in percent complete (Int)
63 length - Optional : character length of bar (Int)
64 fill - Optional : bar fill character (Str)
65 printEnd - Optional : end character (e.g. "\r", "\r\n") (Str)
66 """
67 percent = ("{0:." + str(decimals) + "f}").format(100 * (iteration / float(total)))
68 filledLength = int(length * iteration // total)
69 bar = fill * filledLength + '-' * (length - filledLength)
70 print('\r%s |%s| %s%% %s' % (prefix, bar, percent, suffix), end = printEnd)
71 # Print New Line on Complete
72 if iteration == total:
73 print()
74
75
76# --------------------------------------------------------------- #
77
78productos = []
79createTableProducts()
80
81print("Accediendo a la web...")
82link1 = "https://www.ulabox.com/campaign/productos-sin-gluten#gref"
83soup = BeautifulSoup(getHTML(link1), 'html.parser')
84print("Leyendo datos...")
85articles = soup.findAll('article')
86
87i = 0
88for article in articles:
89
90 marca = article['data-product-brand']
91 nombre = article['data-product-name']
92 link = ""
93 precios = []
94 precios.append(article['data-price'])
95
96 precio_viejo = article.find('del')
97 if not (precio_viejo is None):
98 precios.append( precio_viejo.string.split()[0].replace(',', '.'))
99
100
101 if len(precios) != 2:
102 precios.append("0.0")
103
104 link = "https://www.ulabox.com"+article.find('a')['href']
105
106 producto = Producto(marca, nombre, link, precios)
107 productos.append(producto)
108 printProgressBar(i + 1, len(articles), prefix = 'Extrayendo datos:', suffix = 'Complete', length = 50)
109 i = i+1
110 #print("Articulo " +str(i)+"/"+str(len(articles)))
111 #print(producto)
112
113time.sleep(0.2)
114saveProducts(productos)
115
116
117
118
119
120def getBrandsDistinct():
121 res = []
122 conn = sqlite3.connect('productos.db')
123 for row in conn.execute('SELECT DISTINCT MARCA FROM PRODUCTOS'):
124 res.append(row)
125 conn.close()
126 return res
127
128def getOffers():
129 res = []
130 conn = sqlite3.connect('productos.db')
131 for row in conn.execute('SELECT nombre, precio_anterior, precio_actual FROM PRODUCTOS where precio_anterior > 0'):
132 res.append(row)
133 conn.close()
134 return res
135
136def getByBrand(brand):
137 res = []
138 conn = sqlite3.connect('productos.db')
139 for row in conn.execute('SELECT nombre, precio_actual FROM PRODUCTOS where marca = ?', (brand,)):
140 res.append(row)
141 conn.close()
142 return res
143
144def showProductsByBrand_Window():
145 list_window = Toplevel()
146 scrollbar = Scrollbar(list_window, orient="vertical")
147 Lb1 = Listbox(list_window, width=50, yscrollcommand=scrollbar.set)
148 scrollbar.config(command=Lb1.yview)
149 scrollbar.pack(side="right", fill="y")
150 Lb1.pack(side="left",fill="both", expand=True)
151 i = 0
152 for n in getByBrand(w.get()):
153 Lb1.insert("end", n[0])
154 Lb1.insert("end", "Precio: " + str(n[1]) + "€")
155 Lb1.insert("end", "")
156
157 Lb1.pack()
158
159def showProductsOffers_Window():
160 list_window = Toplevel()
161 scrollbar = Scrollbar(list_window, orient="vertical")
162 Lb1 = Listbox(list_window, width=50, yscrollcommand=scrollbar.set)
163 scrollbar.config(command=Lb1.yview)
164 scrollbar.pack(side="right", fill="y")
165 Lb1.pack(side="left",fill="both", expand=True)
166 i = 0
167 for n in getOffers():
168 Lb1.insert("end", n[0])
169 Lb1.insert("end", "Precio sin oferta: " + str(n[1]) + "€")
170 Lb1.insert("end", "Precio con oferta: " + str(n[2]) + "€")
171 Lb1.insert("end", "")
172
173 Lb1.pack()
174
175
176from tkinter import *
177from tkinter import ttk
178from tkinter import messagebox
179
180master = Tk()
181
182
183marcas = getBrandsDistinct()
184w = Spinbox(master, values=[i[0] for i in marcas])
185i = 0
186w.pack(side = LEFT)
187mostrar = Button(master, text ="Mostrar productos", command = showProductsByBrand_Window)
188mostrar.pack(side = LEFT)
189buscar = Button(master, text ="Buscar ofertas", command = showProductsOffers_Window)
190buscar.pack()
191
192mainloop()