Мобильная версияНапример, я просматриваю список строк и обновить каждую строку по отдельности, а затем выполнить фиксацию базы данных для каждого обновления. Хотя этот подход работает, он приводит к большому количеству запросов и фиксаций к базе данных, что кажется неэффективным и в первую очередь увеличивает наши накладные расходы из-за обращений к базе данных
Хотя уведомления по электронной почте отправляются успешно, столбец с напоминанием не обновляется соответствующим образом в базе данных при использовании пакетного подхода. Эта несогласованность означает, что система не отражает правильное состояние после операции.
Я подозреваю, что есть лучший способ массового обновления нескольких строк с меньшим количеством обращений к базе данных, но я не уверен, как это сделать. структурировать такой запрос в SQLAlchemy или правильно его реализовать. Как я могу эффективно обновить столбец с напоминанием для нескольких строк одновременно, чтобы уменьшить количество взаимодействий с базой данных?
вот функция, которую я пробовал:
Код: Выделить всё
def submitted_but_not_placed():
"""
Identify candidates who have been submitted but not placed,
group them by recruiter (via fuzzy matching), notify recruiters, and update the database.
"""
print("Starting 'submitted_but_not_placed' function...")
# Step 1: Get all submissions where `reminded` is False
try:
db.session.expire_all() # ensure we refresh from DB
submissions = Submission.query.filter(Submission.reminded == False).all()
print(f"Submissions fetched: {submissions}")
total_submissions = Submission.query.count()
print(f"Fetched {len(submissions)} submissions where 'reminded' is False.")
#print(f"Total submissions in DB: {total_submissions}")
except Exception as e:
print(f"Error fetching submissions: {e}")
return
# Step 2: Get all active placements (where end_date IS NULL)
try:
active_placements = Placement.query.filter(
Placement.end_date.is_(None)
).all()
print(f"Fetched {len(active_placements)} active placements.")
except Exception as e:
print(f"Error fetching active placements: {e}")
return
# Step 3: Extract candidate IDs from active placements
active_candidate_ids = {placement.candidate_id for placement in active_placements}
print(f"Extracted {len(active_candidate_ids)} active candidate IDs.")
# Step 4: Filter submissions that are not placed (those whose candidate_id not in active_candidate_ids)
unplaced_submissions = [
submission for submission in submissions
if submission.candidate_id not in active_candidate_ids
]
print(f"Filtered {len(unplaced_submissions)} unplaced submissions.")
# -------------------------------------------------------------------
# Step 5: Fuzzy match recruiters to unplaced submissions using ILIKE
# -------------------------------------------------------------------
try:
recruiters = Recruiter.query.all()
print(f"Fetched {len(recruiters)} recruiters.")
except Exception as e:
print(f"Error fetching recruiters: {e}")
return
grouped_by_recruiter = {}
for recruiter in recruiters:
recruiter_name = recruiter.name
recruiter_email = recruiter.username
# Skip recruiters without a valid name or email
if not recruiter_name or not recruiter_email:
print(f"Skipping recruiter with missing name or email.")
continue
# Normalize recruiter name for wildcard matching (use first word in name)
recruiter_name_normalized = recruiter_name.split()[0].strip().lower()
# Query unplaced submissions for this recruiter (reminded=False, not placed, rep ILIKE)
# We’ll do it via a new DB query so it uses the ILIKE filter:
matched_submissions = Submission.query.filter(
Submission.reminded == False,
Submission.candidate_id.notin_(active_candidate_ids),
Submission.rep.ilike(f"%{recruiter_name_normalized}%")
).all()
grouped_by_recruiter[recruiter_name] = {
"email": recruiter_email,
"submissions": matched_submissions
}
# Debugging output: Just show the recruiter name & email
for recruiter_name, data in grouped_by_recruiter.items():
print(f"Recruiter: {recruiter_name}, Email: {data['email']}")
# -------------------------------------------------------------------
# Step 6: Send emails & mark submissions as reminded
# -------------------------------------------------------------------
for recruiter_name, data in grouped_by_recruiter.items():
#recruiter_email = "example"
recruiter_email = data["email"]
print(f"Debug: Recruiter email fetched from data: {data['email']}")
matched_subs = data["submissions"]
# Debugging: Print recruiter details before processing
print(f"Debug: Processing recruiter '{recruiter_name}', Email: {recruiter_email}, Submissions: {len(matched_subs)}")
if not recruiter_email:
print(f"No email found for recruiter {recruiter_name}, skipping.")
continue
print(f"Processing recruiter '{recruiter_name}' with {len(matched_subs)} submissions.")
# Sort the matched_subs list by the candidate's full name
matched_subs = sorted(data["submissions"], key=lambda submission: submission.full_name)
# Format the email message
email_content = f"Submitted But Not Placed Candidates for {recruiter_name}"
email_content += ""
email_content += "Full NameCompanySubmitted On"
for submission in matched_subs: # Iterate over all submissions without enumeration
try:
email_content += (
f""
f"{submission.full_name}"
f"{submission.company}"
f"{datetime.strptime(submission.date, '%Y-%m-%dT%H:%M:%S.%fZ').strftime('%Y-%m-%d')}"
f""
)
# Mark submission as reminded
submission.reminded = True
db.session.commit()
except Exception as e:
db.session.rollback() # Rollback if commit fails
continue
email_content += ""
# Send the email
try:
access_token = get_access_token() # Fetch a new access token
send_email_with_microsoft_graph(
access_token=access_token,
sender=os.getenv("example"), # Sender's email
recipient="example",
#recipient=recruiter_email, # Recruiter's email
subject="Submitted But Not Placed Candidates",
content=email_content,
cc="example" # CC list
)
except Exception as e:
print(f"Error committing database changes: {e}")
# Step 7: Commit changes to the database
try:
db.session.commit()
except Exception as e:
print(f"Error committing database changes: {e}")
Подробнее здесь: https://stackoverflow.com/questions/793 ... abase-hits
