def update_password(db: Database, email: str, password: str) -> bool:
query_str = """
UPDATE user
SET password = "******"
WHERE email = "{}"
""".format(password, email)
print(query_str)
return db.execute(query_str=query_str)
def update_user(db: Database, user_id: int, email: str, first_name: str,
last_name: str, phone_number: str) -> bool:
query_str = """
UPDATE user
SET email = "{}", first_name = "{}", last_name = "{}", phone_number = "{}"
WHERE id = {}
""".format(email, first_name, last_name, phone_number, user_id)
return db.execute(query_str=query_str)
def register_user(db: Database, invitation_code: str, first_name: str,
last_name: str, phone_number: str,
password: str) -> bool:
query_str = """
UPDATE user
SET
first_name = "{}", last_name = "{}", phone_number = "{}", password = "******"
WHERE invitation_code = "{}"
""".format(first_name, last_name, phone_number, password, invitation_code)
return db.execute(query_str=query_str)
def insert_user(db: Database,
email: str,
first_name: str = '',
last_name: str = '',
phone_number: str = '',
is_admin: int = 0,
invitation_code: str = '') -> bool:
query_str = """
INSERT INTO user (
email, first_name, last_name, phone_number, is_admin, invitation_code)
VALUES ("{}", "{}", "{}", "{}", {}, "{}")
""".format(email, first_name, last_name, phone_number, is_admin,
invitation_code)
return db.execute(query_str=query_str)
class ExploitRate:
def __init__(self, src, dst):
self.src = src
self.dst = dst
self.db = Database()
def get_by_section(self, section):
current_date = strftime("%Y-%m-%d", localtime())
# 쿼리를 날릴 때 범위를 정하기 위해 월, 달, 일을 나눔
day = int(current_date[8:10]) - section
year = int(current_date[:4])
month = int(current_date[5:7])
# 현재 일수 보다 범위가 클경우 전달부터 하도록 함 ex) current 10일 범위가 30일경우 전달 20부터 10일까지
if day < 1:
if month == 1:
month = 12
else:
month -= 1
day = calendar.monthrange(year, month)[1] + day
if month < 10:
month = "0" + str(month)
if day < 10:
day = "0" + str(day)
else:
day = str(day)
from_date = current_date[:5] + month + current_date[7:]
from_date = from_date[:8] + day + from_date[10:]
# src,dct가 같은 것중 에서 between(from_date ~ current_date)에 대한 정보를 가져옴
query = query_formats.daily_exchange_rate_select_format % (
self.src, self.dst, from_date)
res = self.db.execute(query)
return res
class Wows_database:
"""
Scrapes and registers data into database.
"""
__slots__ = ('database', 'logger')
def __init__(self, db_path):
self.database = Database(db_path)
self.logger = Logger(__name__)
# if db file not found or empty, create file
try:
with open(db_path, 'rb') as f:
if f.read() == b'':
self._create_table()
except Exception as e:
self.logger.info('Database not found.')
self._create_table()
async def update(self):
"""
Update database.
"""
self.logger.info('Update started.')
try:
# await self._update_medium()
# await self._update_facebook()
await self._update_hp()
except Exception:
self.logger.critical(f'Exception while updating: {traceback.format_exc()}')
self.logger.info('Update finished.')
def _create_table(self):
self.logger.debug('Creating wows database table.')
self.database.executescript("""
CREATE TABLE wowsnews(
id INTEGER PRIMARY KEY AUTOINCREMENT,
source TEXT,
title TEXT,
description TEXT,
url TEXT,
img TEXT
);
""")
self.logger.debug('Created wows database table.')
def _get_latest(self, source:str):
"""
Get latest news stored in database.
Returns
-------
res : tuple, None
"""
self.logger.debug(f'Starting _get_latest source: {source}')
res = self.database.fetchone('SELECT * FROM wowsnews WHERE source==? ORDER BY id DESC', (source,))
self.logger.debug(f'_get_latest result: {res}')
return res
async def _update_hp(self):
"""
Check for new articles, if found update db.
"""
self.logger.info('Updating wows hp.')
# get data and data from db.
try:
data = get_hp_articles()
except ScrapingException:
self.logger.critical('Scraping failed.')
return
data_db = self._get_latest('wowshomepage')
# if database is up to date return
if _is_same_data(data_db, data[0]):
self.logger.info('Wows hp is up to date.')
return
# if same url already exists in database, return
elif self._url_exists(data[0][3]):
self.logger.info('Article with same url exists.')
return
# update db
self.logger.info('Saving data.')
try:
self.database.execute('INSERT INTO wowsnews(source, title, description, url, img) VALUES(?, ?, ?, ?, ?)', data[0])
except Exception as e:
self.logger.critical(f'Inserting into database failed: {e}')
return
self.logger.info('Updated wows hp.')
async def _update_facebook(self):
"""
Check for new articles, if found update db.
"""
self.logger.info('Updating facebook.')
try:
data = get_facebook_articles()
except ScrapingException:
self.logger.critical('Scraping failed.')
return
data_db = self._get_latest('facebook')
# if up to date, return
if _has_same_url(data_db, data[0]):
self.logger.info('Facebook is up to date.')
return
# counter shows how many articles to update
counter = 0
for d in data:
# if data is most recent in db
if d == data_db:
break
counter += 1
# data.reverse() not working, so using temp.reverse()
temp = data
temp.reverse()
news = temp[:counter]
# update db
try:
for new in news:
# continue if url already exists in database
if self._url_exists(new[3]):
self.logger.info('Article with same url exists.')
continue
self.database.execute('INSERT INTO wowsnews(source, title, description, url, img) VALUES(?, ?, ?, ?, ?)', new)
except Exception as e:
self.logger.critical(f'Inserting into database failed: {e}')
return
self.logger.info('Updated facebook.')
async def _update_medium(self):
"""
Check for new articles, if found update db.
"""
self.logger.info('Updating medium.')
try:
data = get_medium_articles()
except ScrapingException:
self.logger.critical('Scraping failed.')
return
# get latest data in database
data_db = self._get_latest('medium')
# if up to date, return
if _is_same_data(data_db, data[0]):
self.logger.info('Medium is up to date.')
return
# counter shows how many articles to update
counter = 0
for d in data:
# if url is most recent in db
if d == data_db:
break
counter += 1
# data.reverse() not working, so using temp reverse()
temp = data
temp.reverse()
news = temp[:counter]
try:
for new in news:
self.database.execute('INSERT INTO wowsnews(source, title, description, url, img) VALUES(?, ?, ?, ?, ?)', new)
except Exception as e:
self.logger.critical(f'Inserting into database failed: {e}')
return
self.logger.info('Updated medium.')
def _url_exists(self, url:str):
self.logger.debug('Checking if url already exists in database.')
self.logger.debug(f'url is {url[10:]}')
try:
res = self.database.fetchone(f'SELECT * FROM wowsnews WHERE url==?', (url,))
except Exception as e:
self.logger.critical(f'Fetching datbase failed: {e}')
res = None
self.logger.debug(f'Result: {res}')
if not res:
return False
return True
def main():
# Initialise logging
logger = logging.getLogger('assignment-etl')
# Set up the schema (for this exercise, it's OK to just do this)
db = Database()
logger.info({'message': 'Creating `iceland` schema'})
print("hwooooo!!!!!")
db.execute("create schema if not exists iceland")
# Concerts
from data_models.data_model_api_iceland_concerts import DataModelApiIcelandConcerts
iceland_concerts_data_model = DataModelApiIcelandConcerts()
iceland_concerts_data_model.drop_table() # Start fresh every time
iceland_concerts_data_model.create_table() # This creates the table
from etl.iceland_api.api_iceland_concerts import ApiIcelandConcerts
iceland_concerts_etl = ApiIcelandConcerts()
if not iceland_concerts_etl.run(): # This populates the table
raise Exception("ETL Job failed: api_iceland_concerts")
#Earthquakes
from data_models.data_model_api_iceland_earthquakes import DataModelApiIcelandEarthquakes
iceland_earthquakes_data_model = DataModelApiIcelandEarthquakes()
iceland_earthquakes_data_model.drop_table() # Start fresh every time
iceland_earthquakes_data_model.create_table() # This creates the table
from etl.iceland_api.api_iceland_earthquakes import ApiIcelandEarthquakes
iceland_earthquakes_etl = ApiIcelandEarthquakes()
if not iceland_earthquakes_etl.run(): # This populates the table
raise Exception("ETL Job failed: api_iceland_earthquakes")
#Drivers base table - created with date and time as separate columns
from data_models.data_model_api_iceland_drivers import DataModelApiIcelandDrivers
iceland_drivers_data_model = DataModelApiIcelandDrivers()
iceland_drivers_data_model.drop_table() # Start fresh every time
iceland_drivers_data_model.create_table() # This creates the table
from etl.iceland_api.api_iceland_drivers import ApiIcelandDrivers
iceland_drivers_etl = ApiIcelandDrivers()
if not iceland_drivers_etl.run(): # This populates the table
raise Exception("ETL Job failed: api_iceland_drivers")
#drivers table - created with timestamp column from date and time
from data_models.data_model_drivers_custom import DataModelApiIcelandDriversCustom
iceland_drivers_custom_data_model = DataModelApiIcelandDriversCustom()
iceland_drivers_custom_data_model.drop_drivers()
iceland_drivers_custom_data_model.create_drivers()
#Test case - 1
from tests.test_case_1 import DataModelApiIcelandTestCaseOne
test_case_one = DataModelApiIcelandTestCaseOne()
test_case_one.test_case_one()
#Test Case - 2
from tests.test_case_2 import DataModelApiIcelandTestCaseTwo
test_case_two = DataModelApiIcelandTestCaseTwo()
test_case_two.test_case_two()
#Test Case - 3
from tests.test_case_3 import DataModelApiIcelandTestCaseThree
test_case_three = DataModelApiIcelandTestCaseThree()
test_case_three.test_case_three()
