def create_wb(filtername=""):
# create folder if not present
if not os.path.isdir(xlsx_hlp.folder_name):
os.mkdir(xlsx_hlp.folder_name)
xlsx_hlp.current = os.path.join(
xlsx_hlp.folder_name, xlsx_hlp.filename + filtername + ".xlsx")
# if this file is already present
if os.path.isfile(xlsx_hlp.current):
xlsx_hlp.open_wb()
else:
# create excel object with current file name
xlsx_hlp.wb = xlsxwriter.Workbook(xlsx_hlp.current)
xlsx_hlp.wb = xlsxwriter.Workbook(xlsx_hlp.current)
xlsx_hlp.ws_main = xlsx_hlp.wb.add_worksheet('main')
xlsx_hlp.row_num_main = 1
xlsx_hlp.ws_comp = xlsx_hlp.wb.add_worksheet('compétences')
xlsx_hlp.row_num_comp = 1
xlsx_hlp.ws_lang = xlsx_hlp.wb.add_worksheet('lang')
xlsx_hlp.row_num_lang = 1
xlsx_hlp.ws_atouts = xlsx_hlp.wb.add_worksheet('atouts')
xlsx_hlp.row_num_atouts = 1
xlsx_hlp.ws_nb_moments = xlsx_hlp.wb.add_worksheet(
'nb_moments_cles')
xlsx_hlp.row_num_nb_moments = 1
xlsx_hlp.set_all_headers()
xlsx_hlp.wb.close()
scr_hlp.pause_if_EXTRADEBUG("New File created")
xlsx_hlp.open_wb()
def get_last_page():
if not os.path.isfile(Program_File):
return 0
f = open(Program_File, "r")
page = int(f.readline())
scr_hlp.pause_if_EXTRADEBUG(f"Got Last Page = {page}")
f.close()
return page
def open_wb():
scr_hlp.print_if_DEBUG("Opening an existing sheet named = %s" %
xlsx_hlp.current)
wbRD = xlrd.open_workbook(xlsx_hlp.current)
sheets = wbRD.sheets()
xlsx_hlp.wb = xlsxwriter.Workbook(xlsx_hlp.current)
for sheet in sheets: # write data from old file
if sheet.name == 'main':
ws = xlsx_hlp.ws_main = xlsx_hlp.wb.add_worksheet(sheet.name)
xlsx_hlp.row_num_main = sheet.nrows
scr_hlp.print_if_DEBUG("%s was having %i rows prevously." %
(sheet.name, sheet.nrows - 1))
elif sheet.name == 'compétences':
ws = xlsx_hlp.ws_comp = xlsx_hlp.wb.add_worksheet(sheet.name)
xlsx_hlp.row_num_comp = sheet.nrows - 1
scr_hlp.print_if_DEBUG("%s was having %i rows prevously." %
(sheet.name, sheet.nrows - 1))
elif sheet.name == 'lang':
ws = xlsx_hlp.ws_lang = xlsx_hlp.wb.add_worksheet(sheet.name)
xlsx_hlp.row_num_lang = sheet.nrows - 1
scr_hlp.print_if_DEBUG("%s was having %i rows prevously." %
(sheet.name, sheet.nrows - 1))
elif sheet.name == 'atouts':
ws = xlsx_hlp.ws_atouts = xlsx_hlp.wb.add_worksheet(sheet.name)
xlsx_hlp.row_num_atouts = sheet.nrows - 1
scr_hlp.print_if_DEBUG("%s was having %i rows prevously." %
(sheet.name, sheet.nrows - 1))
elif sheet.name == 'nb_moments_cles':
ws = xlsx_hlp.ws_nb_moments = xlsx_hlp.wb.add_worksheet(
sheet.name)
xlsx_hlp.row_num_nb_moments = sheet.nrows - 1
scr_hlp.print_if_DEBUG("%s was having %i rows prevously." %
(sheet.name, sheet.nrows - 1))
else:
raise NameError("Some Problem in Excel file")
for row in range(sheet.nrows):
for col in range(sheet.ncols):
ws.write(row, col, sheet.cell(row, col).value)
scr_hlp.pause_if_EXTRADEBUG("Existing sheet opened")
def does_id_exists_in_DB(profile_id):
if not DB.SAVE_ON:
scr_hlp.print_if_DEBUG(
"DB.SAVE_ON is False. So program is not fetching data")
return
conn = psycopg2.connect(user=pg_user,
password=pg_pass,
host=pg_host,
port=pg_port,
database=pg_db)
cur = conn.cursor()
cur.execute(f"select id from main where id={profile_id}")
count = cur.rowcount
cur.close()
conn.close()
scr_hlp.print_if_DEBUG(f"Checking if profile id: {profile_id} exists?")
if count != 0:
scr_hlp.pause_if_EXTRADEBUG("profile exists.")
return True
scr_hlp.pause_if_EXTRADEBUG("profile not exists")
return False
def addtoDB(columns_lst, values_lst, dtable):
if not DB.SAVE_ON:
scr_hlp.print_if_DEBUG(
"DB.SAVE_ON is False. So program is not saving data")
return
conn = psycopg2.connect(user=pg_user,
password=pg_pass,
host=pg_host,
port=pg_port,
database=pg_db)
cur = conn.cursor()
columns = ", ".join(columns_lst)
values = ", ".join([f"'{replaceq(v)}'" for v in values_lst])
insert_q = f"insert into {dtable} ({columns}) values({values});"
cur.execute(insert_q)
count = cur.rowcount
conn.commit()
scr_hlp.pause_if_EXTRADEBUG(
f"{count} Record inserted successfully into mobile table")
cur.close()
conn.close()
def save_wb():
xlsx_hlp.wb.close()
scr_hlp.pause_if_EXTRADEBUG("Excel file saved")
xlsx_hlp.open_wb()
lieux = [
'&lieux=91',
'&lieux=77',
'&lieux=75',
]
secteursActivite = [
'§eursActivite=101753',
'§eursActivite=101755',
'§eursActivite=101757',
]
list_page_URL = "https://www.apec.fr/recruteur/mon-espace/candidapec.html#/rechercheNormale?page=%s"
if not os.path.isdir(scr_hlp.get_dwnload_dir_path()):
os.mkdir(scr_hlp.get_dwnload_dir_path())
scr_hlp.pause_if_EXTRADEBUG("Download dir created")
for fonc in Fonctions:
for loc in lieux:
for sec in secteursActivite:
page_num = 0
list_page_URL = list_page_URL + fonc + loc + sec
xlsx_hlp.create_wb(fonc + loc + sec)
scr_hlp.start_chrome()
scr_hlp.load_page(list_page_URL)
sleep(2)
scr_hlp.click_element(
"//button[@class='optanon-allow-all accept-cookies-button']")
while True:
scr_hlp.pause_if_EXTRADEBUG("Starting job with page_num = %i" %
'&lieux=75',
]
secteursActivite = [
'§eursActivite=101753',
'§eursActivite=101755',
'§eursActivite=101757',
]
list_page_URL = "https://www.apec.fr/recruteur/mon-espace/candidapec.html#/rechercheNormale?page=%s"
scr_hlp.list_page_URL = list_page_URL
Program_File = "program_data"
if not os.path.isdir(scr_hlp.get_dwnload_dir_path()):
os.mkdir(scr_hlp.get_dwnload_dir_path())
scr_hlp.pause_if_EXTRADEBUG("Download dir created")
def get_last_page():
if not os.path.isfile(Program_File):
return 0
f = open(Program_File, "r")
page = int(f.readline())
scr_hlp.pause_if_EXTRADEBUG(f"Got Last Page = {page}")
f.close()
return page
def save_last_page(page):
f = open(Program_File, "w")
f.write(str(page))