def count_and_save_errors(self):
with DatabaseConnection('data.db') as cursor:
cursor.execute(
"CREATE TABLE IF NOT EXISTS errors (error_number int, table_name text)"
)
cursor.execute("INSERT INTO errors VALUES (?, ?)",
(self.error_count, self.table_name))
def _insert_into_table(self, values_list):
content_list = [value.get('content', None) for value in values_list]
for insert_values in zip(*content_list):
if None in insert_values:
continue
with DatabaseConnection('data.db') as cursor:
cursor.execute("INSERT INTO {} VALUES {}".format(
self.table_name, insert_values))
def per_sector():
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute(
"SELECT dealsDateCreated,dealsPrice FROM deals")
deals = content.fetchall()
date_formated = remove_day(deals)
deals_per_month = group_data(date_formated)
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute(
"SELECT dealsDateCreated, dealsPrice, sectorKey FROM deals INNER JOIN companies ON deals.companiesId = companies.companiesId"
)
deals = content.fetchall()
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute("SELECT sectorKey, sector FROM sectors")
sectors = content.fetchall()
date_formated = remove_day(deals)
sums = defaultdict(int)
for amount, date, sectorKey in date_formated:
amount_number = int(amount)
key = f'{date}#{sectorKey}'
sums[key] += amount_number
another_list = []
for sectorKey, sector in sectors:
for key, value in sums.items():
date, formated_key = key.split('#')
if formated_key == sectorKey:
another_list.append((sector, sectorKey, value, date))
final = []
for name, key, value, month in another_list:
total = int(deals_per_month[month])
sector_pct = (100 * value / total) / 100
formated_pct = "{:.2f}".format(sector_pct)
final.append((int(key), name, formated_pct, month))
final.sort(key=operator.itemgetter(0)) # index da sectorKey
final.sort(key=lambda index: datetime.strptime(index[3], '%m/%Y'))
return final
def per_contact():
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute(
"SELECT dealsPrice, contactsName FROM deals INNER JOIN contacts ON deals.contactsId = contacts.contactsId"
)
contacts = content.fetchall()
data = group_data(contacts)
final = list(data.items())
final.sort(key=operator.itemgetter(1), reverse=True)
return final
def _create_table(self, values_list):
"""
Eu sei que essa não é a pratica recomendada mas utilizar o .format foi o único jeito que eu
consegui usar nome de colunas e tables dinamicamente.
"""
title_list = [
f'{value.get("title", None)} text,' for value in values_list
]
titles_formated_for_SQL = f"({''.join(title_list)[:-1]})" # descobri que para conseguir criar uma coluna para a table é necessario esse formatação super estranha
with DatabaseConnection('data.db') as cursor:
cursor.execute("CREATE TABLE IF NOT EXISTS {} {}".format(
self.table_name, titles_formated_for_SQL))
def per_date():
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute(
"SELECT dealsDateCreated,dealsPrice FROM deals")
deals = content.fetchall()
date_formated = remove_day(deals)
data = group_data(date_formated)
final = list(data.items())
final.sort(key=lambda index: datetime.strptime(index[0], '%m/%Y'),
reverse=True)
return final
def select_from_db(self):
if self.beach:
with DatabaseConnection(self.database_path) as cursor:
select_query = "SELECT * FROM locations_2 WHERE beach=?"
results = cursor.execute(select_query, (self.beach, ))
return results.fetchall()
elif self.city:
with DatabaseConnection(self.database_path) as cursor:
select_query = "SELECT * FROM locations_2 WHERE city=?"
results = cursor.execute(select_query, (self.city, ))
return results.fetchall()
elif self.state:
with DatabaseConnection(self.database_path) as cursor:
select_query = "SELECT * FROM locations_2 WHERE state=?"
results = cursor.execute(select_query, (self.state, ))
return results.fetchall()
else:
with DatabaseConnection(self.database_path) as cursor:
select_query = "SELECT * FROM locations_2"
results = cursor.execute(select_query)
return results.fetchall()
def errors():
with DatabaseConnection('./parser/data.db') as cursor:
content = cursor.execute("SELECT * FROM errors")
errors = content.fetchall()
return errors
def save_to_db(state, city, beach, wind, lat, lon, path_to_db):
with DatabaseConnection(path_to_db) as cursor:
cursor.execute('INSERT INTO locations_4 VALUES (?,?,?,?,?,?)',
(state, city, beach, wind, lat, lon))