def close_connection(self, del_cursors: bool = False) -> None:
""" Method to close connection to this database.
Parameters:
del_cursors (bool): if True, first delete dependent cursors,
if False, refuse to close connection.
Returns:
"""
if del_cursors:
# TODO need to test with multiple DBClients.
for caller, cursor in self.callers.items():
self.delete_cursor(caller)
del caller
else:
print('Dependent DBClients exist, will not close connection.')
if self.db_type in c.FILE_DATABASES:
z = '\n{} from database at "{}".'
z = z.format('{}', self.db_path)
else:
z = '\n{} from instance "{}" on host "{}".'
z = z.format('{}', self.instance, self.hostname)
try:
self.connection.close()
self.connection = None
print(z.format('Successfully disconnected'))
except self.db_lib_obj.Error:
print_stacktrace()
print(z.format('Failed to disconnect'))
# Nothing to clean up.
exit(1)
return
def __init__(self,
out_file_name: str = '',
align_col: bool = True,
col_sep: str = ',') -> None:
""" Constructor method for this class.
Parameters:
out_file_name (str): relative or absolute path to output file, or '' for standard output.
align_col (bool): if True, pad columns with spaces in the output so they always have the same width.
col_sep (str): character(s) to separate columns in output. Common choices:
"" (no characters)
" " (single space, aka chr(32))
chr(9) (aka the horizontal tab character)
"|"
","
Returns:
"""
out_file = None
if out_file_name == '':
out_file = sys.stdout
else:
try:
out_file = open(out_file_name, 'w')
except OSError:
print_stacktrace()
# Can envision situations where exiting might be excessive.
exit(1)
self.out_file_name: str = out_file_name
self.out_file = out_file
self.align_col: bool = align_col
self.col_sep: str = col_sep
return
def get_out_file_name(self) -> None:
""" Prompt for relative or absolute path to output file.
Parameters:
Returns:
"""
prompt = ('\nEnter the name and relative or absolute'
'\nlocation of the file to write output to.'
'\nOr hit Return to print to the standard output:\n')
# Keep looping until able to open output file, or "" entered.
while True:
out_file_name = input(prompt).strip()
if out_file_name == '':
out_file = sys.stdout
break
else:
dir_name = dirname(out_file_name)
if isdir(dir_name):
try:
out_file = open(out_file_name, 'w')
break
except OSError:
print_stacktrace()
else:
print('Invalid directory specified: ' + dir_name)
self.out_file = out_file
self.out_file_name = out_file_name
if self.out_file_name == '':
print('You chose to write to the standard output.')
else:
print('Your output file is "{}".'.format(self.out_file_name))
return
def __init__(self,
os: str,
db_type: str,
db_path: str,
username: str,
password: str,
hostname: str,
port_num: int,
instance: str) -> None:
""" Constructor method for this class.
Parameters:
os (str): the OS on which this program is executing.
db_type (str): the type of database (Oracle, SQL Server, etc).
db_path (str): the path of the database file, for SQLite and Access.
username (str): the username for the connection to this database.
password (str): the password for "username".
hostname (str): the hostname of this database.
port_num (int): the port this database listens on.
instance (str): the name of this database instance.
Returns:
"""
# Save arguments of __init__.
self.os: str = os
self.db_type: str = db_type
self.db_path: str = db_path
self.username: str = username
self.password: str = password
self.hostname: str = hostname
self.port_num: int = port_num
self.instance: str = instance
# Check if db_type valid.
if self.db_type not in c.DB_TYPES:
print('Invalid database type "{}".'.format(self.db_type))
# Nothing to clean up.
exit(1)
# Import appropriate database library.
self.db_lib_name = c.LIB_NAME_FOR_DB[self.db_type]
self.db_lib_obj = __import__(self.db_lib_name)
# Appropriate database client executable.
self.db_client_exe = c.DB_CLIENT_EXES[self.db_type]
# Get database library version.
if self.db_lib_name in {c.PSYCOPG2, c.PYMYSQL}:
self.db_lib_version = self.db_lib_obj.__version__
else:
self.db_lib_version = self.db_lib_obj.version
z = 'Using {} library version {}.'
print(z.format(self.db_lib_name, self.db_lib_version))
# Get the library's primary parameter style.
print('Parameter style "{}".'.format(self.db_lib_obj.paramstyle))
# paramstyle = 'named': cx_Oracle. Option for sqlite3 & psycopg2.
# paramstyle = 'qmark': sqlite3 and pyodbc.
# paramstyle = 'pyformat': pymysql and psycopg2.
# Get the parameter style we're using.
self.paramstyle = c.PARAMSTYLE_FOR_LIB[self.db_lib_name]
if self.db_type == ACCESS:
self.paramstyle = c.NOBINDVARS
# Initialize bind_vars.
if self.paramstyle in {c.NAMED, c.PYFORMAT}:
self.bind_vars = dict()
elif self.paramstyle == c.QMARK:
self.bind_vars = tuple()
else:
self.bind_vars = None
# Connect to database instance.
self.connection = None
try:
if db_type in c.USES_CONNECTION_STRING:
z = self.get_db_connection_string()
self.connection = self.db_lib_obj.connect(z)
else:
args = (self.hostname, self.username, self.password,
self.instance, self.port_num)
self.connection = self.db_lib_obj.connect(*args)
print('Successfully connected to database.')
except self.db_lib_obj.Error:
print_stacktrace()
print('Failed to connect to database.')
# Nothing to clean up.
exit(1)
# Get database software version.
self.db_software_version = self.get_db_software_version()
# Prepare to save cursors for this connection.
self.callers = dict()
return
def main():
""" Function main. Watch for new online jobs on Wyzant.com.
Parameters:
Returns:
"""
do_beep = f.get_boolean("Beep when new job found? ")
do_email = f.get_boolean("Email when new job found? ")
do_log = f.get_boolean("Save activity to log? ")
print_jobs = f.get_boolean("Print jobs? ")
while True:
num_pages = input("Number of job pages to read (1 or 2)? ")
if num_pages in {"1", "2"}:
num_pages = int(num_pages)
break
# On Exception, come back to here and re-initialize everything.
while True:
try:
# Jobs dicts, stores info about job listings.
jobs = Jobs()
jobs_prev = Jobs()
# Check the version of chromedriver.exe, and update when needed.
# Check_Chromedriver.driver_mother_path = r"C:\Program Files\WebDrivers"
# Check_Chromedriver.main()
# stdout.write("Done checking the version of chromedriver.exe.\n\n")
# Open output file for appending.
if do_log:
outfile = open('log.txt', 'a')
else:
outfile = None
stdout.write("Initializing Selenium.\n")
my_selenium = MySelenium()
stdout.write("Done initializing Selenium.\n")
stdout.write("Logging into Wyzant.\n")
my_selenium.website_login(c.USERNAME, c.PASSWORD, c.LOGIN_PAGE_URL,
c.PRE_LOGIN_PAGE_TITLE,
c.POST_LOGIN_PAGE_TITLE,
c.USERNAME_FIELD_XPATH,
c.PASSWORD_FIELD_XPATH,
c.LOGIN_BUTTON_XPATH)
stdout.write("Done logging into Wyzant.\n")
stdout.write("Going to the Wyzant job listings page.\n")
my_selenium.go_to_web_page(c.JOBS_PAGE_URL, By.CLASS_NAME,
c.UI_PAGE_LINK)
stdout.write("At Wyzant job listings page.\n")
xpath = "//label[@for='lesson_type_online']"
my_selenium.click_sleep_wait(xpath, c.SLEEP_TIME, By.CLASS_NAME,
c.UI_PAGE_LINK)
stdout.write("Fetched Wyzant jobs list.\n")
# Loop forever.
while True:
my_selenium.force_refresh(By.CLASS_NAME, c.UI_PAGE_LINK)
# Save jobs into jobs_prev. Skip if job_ids empty due to faulty page load.
if jobs.count_jobs() > 0:
jobs_prev = deepcopy(jobs)
jobs.reset()
# Print and write to log file the current datetime.
date_time = datetime.now().strftime("%Y/%m/%d %H:%M:%S")
stdout.write(date_time + " ")
if do_log:
outfile.write(date_time + "\n")
outfile.flush()
for page_num in range(1, num_pages + 1):
if num_pages == 2:
# Click back and forth between pages 1 and page 2.
xpath = f'//div[@role="navigation"]/a[text()="{page_num}"]'
pages = my_selenium.find_elements_by_xpath(xpath)
# print("selecting: " + pages[0].text)
pages[0].click()
# Each instance of class "academy-card" contains 1 job, 10 visible per page.
academy_cards = my_selenium.get_all_related_by_class(
"academy-card")
for card_num, card_obj in enumerate(academy_cards):
# Get Job listing URL.
job_url = card_obj.find_element_by_xpath(
'./h3/a').get_attribute('href')
card_num_display = 10 * (page_num - 1) + card_num
# Save job properties.
params = dict()
params[c.JOB_ID] = int(job_url.split("/")[-1].strip())
params[c.CARD_NUMBER] = card_num_display
job_age_info = card_obj.find_element_by_xpath(
'./div[1]/span[1]').text.strip()
if job_age_info == "No applications yet":
params[c.APPLICATIONS] = "N"
job_age_info = card_obj.find_element_by_xpath(
'./div[1]/span[2]').text.strip()
else:
params[c.APPLICATIONS] = "Y"
params[c.JOB_AGE] = f.age_to_minutes(job_age_info)
params[
c.STUDENT_NAME] = card_obj.find_element_by_xpath(
'./p[1]').text.strip()
params[c.JOB_TOPIC] = card_obj.find_element_by_xpath(
'./h3/a').text.strip()
pay_rate = card_obj.find_element_by_xpath(
'./div[3]/span/div/div[1]/span').text.strip()
params[c.PAY_RATE] = pay_rate.replace(
"Recommended rate: ", "")
params[
c.
JOB_DESCRIPTION] = card_obj.find_element_by_xpath(
'./p[2]').text.strip()
# Does "Show Details" control exist?
show_details = card_obj.find_elements_by_xpath(
'./div[4]/div/div/p')
if len(show_details) == 1:
# If "Show Details" exists, click it.
show_details[0].click()
# Each instance of class "spc_zero" contains one job attribute.
spc_zeros = card_obj.find_elements_by_class_name(
"spc-zero")
# Iterate over all job attributes in class "spc_zero".
for spc_zero in spc_zeros:
# There are 1-2 children of class "spc_zero".
children = spc_zero.find_elements_by_xpath(
'./child::*')
if len(children) == 2:
# Job attribute in 2nd child of class "spc_zero".
value = spc_zero.find_element_by_xpath(
'./span[2]').text.strip()
else:
# Sometimes the job availability attribute isn't the 2nd child of class "spc_zero".
xpath = './../p[@class="text-semibold spc-tiny"]'
items = spc_zero.find_elements_by_xpath(
xpath)
value = "; ".join(
[item.text for item in items]).strip()
# Job attribute in 1st child of class "spc_zero".
my_key = spc_zero.find_element_by_xpath(
'./span[1]').text
my_key = my_key.replace(":", "").strip()
params[my_key] = value
# Done iterating over all job attributes in class "spc_zero".
# Save job properties in new instance of class Jobs.
jobs.add_job(**params)
# Print progress, on just one line.
if card_num_display == 0:
stdout.write(
f"Done fetching job {card_num_display}")
else:
stdout.write(f", {card_num_display}")
# Done iterating over academy_cards.
# Done iterating over pages.
# After stdout.write, need to add newline.
stdout.write("\n")
# Look for new jobs.
# Get job IDs in job_ids and not in job_ids_prev.
current_num = jobs.count_jobs()
previous_num = jobs_prev.count_jobs()
# Skip if job_ids or job_ids_prev has too few entries (1st loop or faulty page load).
if current_num <= 10 * (num_pages - 1):
stdout.write(f"Current # Job IDs: {current_num}.\n")
elif previous_num <= 10 * (num_pages - 1):
stdout.write(f"Previous # Job IDs: {previous_num}.\n")
else:
job_ids_previous = jobs_prev.get_job_ids()
new_job_ids = jobs.get_new_job_ids(job_ids_previous)
# Iterate over all new job listings.
for job_id in new_job_ids:
# Collect job data.
email_subject = f"New job at www.wyzant.com/tutor/jobs/{job_id}"
job_summary, job_data, age = jobs.get_job_data(
email_subject + "\n", job_id)
if age <= 120:
# Make audible tone.
if do_beep:
Beep(6000, 1000)
# Send email.
if do_email:
f.send_email(c.SMTP_SERVER,
c.SMTP_PORT,
c.SMTP_PASSWORD,
c.EMAIL_SENDER,
c.EMAIL_RECIPIENT,
subject=email_subject,
body=job_data)
# Print the job data, write job summary to log file.
stdout.write(job_data)
if do_log:
outfile.write(job_summary + "\n")
outfile.flush()
# Done iterating over new_job_ids.
if print_jobs and jobs is not None:
stdout.write("BEGIN PRINTING JOBS.\n")
stdout.write(str(jobs) + '\n')
stdout.write("DONE PRINTING JOBS.\n")
# Wait some more, so that jobs page polled about every 30 seconds.
sleep(20)
# End of inner while loop.
except Exception:
# Print exception.
f.print_stacktrace()
# Close log file.
if do_log:
outfile.flush()
outfile.close()
# Make audible tone.
if do_beep:
Beep(1000, 1000)
# Wait, in case of a web glitch.
sleep(c.SLEEP_TIME)
def run_sql(self) -> (list, list, int):
""" Run the SQL, perhaps return rows and column names.
Parameters:
Returns:
For SQL SELECT:
col_names: list of the names of the columns being fetched.
all_rows: list of tuples, each tuple is one row being fetched.
row_count: number of rows fetched.
For other types of SQL:
list()
list()
row_count: number of rows affected.
"""
col_names = list()
all_rows = list()
row_count = 0
if not self.sql:
print('NO SQL TO EXECUTE.')
self.clean_up()
exit(1)
else:
try:
# Execute SQL.
if len(self.bind_vars) > 0:
if self.db_type == ACCESS:
print('NO BIND VARIABLES ALLOWED IN MICROSOFT ACCESS.')
self.clean_up()
exit(1)
self.cursor.execute(self.sql, self.bind_vars)
else:
self.cursor.execute(self.sql)
# Check for something really yucky.
if self.cursor is None:
print('\nCursor is None.')
self.clean_up()
exit(1)
# Classify SQL.
sql_type: str = self.sql.split()[0].upper()
row_count = self.cursor.rowcount
# Handle SQL results.
if sql_type in {'INSERT', 'UPDATE', 'DELETE'}:
self.cursor.commit()
elif sql_type != 'SELECT':
print('Not a CRUD statement!')
elif sql_type == 'SELECT':
# Fetch rows. Fetchall for large number of rows a problem.
all_rows = self.cursor.fetchall()
# Get column names
col_names = [item[0] for item in self.cursor.description]
# Column data types. Too specific and inconsistent to use.
# col_types = [item[1] for item in self.cursor.description]
# Could combine all_rows & col_names into dict, with keys
# from col_names & values from all_rows, but performance
# would suffer.
# In Oracle, cursor.rowcount = 0, so get row count directly.
row_count = len(all_rows)
except self.db_library.Error:
print_stacktrace()
finally:
return col_names, all_rows, row_count