def main():
rs.login()
conn = db.db_connect(SQLVars.DATABASE.value)
exists = db.check_if_table_exists(conn, SQLVars.TICKERS.value)
if not exists:
sql_create_tickers_table = """ CREATE TABLE IF NOT EXISTS existing_tickers (
ticker text,
first_dt text
) """
db.execute_query(conn, sql_create_tickers_table)
current_symbols = db.get_col_from_table(conn, TickersColumns.TICKER.value,
SQLVars.TICKERS.value)
current_symbols = [
i[getIndexOfEnum('ticker', TickersColumns)] for i in current_symbols
]
stocks_to_check = ['IPO' + chr(i + ord('a')).upper() for i in range(26)]
stocks_to_check_filtered = list(
set(stocks_to_check) - set(current_symbols))
curr_date = datetime.datetime.today().strftime('%Y-%m-%d')
new_tickers = []
for symbol in stocks_to_check_filtered:
quotes = rs.get_quote(symbol)
if quotes:
new_tickers.append((symbol, curr_date))
order_by_dollars(symbol, 1000)
db.insert_into_table(conn, SQLVars.TICKERS.value, new_tickers)
rs.logout()
def test_drop_collections(self):
"""test building a table, check if it exists,
then drop it and make sure it reuturns nothing"""
database = database_setup()
test_dicts = [{
'person': 'Maveric'
}, {
'person': 'Charlie'
}, {
'person': 'Iceman'
}, {
'person': 'Goose'
}, {
'person': 'Viper'
}, {
'person': 'Jester'
}, {
'person': 'Couger'
}, {
'person': 'Wolfman'
}, {
'person': 'Slider'
}]
insert_into_table('test', test_dicts)
for test_value in test_dicts:
query = database.test.find(filter={"person": test_value['person']},
projection={'_id': False})
self.assertEqual([values["person"] for values in query][0],
test_value['person'])
drop_collections()
query = database.test.find()
self.assertEqual([values for values in query], [])
def run(self):
while not self.queue.empty() or active_count() > 0:
stored_data = self.queue.get()
if is_not_phone_exists(stored_data[0]):
logger.info(current_thread().name
+ " - Found unique number. Storing info in db..."
+ stored_data[2])
insert_into_table(*stored_data)
else:
logger.debug('Already in the base. Skip...')
self.queue.task_done()
logger.info(current_thread().name + " run finished")
def test_insert_into_table(self):
"""takes json and sticks into database"""
database = database_setup()
test_dicts = [{
'person': 'Maveric'
}, {
'person': 'Charlie'
}, {
'person': 'Iceman'
}, {
'person': 'Goose'
}, {
'person': 'Viper'
}, {
'person': 'Jester'
}, {
'person': 'Couger'
}, {
'person': 'Wolfman'
}, {
'person': 'Slider'
}]
insert_into_table('test', test_dicts)
for test_value in test_dicts:
query = database.test.find(filter={"person": test_value['person']},
projection={'_id': False})
self.assertEqual([values["person"] for values in query][0],
test_value['person'])
database.test.drop()
self.assertEqual(insert_into_table('test', [None]), 1)
database.test.drop()
import database as db
import station_names
import process_data
import get_data
import os
# set up connection to the database
# edit this when working on the server
db.set_up_connection(db.db, 'weather')
# insert stations
stations_df = station_names.get_stations_dataframe()
db.insert_into_table(stations_df, 'Station')
# get daily measurement data
userpath = os.path.dirname(os.path.realpath(__file__))
#get_data.get_data(userpath, historical=True, recent=True,
# hourly=False, verbose=True)
# insert measurement data
print('inserting measurement data into the database...')
for i, s_id in enumerate(stations_df.index):
try:
mes = process_data.process_data(userpath, s_id, 'daily')
except BaseException as e:
print('something went wrong processing station: {}'.format(s_id))
print(e)
else:
db.insert_into_table(mes, 'DailyMeasurement')
print('{}: {}'.format(i, s_id))
}).text
except AttributeError:
hotel_dict["Rating"] = None
amenities = hotel_card.find("div", {"class": "amenityWrapper"})
amenities_list = []
for amenity in amenities.findAll("div",
{"class": "amenityWrapper__amenity"}):
amenities_list.append(
amenity.find("span", {
"class": "d-body-sm"
}).text.strip())
hotel_dict["Amenities"] = ', '.join(amenities_list[:-1])
scraped_info_list.append(hotel_dict)
#insert the data into the table
#a row can be inserted in a table only if it is a tuple
database.insert_into_table(dbname, tuple(hotel_dict.values()))
dataframe = pandas.DataFrame(scraped_info_list)
dataframe.sort_values(by="Price", inplace=True)
dataframe.reset_index(drop=True, inplace=True)
print("Creating csv file..")
dataframe.to_csv("hotels.csv")
print("Data in table hotels:")
database.get_table_data(dbname)
for link in all_links_list:
driver.get(link)
show_phone_number = driver.find_elements_by_css_selector(
SHOW_PHONE_NUMBER_CSS)
if show_phone_number:
show_phone_number[1].click()
if show_phone_number[1].get_attribute('style') != PHONE_NUMBER_VISIBLE:
WebDriverWait(driver, 20).until(
ElementHasAttribute((By.CSS_SELECTOR, SHOW_PHONE_NUMBER_CSS),
'style', PHONE_NUMBER_VISIBLE))
phone_number = driver.find_elements_by_css_selector(
PHONE_NUMBER_CSS)[1].text
if is_not_phone_exists(phone_number):
profile_link = driver.find_element_by_css_selector(
USER_NAME_CSS).get_attribute('href')
user_name = driver.find_element_by_css_selector(
USER_NAME_CSS).text.strip()
price = driver.find_element_by_css_selector(PRICE_CSS).text
info_line = driver.find_element_by_class_name(
USER_SINCE_CLASS).text
user_address = driver.find_element_by_css_selector(
USER_ADDRESS_CSS).text
insert_into_table(phone_number, user_name, link, price,
profile_link, info_line, user_address)
driver.close()
import database as db
import station_names
import process_data
import get_data
import os
# set up connection to the database
# edit this when working on the server
db.set_up_connection(db.db, 'bence_test', create_tables=True)
# insert stations
stations_df = station_names.get_stations_dataframe()
db.insert_into_table(stations_df, 'Station')
# get daily measurement data
#userpath = os.path.dirname(os.path.realpath(__file__))
userpath = '/local/data_dwd/october2018'
#get_data.get_data(userpath, historical=True, recent=True,
# hourly=False, verbose=True)
# insert measurement data
with db.porm.db_session:
print('inserting measurement data into the database...')
for i, s_id in enumerate(stations_df.index):
try:
mes = process_data.process_data(userpath, s_id, 'daily')
except BaseException as e:
print('something went wrong processing station: {}'.format(s_id))
print(e)
else:
if not mes.empty:
def subscribe(message):
cid = message.chat.id
user = message.from_user
db.insert_into_table(cid, user)
bot.send_message(cid, cfg.subscribe_msg)
else:
display_mails = ''
# disable mail generation
# if site:
# raw_mails = (generation_mails(site))
# info += 'Generation; '
# ------------------------- Send Mails --------------------------
# raw_mails = [raw_mails[0]]
message = ''
subject = ''
for mail in raw_mails:
if is_not_mail_exist(mail) and check_mail(mail):
insert_into_table(mail, hotel)
with open('mail_message.msg', encoding='utf-8') as f:
message = f.read().format(mail=mail, site=site)
with open('mail_subject.msg', encoding='utf-8') as f:
subject = f.read().format(name=hotel)
gen_message_in_file('{}_{}'.format(count, hotel), mail, subject,
message)
# send_mail(subject, message)
# send_mail(subject, message, mail)
info += 'Sent; '
else:
info += 'Exists; '
# ------------------------ Write Record -------------------------
def insert_df(table_name, df):
db.insert_into_table(df, table_name, auto_id=True)
phone_numbers = driver.find_elements(*RIA_NUMBER)
numbers = []
for number in phone_numbers:
if number.tag_name == 'span':
numbers.append(number.get_attribute('data-phone-number'))
numbers = '\n'.join(numbers)
except NoSuchElementException:
logger.debug(f'No numbers found in link {ticket}.')
continue
if is_not_phone_exists(numbers):
logger.info(
f'Found unique number. Gathering info from link {ticket}...')
name = driver.find_element(*RIA_NAME).text
address = driver.find_element(*RIA_ADDRESS).text
price = driver.find_element(*RIA_PRICE).text
try:
profile = driver.find_element(*RIA_PROFILE) \
.find_element_by_tag_name('a').get_attribute('href')
except NoSuchElementException:
profile = None
info = driver.find_element(*RIA_DESCRIPTION).text
link = driver.current_url
insert_into_table(numbers, name, link, price, profile, info, address)
logger.info(f'Information from {ticket} was stored in db.')
else:
logger.debug('Already in the base. Skip...')
driver.close()