def save_into_db(data):
# create an an sqlite_advisory object
db = Database("countries.sqlite")
db.drop_table("IE")
db.add_table("IE",
country_iso="text",
name="text",
advisory_text="text",
visa_info="text")
try:
for country in data:
iso = data[country].get('country-iso')
name = data[country].get('name')
advisory = data[country].get('advisory-text').replace('"', '')
LOGGER.info(f'Saving {name} into the IE table')
visa = data[country].get('visa-info')
db.insert("IE", iso, name, advisory, visa)
LOGGER.success(
f"{name} was saved into the IE table with the following information: {visa}. {advisory}"
)
LOGGER.info('IE table successfully saved to the database')
except Exception as error_msg:
LOGGER.error(
f'An error has occured while saving {name} into the IE table because of the following error: {error_msg}'
)
db.close_connection()
def save_cities_timezones():
LOGGER.info("Retreiving timezones information for all countries...")
data = adding_lat_and_lng(get_cities_info())
# geolocator = Nominatim(user_agent="travelingstrategy")
#con = sqlite3.connect('../countries.sqlite')
#cur = con.cursor()
# should not create the table every time
# change in the future
#cur.execute('DROP TABLE IF EXISTS timezones')
#con.commit()
#cur.execute('CREATE TABLE timezones (city VARCHAR, country_name VARCHAR, country_iso VARCHAR, timezone VARCHAR, lat REAL, lng REAL, utc_offset int)')
#SScon.commit()
db = Database("countries.sqlite")
db.drop_table("timezones")
db.add_table("timezones", city="VARCHAR", country_name="VARCHAR", country_iso="VARCHAR", timezone="VARCHAR", la="REAL", lng="REAL",utc_offset = "int")
for city_info in data:
city = city_info["city"]
country_name = city_info["country_name"]
country_iso = city_info["country_iso"]
timezone = city_info["timezone"]
lat = city_info["lat"]
lng = city_info["lng"]
utc_offset = city_info["utc_offset"]
LOGGER.success(f"{country_name} was sucefuly save into the timezone table with the following information: {country_iso} and {timezone}")
db.insert("timezones",city, country_name, country_iso, timezone, lat, lng, utc_offset)
LOGGER.success{f'{country_name} successfully saved to the database.'}
db.close_connection()
def save_to_canada():
db = Database("countries.sqlite")
db.drop_table("CA")
db.add_table("CA",
country_iso="text",
name="text",
advisory_text="text",
visa_info="text")
LOGGER.info('Saving CA table into the databse')
#getting the data from all countries
all_countries = get_all_countries()
countries_data = advisory_canada(all_countries)
#saving the data in db
try:
for country in countries_data:
iso = countries_data[country].get('country-iso')
name = countries_data[country].get('name')
advisory = countries_data[country].get('advisory-text')
visa = countries_data[country].get('visa-info')
LOGGER.info(f'Saving {name} into the CA table')
db.insert("CA", iso, name, advisory, visa)
LOGGER.success(
f'{name} was successfully saved into the CA table with the following table: {advisory}. {visa}'
)
LOGGER.success('CA table was successfully saved into the database')
except Exception as error_msg:
LOGGER.error(
f'An error has occurred while saving the countries into the CA table because of the following error: {error_msg}'
)
db.close_connection()
def save_into_db(data):
# create an an sqlite_advisory object
db = Database("countries.sqlite")
db.drop_table("US")
db.add_table("US",
country_iso="text",
name="text",
advisory_text="text",
visa_info="text")
LOGGER.info('Saving United State table into the database')
try:
for country in data:
iso = data[country].get('country-iso')
name = data[country].get('name')
advisory = data[country].get('advisory-text')
visa = data[country].get('visa-info')
LOGGER.info(f"Saving {name} into the US table")
db.insert("US", iso, name, advisory, visa)
LOGGER.info(
f"{name} was succesfully saved into the US table with the following information: {visa}. {advisory}"
)
LOGGER.success('US table has been successfully saved into the databse')
except Exception as error_msg:
LOGGER.error(
f'Error has occured while saving the countries into the US table because of the following error: {error_msg}'
)
db.close_connection()
#save_to_united_states()
def save_to_MU():
LOGGER.info(f'Saving and parsing Mauritius into the databse')
driver = create_driver()
LOGGER.info('Begin parsing for Mauritius advisory')
try:
wiki_visa_url = wiki_visa_url_MU
wiki_visa_ob = wiki_visa_parser(wiki_visa_url, driver)
visas = wiki_visa_ob.visa_parser_table()
LOGGER.success(
'Parsing for Mauritius advisory has been successfully completed')
except Exception as error_msg:
LOGGER.error(
f'Error has occured while parsing for Mauritius advisory because of the following error: {error_msg}'
)
info = {}
array_info = []
# create an an sqlite_advisory object
db = Database("countries.sqlite")
db.drop_table("MU")
db.add_table("MU",
country_iso="text",
name="text",
advisory_text="text",
visa_info="text")
LOGGER.info('Saving Mauritius table into the database')
try:
for country in visas:
iso = find_iso_of_country(country)
if (iso != ""):
name = country
LOGGER.info(f'Saving {name}')
visa = visas[country].get(
'visa') #dictionary for visa info is country{visa:text}
advisory = "Not available yet"
info = {
"country_iso": iso,
"name": name,
"advisory": advisory,
"visa_info": visa
}
array_info.append(info)
print(name, " ", visa, " ", advisory)
db.insert("MU", iso, name, advisory, visa)
LOGGER.success(
f'{name} was sucessfully saved to the database with the following information: {visa}. {advisory}.'
)
LOGGER.success(
'Mauritius table successfully saved to the database')
except Exception as error_msg:
LOGGER.error(
f'An error has occured while saving Mauritius table to the database because of the following error: {error_msg}'
)
db.close_connection()
quit_driver(driver)
with open('./advisory-mu.json', 'w') as outfile:
json.dump(array_info, outfile)
def save_to_UK():
LOGGER.info("Begin parsing and saving for United Kingdom table...")
driver = create_driver()
LOGGER.info('Parsing the visa requirements of all countries for United Kingdom advisory')
try:
wiki_visa_url ="https://en.wikipedia.org/wiki/Visa_requirements_for_British_citizens"
wiki_visa_ob = wiki_visa_parser(wiki_visa_url,driver)
visas = wiki_visa_ob.visa_parser_table()
data = parse_all_countries_advisory()
LOGGER.success('Successfully parsed the visa requirements of all countries for United Kingdom advisory')
except Exception as error_msg:
LOGGER.error(f'An error has occured while retrieving the visa reuirements of all countries for United Kingdom advisory because of the following error: {error_msg}')
info = {}
array_info = []
# create an an sqlite_advisory object]
db = Database("countries.sqlite")
db.drop_table("GB")
db.add_table("GB", country_iso="text", name="text", advisory_text="text", visa_info="text")
LOGGER.info('Saving countries informations into the UK table')
try:
for country in visas:
iso = find_iso_of_country(country)
if(iso != ""):
try:
name = country
advisory = data[iso].get('advisory') #dictionary for the travel advisory is iso{advisory:text}
visa_info = visas[country].get('visa') #dictionary for visa info is country{visa:text}
info = {
"country_iso" : iso,
"name": name,
"advisory": advisory,
"visa_info": visa_info
}
array_info.append(info)
LOGGER.success(f"Saving {name} into the UK table with the following information: {visa_info}. {advisory}")
db.insert("GB",iso,name,advisory,visa_info)
LOGGER.success(f'{name} sucesfully saved to the database.')
except KeyError:
LOGGER.warning(f'This country doesn\'t have advisory info: {country}')
print("This country doesn't have advisory info: ",country)
LOGGER.info(f'Its ISO is {iso}')
print("Its ISO is: ",iso)
LOGGER.success('All countries have been succesfully saved into the UK table')
except Exception as error_msg:
LOGGER.error(f'An error has occured while saving countries into the UK table because of the following: {error_msg}')
db.close_connection()
with open('./advisory-uk.json', 'w') as outfile:
json.dump(array_info, outfile)
def get_products(self) -> dict: """ Method to read and return the shopping data from a SQLite file database :param :return: dict """ products = dict() db = Database() db.create_connection(self._file_path) rows = db.get_products() db.close_connection() for row in rows: if row[0] not in products: try: products[row[0]] = Product(row[0], row[1], row[2], row[3]) # code, price, lastupdate, currency except Exception as e: # IF the database was not correct parsed, the item will be discarted, # the event will be logged in the log file and the program will continue logging.error(str(datetime.now())+': ' + e) continue return products
def save_to_SG():
LOGGER.info(f'Saving Singapore into the databse')
driver = create_driver()
LOGGER.info(
'Parsing visa requirments for all countries into the Singapore table')
try:
wiki_visa_url = wiki_visa_url_SG
wiki_visa_ob = wiki_visa_parser(wiki_visa_url, driver)
visas = wiki_visa_ob.visa_parser_table()
LOGGER.success(
'Visa requirements have been succesfully parsed for the Singapore table'
)
except Exception as error_msg:
LOGGER.error(
f'An error has occured whilse parsing for visa requirements because of the following error: {error_msg}'
)
advisories = parse_all_countries_advisories()
array_info = []
# create an an sqlite_advisory object
db = Database("countries.sqlite")
db.drop_table("SG")
db.add_table("SG",
country_iso="text",
name="text",
advisory_text="text",
visa_info="text")
array_info = save_info(db, visas, advisories, array_info)
db.close_connection()
LOGGER.success(f'Singapore was sucesfully saved to the database')
quit_driver(driver)
with open('./advisory-sg.json', 'w') as outfile:
json.dump(array_info, outfile)
if __name__ == '__main__':
db = Database()
db.create_connection("../tests/data.db")
sql_create_table = """CREATE TABLE IF NOT EXISTS products (
code text PRIMARY KEY,
price real NOT NULL,
lastupdate text,
currency text
);"""
db.create_table(sql_create_table)
product_a = ('a', 1.0, '10-09-2019', 'EUR')
product_b = ('b', 1.1, '10-09-2019', 'EUR')
product_c = ('c', 1.173, '10-09-2019', 'EUR')
product_d = ('d', 2.233, '10-09-2019', 'EUR')
product_e = ('e', 3.0, '10-09-2019', 'EUR')
#create products
print(db.add_product(product_a))
print(db.add_product(product_b))
print(db.add_product(product_c))
print(db.add_product(product_d))
print(db.add_product(product_e))
print(db.get_products())
db.close_connection()
