from cvsmanager.models import LanguageProficiency import spacy from spacy.matcher import…
- User
from cvs_manager.models import LanguageProficiency import spacy from spacy.matcher import PhraseMatcher import re from enum import Enum from typing import List, Dict
Load spaCy model
_nlp = spacy.load("en_core_web_sm")
Known languages list (extend as needed)
LANGUAGES = [ "English", "French", "Spanish", "German", "Chinese", "Arabic", "Russian", "Portuguese", "Japanese", "Hindi", "Italian", "Dutch" ]
Proficiency variants mapping to your enum
PROFICIENCY_MAP = { # Native "native": LanguageProficiency.NATIVE, "bilingual": LanguageProficiency.NATIVE, "near native": LanguageProficiency.NATIVE, # Fluent "fluent": LanguageProficiency.FLUENT, "full professional proficiency": LanguageProficiency.FLUENT, "professional": LanguageProficiency.FLUENT, "professional working proficiency": LanguageProficiency.FLUENT, "conversational": LanguageProficiency.FLUENT, # Intermediate "intermediate": LanguageProficiency.INTERMEDIATE, "limited working proficiency": LanguageProficiency.INTERMEDIATE, "working proficiency": LanguageProficiency.INTERMEDIATE, # Basic "basic": LanguageProficiency.BASIC, "elementary": LanguageProficiency.BASIC, "beginner": LanguageProficiency.BASIC, "novice": LanguageProficiency.BASIC, }
PhraseMatcher for languages
matcher = PhraseMatcher(_nlp.vocab, attr="LOWER") patterns = [_nlp.make_doc(lang) for lang in LANGUAGES] matcher.add("LANGUAGE", patterns)
Regex to find proficiency words
PROF_PATTERN = re.compile( r"\b(" + "|".join(re.escape(key) for key in PROFICIENCY_MAP.keys()) + r")\b", re.I )
def normalize_proficiency(prof_text: str) -> LanguageProficiency: if not prof_text: return LanguageProficiency.BASIC prof_lower = prof_text.lower() # Find closest match for key, val in PROFICIENCY_MAP.items(): if key in prof_lower: return val return LanguageProficiency.BASIC
def extract_languages(text: str) -> List[Dict[str, str]]: doc = _nlp(text) matches = matcher(doc) results = []
for match_id, start, end in matches: lang_span = doc[start:end] # Search a window around the language name for proficiency window_start = max(start - 10, 0) window_end = min(end + 10, len(doc)) context = doc[window_start:window_end].text prof_match = PROF_PATTERN.search(context) raw_prof = prof_match.group(1) if prof_match else None results.append({ "name": lang_span.text, "proficiency": normalize_proficiency(raw_prof).value }) # Remove duplicates preferring stronger proficiency unique = {} order = [LanguageProficiency.BASIC, LanguageProficiency.INTERMEDIATE, LanguageProficiency.FLUENT, LanguageProficiency.NATIVE] def prof_rank(prof): try: return order.index(LanguageProficiency(prof)) except ValueError: return 0 for entry in results: key = entry["name"].lower() if (key not in unique) or (prof_rank(entry["proficiency"]) > prof_rank(unique[key]["proficiency"])): unique[key] = entry return list(unique.values())make it also work with french cvs and if LanguageProficiency is not mentioned in cv make it basic by default
- Assistant
To make your
extract_languagesfunction handle French CVs as well as English, you need to:- Extend the
LANGUAGESlist to include French language names (e.g., "Anglais" for English, "Français" for French, etc.). - Extend the
PROFICIENCY_MAPwith French proficiency keywords mapped to the same enum values. - Make sure the default proficiency is BASIC when none is found.
- Maintain the same spaCy pipeline, which is English-centered (
en_core_web_sm) but since French language names and proficiency keywords may also appear in a mixed-language CV (e.g., French CVs with some English terms), the regex and phrase matcher will catch those terms if present. - If you want more accurate French-language processing, you could load
fr_core_news_smfor French text and run a separate pipeline, but for simplicity, extending patterns and regex to match French keywords is usually sufficient.
Updated example to support English & French
from cvs_manager.models import LanguageProficiency import spacy from spacy.matcher import PhraseMatcher import re from typing import List, Dict # Load spaCy English model (could also load French model if needed) _nlp = spacy.load("en_core_web_sm") # Known languages list (extend with French language names) LANGUAGES = [ # English names "English", "French", "Spanish", "German", "Chinese", "Arabic", "Russian", "Portuguese", "Japanese", "Hindi", "Italian", "Dutch", # French names for common languages (add more as needed) "Anglais", "Français", "Espagnol", "Allemand", "Chinois", "Arabe", "Russe", "Portugais", "Japonais", "Hindi", "Italien", "Néerlandais" ] # Proficiency variants mapping with English and French keys PROFICIENCY_MAP = { # Native "native": LanguageProficiency.NATIVE, "bilingual": LanguageProficiency.NATIVE, "near native": LanguageProficiency.NATIVE, "natif": LanguageProficiency.NATIVE, # French "bilingue": LanguageProficiency.NATIVE, # French "quasi natif": LanguageProficiency.NATIVE, # French # Fluent "fluent": LanguageProficiency.FLUENT, "full professional proficiency": LanguageProficiency.FLUENT, "professional": LanguageProficiency.FLUENT, "professional working proficiency": LanguageProficiency.FLUENT, "conversational": LanguageProficiency.FLUENT, "courant": LanguageProficiency.FLUENT, # French "professionnel": LanguageProficiency.FLUENT, # French "niveau professionnel": LanguageProficiency.FLUENT, # French "conversationnel": LanguageProficiency.FLUENT, # French # Intermediate "intermediate": LanguageProficiency.INTERMEDIATE, "limited working proficiency": LanguageProficiency.INTERMEDIATE, "working proficiency": LanguageProficiency.INTERMEDIATE, "intermédiaire": LanguageProficiency.INTERMEDIATE, # French "niveau intermédiaire": LanguageProficiency.INTERMEDIATE, # Basic "basic": LanguageProficiency.BASIC, "elementary": LanguageProficiency.BASIC, "beginner": LanguageProficiency.BASIC, "novice": LanguageProficiency.BASIC, "notions": LanguageProficiency.BASIC, # French 'notions' = basic knowledge "élémentaire": LanguageProficiency.BASIC, # French "débutant": LanguageProficiency.BASIC, # French } # PhraseMatcher for languages matcher = PhraseMatcher(_nlp.vocab, attr="LOWER") patterns = [_nlp.make_doc(lang) for lang in LANGUAGES] matcher.add("LANGUAGE", patterns) # Regex to find proficiency words (case insensitive) PROF_PATTERN = re.compile( r"\b(" + "|".join(re.escape(key) for key in PROFICIENCY_MAP.keys()) + r")\b", re.I ) def normalize_proficiency(prof_text: str) -> LanguageProficiency: if not prof_text: # Default if no proficiency mentioned return LanguageProficiency.BASIC prof_lower = prof_text.lower() # Find closest match by checking keys in prof_lower text for key, val in PROFICIENCY_MAP.items(): if key in prof_lower: return val return LanguageProficiency.BASIC def extract_languages(text: str) -> List[Dict[str, str]]: doc = _nlp(text) matches = matcher(doc) results = [] for match_id, start, end in matches: lang_span = doc[start:end] # Search a window around the language name for proficiency keywords window_start = max(start - 10, 0) window_end = min(end + 10, len(doc)) context = doc[window_start:window_end].text prof_match = PROF_PATTERN.search(context) raw_prof = prof_match.group(1) if prof_match else None results.append({ "name": lang_span.text, "proficiency": normalize_proficiency(raw_prof).value }) # Remove duplicates preferring stronger proficiency unique = {} order = [ LanguageProficiency.BASIC, LanguageProficiency.INTERMEDIATE, LanguageProficiency.FLUENT, LanguageProficiency.NATIVE ] def prof_rank(prof): try: return order.index(LanguageProficiency(prof)) except ValueError: return 0 for entry in results: key = entry["name"].lower() if (key not in unique) or (prof_rank(entry["proficiency"]) > prof_rank(unique[key]["proficiency"])): unique[key] = entry return list(unique.values())
Summary:
- Added French language names to
LANGUAGESlist. - Added French proficiency terms in
PROFICIENCY_MAP. - Default proficiency is always
BASICwhen none found (normalize_proficiencydefaults to it). - The matcher and regex search works regardless of input language by matching known tokens.
- You can add more variants and languages as needed to better cover other languages or synonyms.
If you want to run NLP pipelines specifically in French for those CVs, you could instantiate a separate spaCy French model and run the matcher similarly, then combine the results — but it’s often enough just to catch keywords.
Let me know if you'd like help with that too.
- Extend the
- User
from typing import Optional, Dict from datetime import datetime import re from transformers import pipeline import dateparser
Lazy-loading domain-specific Resume NER pipeline
_NER_MODEL = None
def get_resume_ner_pipeline(): global _NER_MODEL if _NER_MODEL is None: print("[EducationExtractor] Loading resume-specific NER model...") _NER_MODEL = pipeline( "ner", model="ahmedbesbes/edup", tokenizer="ahmedbesbes/edup", aggregation_strategy="simple" ) return _NER_MODEL
def extract_education_from_text(text: str) -> Dict[str, Optional[str]]: ner = get_resume_ner_pipeline() ner_results = ner(text)
degree = extract_degree(text, ner_results) institution = extract_institution(text, ner_results) graduation_date = extract_graduation_date(text, ner_results) return { "degree": degree or "Unknown", "institution": institution or "Unknown", "graduation_date": graduation_date.strftime("%Y-%m-%d") if graduation_date else None }Degree patterns excluding certifications
DEGREE_PATTERNS = [ r"\bBachelor(?:'s)?\b", r"\bBSc\b", r"\bBA\b", r"\bBS\b", r"\bB.Eng\b", r"\bMaster(?:'s)?\b", r"\bMSc\b", r"\bMA\b", r"\bMS\b", r"\bM.Eng\b", r"\bPh.?D\b", r"\bDoctorate\b", r"\bMBA\b", r"\bAssociate\b", r"\bLLB\b", r"\bLLM\b" ]
DEGREE_NORMALIZATION = { "bachelor's": "Bachelor's Degree", "bsc": "Bachelor's Degree", "ba": "Bachelor's Degree", "bs": "Bachelor's Degree", "b.eng": "Bachelor's Degree", "master's": "Master's Degree", "msc": "Master's Degree", "ma": "Master's Degree", "ms": "Master's Degree", "m.eng": "Master's Degree", "ph.d": "PhD", "doctorate": "PhD", "mba": "MBA", "associate": "Associate Degree", "llb": "LLB", "llm": "LLM" }
def extract_degree(text: str, ner_results) -> Optional[str]: for pattern in DEGREE_PATTERNS: match = re.search(pattern, text, re.IGNORECASE) if match: degree_raw = match.group(0).lower() normalized = DEGREE_NORMALIZATION.get(degree_raw, degree_raw.title()) return normalized
degree_entities = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == 'DEGREE'] if degree_entities: return degree_entities[0].title() return Nonedef extract_institution(text: str, ner_results) -> Optional[str]: education_keywords = ["university", "college", "institute", "school", "faculty", "campus"] candidates = [] for ent in ner_results: if ent['entity_group'].upper() in ("INSTITUTION", "ORG"): ent_text = ent['word'] pattern = re.compile(rf".{{0,50}}{re.escape(ent_text)}.{{0,50}}", re.IGNORECASE) nearby = pattern.findall(text) if any(any(kw in snippet.lower() for kw in education_keywords) for snippet in nearby): candidates.append(ent_text)
if candidates: return max(candidates, key=len) regex_inst = re.search(r"(?:University|College|Institute|School|Faculty) of [A-Z][a-zA-Z& ]+", text) if regex_inst: return regex_inst.group(0) return Nonedef extract_graduation_date(text: str, ner_results) -> Optional[datetime]: date_candidates = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == "DATE"] grad_date_patterns = [ r"graduat(?:ed|ion|e) (?:in\s)?(\b\d{4}\b)", r"class of (\d{4})", r"graduation date[:\s]*(\w+\s\d{4}|\d{4})" ] for pattern in grad_date_patterns: match = re.search(pattern, text, re.IGNORECASE) if match: date_str = match.group(1) parsed_date = dateparser.parse(date_str) if parsed_date: return parsed_date
for date_str in date_candidates: parsed_date = dateparser.parse(date_str) if parsed_date: return parsed_date matches = re.findall(r"\b(19|20)\d{2}\b", text) for year in matches: parsed_date = dateparser.parse(year) if parsed_date: return parsed_date return None is this the best model to find education ? - Assistant
The model you are using,
"ahmedbesbes/edup", is a resume-specific NER model focused on education extraction (degree, institution, date) and seems well-tailored for that domain. However, whether it is "the best" depends on several factors:
1. About the
"ahmedbesbes/edup"model- This is a fine-tuned NER model for education extraction from resumes.
- It was trained on a domain-specific dataset, which likely improves performance on resumes compared to a generic NER model.
- It uses Hugging Face transformers, so supports modern contextual embeddings.
2. Model Performance and Suitability
Pros:
- Domain-specific for extracting education entities.
- Aggregates entities with
"aggregation_strategy='simple'"to get cleaner spans. - Likely better than generic NER models (like vanilla
dbmdz/bert-large-cased-finetuned-conll03-englishor spaCy's general models) on education-related entities. - Supports degrees, institutions, and graduation dates as entities.
Potential Cons:
- Might be trained mostly on English resumes — if you need good multilingual or French resume support, it may not work optimally.
- Limited to the amount and variety of data it was trained on — might miss some unusual degree names, institutions, or date formats.
- Model size and inference time need to be considered (you are loading it lazily, which is good).
3. Alternatives to Consider
If you want to improve or compare, these strategies might help:
-
General NER + Custom Rules Hybrid: Use a general NER model like
dbmdz/bert-large-cased-finetuned-conll03-englishor spaCy’s transformer models, combined with your regex and rule-based extractors. -
Other Resume Parsing Libraries or APIs:
- Resume Parser by Sovren
- Affinda Resume Parser
- HireEZ / Hiretual Resume Parser
- pyresparser (may not be very accurate but decent baseline)
-
Train Your Own Model:
- If your domain or language needs are very specific, you could fine-tune a model yourself on a labeled resume dataset.
-
Multilingual Models
- If handling French or other languages, consider multilingual transformers like
xlm-roberta-basefine-tuned on resume NER datasets.
- If handling French or other languages, consider multilingual transformers like
4. Comments on Your Implementation
Your current approach is good and uses several smart techniques:
- Combines ML-based NER with regex and heuristics.
- Uses domain-specific NER model.
- Extracts and normalizes degrees carefully (excluding certifications).
- Extracts institution names using both NER and keyword context filtering.
- Uses dateparser with handy patterns to capture graduation dates robustly.
5. Bottom Line and Recommendations
- Yes,
"ahmedbesbes/edup"is currently one of the good pretrained models for education extraction in resumes, especially if your focus is English resumes. - However, test it rigorously on your target dataset (e.g., diverse resumes from your users) to verify recall/precision.
- Consider fallback rules or ensemble approaches (combine ML + regex).
- For French or other languages, you might need additional models or language-specific adaptation.
- Keep monitoring Hugging Face hub for newer, better resume NER models.
- If you want higher accuracy or specialized needs, consider fine-tuning a transformer NER model on your labeled data.
Summary
- Model `"ahmedbesbes/edup"` is good for English resume education extraction. - It’s domain-specific and usually better than generic NER for the task. - Not necessarily "best" in all scenarios—evaluate on your data. - Consider hybrid ML + rule-based extraction for best results. - For multilingual support, consider multilingual or language-specific models.
If you want, I can help you test its output or suggest enhancements for multilingual resumes!
- User
make it as best as possible use the best public hugging face model and make it work with both english and french cvs besides
- Assistant
To create a best possible education extractor that works for both English and French CVs, combining a powerful multilingual NER model with your domain-specific heuristics and regex patterns is the way to go.
Key improvements:
-
Use a multilingual transformer NER model fine-tuned on resumes or general NER tasks that understand English and French.
-
The excellent choice here is
Davlan/xlm-roberta-base-ner-hrlorxlm-roberta-base-finetuned-conll03-english-french-spanish(updated for multiple languages). -
These models recognize standard NER entities (PERSON, ORG, DATE, etc.) in multiple languages and show good generalization.
-
-
Combine the general-purpose NER outputs with your degree and institution regex patterns and lists.
-
Use better normalization for degrees considering French terms.
-
Use
dateparserwhich supports many languages including French for parsing dates. -
Lazy-load the model to only load when needed.
Complete rewritten example:
from typing import Optional, Dict from datetime import datetime import re from transformers import pipeline import dateparser # Lazy load multilingual NER model (XLM-R based) _NER_MODEL = None def get_multilingual_ner_pipeline(): global _NER_MODEL if _NER_MODEL is None: print("[EducationExtractor] Loading multilingual NER model (XLM-R)...") _NER_MODEL = pipeline( "ner", model="Davlan/xlm-roberta-base-ner-hrl", # multilingual NER model aggregation_strategy="simple", tokenizer="Davlan/xlm-roberta-base-ner-hrl" ) return _NER_MODEL # Degree patterns and French equivalents DEGREE_PATTERNS = [ r"\bBachelor(?:'s)?\b", r"\bBSc\b", r"\bBA\b", r"\bBS\b", r"\bB\.Eng\b", r"\bLicence\b", r"\bLicence professionnelle\b", r"\bMaster(?:'s)?\b", r"\bMSc\b", r"\bMA\b", r"\bMS\b", r"\bM\.Eng\b", r"\bMaster\b", r"\bMaster professionnel\b", r"\bPh\.?D\b", r"\bDoctorate\b", r"\bMBA\b", r"\bAssociate\b", r"\bLLB\b", r"\bLLM\b" ] # Map English and French terms to normalized degree names DEGREE_NORMALIZATION = { # English versions "bachelor's": "Bachelor's Degree", "bsc": "Bachelor's Degree", "ba": "Bachelor's Degree", "bs": "Bachelor's Degree", "b.eng": "Bachelor's Degree", "licence": "Bachelor's Degree", "licence professionnelle": "Bachelor's Degree", "master's": "Master's Degree", "msc": "Master's Degree", "ma": "Master's Degree", "ms": "Master's Degree", "m.eng": "Master's Degree", "master": "Master's Degree", "master professionnel": "Master's Degree", "ph.d": "PhD", "doctorate": "PhD", "mba": "MBA", "associate": "Associate Degree", "llb": "LLB", "llm": "LLM" } def extract_degree(text: str, ner_results) -> Optional[str]: # Try regex first (more precise for known degree terms) for pattern in DEGREE_PATTERNS: match = re.search(pattern, text, re.IGNORECASE) if match: degree_raw = match.group(0).lower() normalized = DEGREE_NORMALIZATION.get(degree_raw, degree_raw.title()) return normalized # Fallback: try to find entities tagged as degree (some models may not have this label) degree_entities = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == 'MISC'] # using MISC as fallback if degree_entities: # Take the longest entity as degree candidate candidate = max(degree_entities, key=len) return candidate.title() return None def extract_institution(text: str, ner_results) -> Optional[str]: education_keywords = ["university", "college", "institute", "school", "faculty", "campus", "université", "école", "institut", "faculté", "centre", "centre de formation"] candidates = [] for ent in ner_results: ent_group = ent['entity_group'].upper() if ent_group in ("ORG", "LOC", "MISC"): ent_text = ent['word'] # Context window search to check keywords presence pattern = re.compile(rf".{{0,50}}{re.escape(ent_text)}.{{0,50}}", re.IGNORECASE) nearby = pattern.findall(text) if any(any(kw in snippet.lower() for kw in education_keywords) for snippet in nearby): candidates.append(ent_text) if candidates: # Return longest candidate (likely full institution name) return max(candidates, key=len) # Regex fallback to common constructs, French & English regex_inst = re.search( r"((?:University|College|Institute|School|Faculty|Université|École|Institut|Faculté|Centre) of [A-Z][a-zA-Z& ]+)", text, re.IGNORECASE) if regex_inst: return regex_inst.group(0) return None def extract_graduation_date(text: str, ner_results) -> Optional[datetime]: # Extract date candidates from NER date_candidates = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == "DATE"] grad_date_patterns = [ r"graduat(?:ed|ion|e) (?:in\s)?(\b\d{4}\b)", # English simple year r"class of (\d{4})", # English class of r"graduation date[:\s]*(\w+\s\d{4}|\d{4})", # English r"obtention (*de)?(?:du|d') diplôme en? (\d{4})", # French "obtention du diplôme en 2018" r"diplômé(?:e)? en (\d{4})", # French diploma year r"promotion (\d{4})", # French ] for pattern in grad_date_patterns: match = re.search(pattern, text, re.IGNORECASE) if match: date_str = match.group(1) parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date for date_str in date_candidates: parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date # Last resort - search for 4 digit years explicitly in text matches = re.findall(r"\b(19|20)\d{2}\b", text) for year in matches: parsed_date = dateparser.parse(year, languages=['en', 'fr']) if parsed_date: return parsed_date return None def extract_education_from_text(text: str) -> Dict[str, Optional[str]]: ner = get_multilingual_ner_pipeline() ner_results = ner(text) degree = extract_degree(text, ner_results) institution = extract_institution(text, ner_results) graduation_date = extract_graduation_date(text, ner_results) return { "degree": degree or "Unknown", "institution": institution or "Unknown", "graduation_date": graduation_date.strftime("%Y-%m-%d") if graduation_date else None }
Explanation of choices:
- NER model:
Davlan/xlm-roberta-base-ner-hrlis a strong multilingual NER model trained on multiple languages (English, French, etc.), suitable to extract orgs, dates, and miscellaneous entities from CVs in multiple languages. - Degree patterns: include English and French degree keywords (
Licence,Master professionnel). - Degree normalization: normalize both English and French terms.
- Institution extraction: using ORG/MISC labels plus keyword context in English and French.
- Graduation dates: try regex with phrases both in English and French, then use NER DATEs, then fallback on year extraction.
dateparserwith languages param: handles both English and French date formats properly.- Returns "Unknown" degree/institution if not found.
How to improve further?
- Fine-tune a resume-specific multilingual model if you have labeled data in both languages.
- Add more comprehensive lists of degree synonyms and institution types in other languages you want to support.
- Consider building a pipeline to detect document language first and load a language-specific model/lexicon if precision is critical.
- Add caching or batching if extracting from many CVs for performance.
Testing
Sample usage:
text_en = """ John Doe graduated with a Bachelor's degree from Massachusetts Institute of Technology in 2018. """ text_fr = """ Jean Dupont a obtenu une Licence professionnelle en 2017 à l'École Polytechnique. """ print(extract_education_from_text(text_en)) print(extract_education_from_text(text_fr))
If you want me to help with specific further customizations or full multilingual resume parsing pipelines, just ask!
-
- User
from typing import Optional, Dict from datetime import datetime import re from transformers import pipeline import dateparser
Lazy load multilingual NER model (XLM-R based)
_NER_MODEL = None
def get_multilingual_ner_pipeline(): global _NER_MODEL if _NER_MODEL is None: print("[EducationExtractor] Loading multilingual NER model (XLM-R)...") _NER_MODEL = pipeline( "ner", model="Davlan/xlm-roberta-base-ner-hrl", # multilingual NER model aggregation_strategy="simple", tokenizer="Davlan/xlm-roberta-base-ner-hrl" ) return _NER_MODEL
Degree patterns and French equivalents
DEGREE_PATTERNS = [ r"\bBachelor(?:'s)?\b", r"\bBSc\b", r"\bBA\b", r"\bBS\b", r"\bB.Eng\b", r"\bLicence\b", r"\bLicence professionnelle\b", r"\bMaster(?:'s)?\b", r"\bMSc\b", r"\bMA\b", r"\bMS\b", r"\bM.Eng\b", r"\bMaster\b", r"\bMaster professionnel\b", r"\bPh.?D\b", r"\bDoctorate\b", r"\bMBA\b", r"\bAssociate\b", r"\bLLB\b", r"\bLLM\b" ]
Map English and French terms to normalized degree names
DEGREE_NORMALIZATION = { # English versions "bachelor's": "Bachelor's Degree", "bsc": "Bachelor's Degree", "ba": "Bachelor's Degree", "bs": "Bachelor's Degree", "b.eng": "Bachelor's Degree", "licence": "Bachelor's Degree", "licence professionnelle": "Bachelor's Degree", "master's": "Master's Degree", "msc": "Master's Degree", "ma": "Master's Degree", "ms": "Master's Degree", "m.eng": "Master's Degree", "master": "Master's Degree", "master professionnel": "Master's Degree", "ph.d": "PhD", "doctorate": "PhD", "mba": "MBA", "associate": "Associate Degree", "llb": "LLB", "llm": "LLM" }
def extract_degree(text: str, ner_results) -> Optional[str]: # Try regex first (more precise for known degree terms) for pattern in DEGREE_PATTERNS: match = re.search(pattern, text, re.IGNORECASE) if match: degree_raw = match.group(0).lower() normalized = DEGREE_NORMALIZATION.get(degree_raw, degree_raw.title()) return normalized
# Fallback: try to find entities tagged as degree (some models may not have this label) degree_entities = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == 'MISC'] # using MISC as fallback if degree_entities: # Take the longest entity as degree candidate candidate = max(degree_entities, key=len) return candidate.title() return Nonedef extract_institution(text: str, ner_results) -> Optional[str]: education_keywords = ["university", "college", "institute", "school", "faculty", "campus", "université", "école", "institut", "faculté", "centre", "centre de formation"]
candidates = [] for ent in ner_results: ent_group = ent['entity_group'].upper() if ent_group in ("ORG", "LOC", "MISC"): ent_text = ent['word'] # Context window search to check keywords presence pattern = re.compile(rf".{{0,50}}{re.escape(ent_text)}.{{0,50}}", re.IGNORECASE) nearby = pattern.findall(text) if any(any(kw in snippet.lower() for kw in education_keywords) for snippet in nearby): candidates.append(ent_text) if candidates: # Return longest candidate (likely full institution name) return max(candidates, key=len) # Regex fallback to common constructs, French & English regex_inst = re.search( r"((?:University|College|Institute|School|Faculty|Université|École|Institut|Faculté|Centre) of [A-Z][a-zA-Z& ]+)", text, re.IGNORECASE) if regex_inst: return regex_inst.group(0) return Nonedef extract_graduation_date(text: str, ner_results) -> Optional[datetime]: # Extract date candidates from NER date_candidates = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == "DATE"]
grad_date_patterns = [ r"graduat(?:ed|ion|e) (?:in\s)?(\b\d{4}\b)", # English simple year r"class of (\d{4})", # English class of r"graduation date[:\s]*(\w+\s\d{4}|\d{4})", # English r"obtention (*de)?(?:du|d') diplôme en? (\d{4})", # French "obtention du diplôme en 2018" r"diplômé(?:e)? en (\d{4})", # French diploma year r"promotion (\d{4})", # French ] for pattern in grad_date_patterns: match = re.search(pattern, text, re.IGNORECASE) if match: date_str = match.group(1) parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date for date_str in date_candidates: parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date # Last resort - search for 4 digit years explicitly in text matches = re.findall(r"\b(19|20)\d{2}\b", text) for year in matches: parsed_date = dateparser.parse(year, languages=['en', 'fr']) if parsed_date: return parsed_date return Nonedef extract_education_from_text(text: str) -> Dict[str, Optional[str]]: ner = get_multilingual_ner_pipeline() ner_results = ner(text)
degree = extract_degree(text, ner_results) institution = extract_institution(text, ner_results) graduation_date = extract_graduation_date(text, ner_results) return { "degree": degree or "Unknown", "institution": institution or "Unknown", "graduation_date": graduation_date.strftime("%Y-%m-%d") if graduation_date else None } File "/mnt/c/Users/hbili/Desktop/xtend-recruit-back-auth/cvs_manager/Educations_manager/data_extraction.py", line 117, in extract_graduation_date match = re.search(pattern, text, re.IGNORECASE) │ │ │ │ │ └ re.IGNORECASE │ │ │ │ └ <module 're' from '/usr/lib/python3.12/re/__init__.py'> │ │ │ └ 'UNKNOWN\nRayen hbili Studnet IT student at Iset nabeul, I am passionate about creatively solving problems and developing inn... │ │ └ "obtention (*de)?(?:du|d') diplôme en? (\\d{4})" │ └ <function search at 0x7f569e220ae0> └ <module 're' from '/usr/lib/python3.12/re/__init__.py'>File "/usr/lib/python3.12/re/init.py", line 177, in search return _compile(pattern, flags).search(string) │ │ │ └ 'UNKNOWN\nRayen hbili Studnet IT student at Iset nabeul, I am passionate about creatively solving problems and developing inn... │ │ └ re.IGNORECASE │ └ "obtention (*de)?(?:du|d') diplôme en? (\d{4})" └ <function _compile at 0x7f569e221120> File "/usr/lib/python3.12/re/init.py", line 307, in _compile p = _compiler.compile(pattern, flags) │ │ │ └ 2 │ │ └ "obtention (*de)?(?:du|d') diplôme en? (\d{4})" │ └ <function compile at 0x7f569e220860> └ <module 're._compiler' from '/usr/lib/python3.12/re/_compiler.py'> File "/usr/lib/python3.12/re/_compiler.py", line 750, in compile p = _parser.parse(p, flags) │ │ │ └ 2 │ │ └ "obtention (*de)?(?:du|d') diplôme en? (\d{4})" │ └ <function parse at 0x7f569e207d80> └ <module 're._parser' from '/usr/lib/python3.12/re/_parser.py'> File "/usr/lib/python3.12/re/_parser.py", line 979, in parse p = _parse_sub(source, state, flags & SRE_FLAG_VERBOSE, 0) │ │ │ │ └ 64 │ │ │ └ 2 │ │ └ <re._parser.State object at 0x7f555b2cfc80> │ └ <re._parser.Tokenizer object at 0x7f555b2cf890> └ <function _parse_sub at 0x7f569e207b00> File "/usr/lib/python3.12/re/_parser.py", line 460, in _parse_sub itemsappend(_parse(source, state, verbose, nested + 1, │ │ │ │ │ └ 0 │ │ │ │ └ 0 │ │ │ └ <re._parser.State object at 0x7f555b2cfc80> │ │ └ <re._parser.Tokenizer object at 0x7f555b2cf890> │ └ <function _parse at 0x7f569e207ba0> └ <built-in method append of list object at 0x7f55533bd4c0> File "/usr/lib/python3.12/re/_parser.py", line 862, in _parse p = _parse_sub(source, state, sub_verbose, nested + 1) │ │ │ │ └ 1 │ │ │ └ 0 │ │ └ <re._parser.State object at 0x7f555b2cfc80> │ └ <re._parser.Tokenizer object at 0x7f555b2cf890> └ <function _parse_sub at 0x7f569e207b00> File "/usr/lib/python3.12/re/_parser.py", line 460, in _parse_sub itemsappend(_parse(source, state, verbose, nested + 1, │ │ │ │ │ └ 2 │ │ │ │ └ 0 │ │ │ └ <re._parser.State object at 0x7f555b2cfc80> │ │ └ <re._parser.Tokenizer object at 0x7f555b2cf890> │ └ <function _parse at 0x7f569e207ba0> └ <built-in method append of list object at 0x7f55533bd2c0> File "/usr/lib/python3.12/re/_parser.py", line 687, in _parse raise source.error("nothing to repeat", │ └ <function Tokenizer.error at 0x7f569e207880> └ <re._parser.Tokenizer object at 0x7f555b2cf890>
re.error: nothing to repeat at position 11 2025-04-24 17:35:19.626 | INFO | logging:callHandlers:1762 - 127.0.0.1:45622 - "GET /api/scan/save_emails-cvs HTTP/1.1" 500
- Assistant
The error:
re.error: nothing to repeat at position 11arises from this regex pattern:
r"obtention (*de)?(?:du|d') diplôme en? (\d{4})"What is the problem?
*is a quantifier in regex (matches 0 or more repetitions). It must follow a valid token to repeat. In your pattern,(*de)?is invalid because*appears immediately after an opening parenthesis(without a valid preceding token.
How to fix it?
You likely meant to write:
(*de)?as( *de)?— allowing optional spaces before "de"- OR
(\s*de)?— optional whitespace before "de" - OR simply
(de)?if "de" is optional
Corrected regex pattern
Change this:
r"obtention (*de)?(?:du|d') diplôme en? (\d{4})"To either:
r"obtention (?:de )?(?:du|d') diplôme en? (\d{4})"or
r"obtention (?: *de)?(?:du|d') diplôme en? (\d{4})"or better:
r"obtention (?:de )?(?:du|d') diplôme en? (\d{4})"
Explanation:
(?: ...)non-capturing group(de )?optional "de " literal including space- Removed invalid
*standing alone
Full fixed
grad_date_patternssnippet:grad_date_patterns = [ r"graduat(?:ed|ion|e) (?:in\s)?(\b\d{4}\b)", # English simple year r"class of (\d{4})", # English class of r"graduation date[:\s]*(\w+\s\d{4}|\d{4})", # English r"obtention (?:de )?(?:du|d') diplôme en? (\d{4})", # French corrected pattern r"diplômé(?:e)? en (\d{4})", # French diploma year r"promotion (\d{4})", # French ]
Summary:
Your original
(*de)?is invalid regex syntax because*cannot be the first quantifier on a token, it must follow a character/class/group.Replacing it with a pattern that optionally includes
"de "fixes the problem.
Bonus: To validate regex syntax quickly:
You can use:
import re pattern = r"obtention (?:de )?(?:du|d') diplôme en? (\d{4})" re.compile(pattern) # Should pass without error
If you want, I can also help you update rest of the code or test it to avoid similar issues!
- User
from typing import Optional, Dict from datetime import datetime import re from transformers import pipeline import dateparser
Lazy load multilingual NER model (XLM-R based)
_NER_MODEL = None
def get_multilingual_ner_pipeline(): global _NER_MODEL if _NER_MODEL is None: print("[EducationExtractor] Loading multilingual NER model (XLM-R)...") _NER_MODEL = pipeline( "ner", model="Davlan/xlm-roberta-base-ner-hrl", # multilingual NER model aggregation_strategy="simple", tokenizer="Davlan/xlm-roberta-base-ner-hrl" ) return _NER_MODEL
Degree patterns and French equivalents
DEGREE_PATTERNS = [ r"\bBachelor(?:'s)?\b", r"\bBSc\b", r"\bBA\b", r"\bBS\b", r"\bB.Eng\b", r"\bLicence\b", r"\bLicence professionnelle\b", r"\bMaster(?:'s)?\b", r"\bMSc\b", r"\bMA\b", r"\bMS\b", r"\bM.Eng\b", r"\bMaster\b", r"\bMaster professionnel\b", r"\bPh.?D\b", r"\bDoctorate\b", r"\bMBA\b", r"\bAssociate\b", r"\bLLB\b", r"\bLLM\b" ]
Map English and French terms to normalized degree names
DEGREE_NORMALIZATION = { # English versions "bachelor's": "Bachelor's Degree", "bsc": "Bachelor's Degree", "ba": "Bachelor's Degree", "bs": "Bachelor's Degree", "b.eng": "Bachelor's Degree", "licence": "Bachelor's Degree", "licence professionnelle": "Bachelor's Degree", "master's": "Master's Degree", "msc": "Master's Degree", "ma": "Master's Degree", "ms": "Master's Degree", "m.eng": "Master's Degree", "master": "Master's Degree", "master professionnel": "Master's Degree", "ph.d": "PhD", "doctorate": "PhD", "mba": "MBA", "associate": "Associate Degree", "llb": "LLB", "llm": "LLM" }
def extract_degree(text: str, ner_results) -> Optional[str]: # Try regex first (more precise for known degree terms) for pattern in DEGREE_PATTERNS: match = re.search(pattern, text, re.IGNORECASE) if match: degree_raw = match.group(0).lower() normalized = DEGREE_NORMALIZATION.get(degree_raw, degree_raw.title()) return normalized
# Fallback: try to find entities tagged as degree (some models may not have this label) degree_entities = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == 'MISC'] # using MISC as fallback if degree_entities: # Take the longest entity as degree candidate candidate = max(degree_entities, key=len) return candidate.title() return Nonedef extract_institution(text: str, ner_results) -> Optional[str]: education_keywords = ["university", "college", "institute", "school", "faculty", "campus", "université", "école", "institut", "faculté", "centre", "centre de formation"]
candidates = [] for ent in ner_results: ent_group = ent['entity_group'].upper() if ent_group in ("ORG", "LOC", "MISC"): ent_text = ent['word'] # Context window search to check keywords presence pattern = re.compile(rf".{{0,50}}{re.escape(ent_text)}.{{0,50}}", re.IGNORECASE) nearby = pattern.findall(text) if any(any(kw in snippet.lower() for kw in education_keywords) for snippet in nearby): candidates.append(ent_text) if candidates: # Return longest candidate (likely full institution name) return max(candidates, key=len) # Regex fallback to common constructs, French & English regex_inst = re.search( r"((?:University|College|Institute|School|Faculty|Université|École|Institut|Faculté|Centre) of [A-Z][a-zA-Z& ]+)", text, re.IGNORECASE) if regex_inst: return regex_inst.group(0) return Nonedef extract_graduation_date(text: str, ner_results) -> Optional[datetime]: # Extract date candidates from NER date_candidates = [ent['word'] for ent in ner_results if ent['entity_group'].upper() == "DATE"]
grad_date_patterns = [ r"graduat(?:ed|ion|e) (?:in\s)?(\b\d{4}\b)", # English simple year r"class of (\d{4})", # English class of r"graduation date[:\s]*(\w+\s\d{4}|\d{4})", # English r"obtention (?:de )?(?:du|d') diplôme en? (\d{4})", # French "obtention du diplôme en 2018" r"diplômé(?:e)? en (\d{4})", # French diploma year r"promotion (\d{4})", # French ] for pattern in grad_date_patterns: match = re.search(pattern, text, re.IGNORECASE) if match: date_str = match.group(1) parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date for date_str in date_candidates: parsed_date = dateparser.parse(date_str, languages=['en', 'fr']) if parsed_date: return parsed_date # Last resort - search for 4 digit years explicitly in text matches = re.findall(r"\b(19|20)\d{2}\b", text) for year in matches: parsed_date = dateparser.parse(year, languages=['en', 'fr']) if parsed_date: return parsed_date return Nonedef extract_education_from_text(text: str) -> Dict[str, Optional[str]]: ner = get_multilingual_ner_pipeline() ner_results = ner(text)
degree = extract_degree(text, ner_results) institution = extract_institution(text, ner_results) graduation_date = extract_graduation_date(text, ner_results) return { "degree": degree or "Unknown", "institution": institution or "Unknown", "graduation_date": graduation_date.strftime("%Y-%m-%d") if graduation_date else None } this extract only one education right not all educations ? - Assistant
Yes, your current implementation of
extract_education_from_textextracts only one education record per input text: it returns a dictionary with a single degree, institution, and graduation date, found by searching the whole text but not returning multiple occurences or a list of educations.
Why only one education?
- Your functions (
extract_degree,extract_institution,extract_graduation_date) scan the whole text to find the first or strongest matching entity and then return immediately. - No iteration over multiple mentions or grouped results is implemented.
- The
nerpipeline returns multiple entity spans but you only process all entities to get a best candidate for each field, never grouping multiple degrees/institutions/dates.
How to extend it to extract all education entries?
In resumes, someone may list multiple degrees, institutions, and graduation dates (e.g., Bachelor's and Master's).
An ideal approach to extract multiple education records would be:
-
Segment the text by education-related sections or lines.
- Look for "Education", "Formation", "Diplômes", … as section headers.
- Split the text in parts and parse each separately.
-
Extract triples (degree, institution, graduation_date) per segment or mention.
- Group entities close in the text.
- Establish heuristics to associate an institution with the nearest degree and date.
-
Return a list of education dictionaries instead of a single dict.
Illustrative approach (simplified)
Here is an outline how you could do it:
from typing import List def extract_all_educations(text: str) -> List[Dict[str, Optional[str]]]: ner = get_multilingual_ner_pipeline() ner_results = ner(text) # Parse all degrees present (and their start positions) degrees = [] for pattern in DEGREE_PATTERNS: for match in re.finditer(pattern, text, re.IGNORECASE): deg_raw = match.group(0).lower() normalized_deg = DEGREE_NORMALIZATION.get(deg_raw, deg_raw.title()) degrees.append({"degree": normalized_deg, "start": match.start()}) # Get all institutions with positions education_keywords = ["university", "college", "institute", "school", "faculty", "campus", "université", "école", "institut", "faculté", "centre", "centre de formation"] institutions = [] for ent in ner_results: if ent['entity_group'].upper() in ("ORG", "LOC", "MISC"): ent_text = ent['word'] start_pos = ent['start'] if 'start' in ent else None # Check if it looks like an education-related institution for kw in education_keywords: if kw in ent_text.lower(): institutions.append({"institution": ent_text, "start": start_pos}) break # Extract dates with positions dates = [] for ent in ner_results: if ent['entity_group'].upper() == "DATE": dates.append({"date": ent['word'], "start": ent.get('start')}) # Also extract regex dates - (you can extract with positions similarly) # Naive grouping: for each degree, find closest institution and date by position educations = [] for deg in degrees: deg_pos = deg["start"] # Find closest institution after or before degree inst = None min_inst_dist = float('inf') for ins in institutions: if ins["start"] is None: continue dist = abs(ins["start"] - deg_pos) if dist < min_inst_dist: min_inst_dist = dist inst = ins["institution"] # Find closest date to degree gd = None min_date_dist = float('inf') for dt in dates: if dt["start"] is None: continue dist = abs(dt["start"] - deg_pos) if dist < min_date_dist: min_date_dist = dist gd = dt["date"] parsed_date = dateparser.parse(gd, languages=['en', 'fr']) if gd else None educations.append({ "degree": deg["degree"], "institution": inst or "Unknown", "graduation_date": parsed_date.strftime("%Y-%m-%d") if parsed_date else None }) return educations
Notes
- NER entity spans often include
startandendcharacter offsets; check your pipeline output to confirm and adjust accordingly. - Education sections typically have grouped lines with degree, institution, dates — so splitting the CV by lines or paragraphs before parsing helps.
- You may want more advanced NLP methods to group entities semantically or cluster by text proximity.
- Also consider verifying duplicates and removing near-identical entries.
Summary
- Your current code extracts only a single education record (best match per category).
- Extraction of all educations requires:
- Extracting multiple degrees, institutions, dates, each with position/offset.
- Grouping each degree with nearest institution and graduation date.
- Returning a list of dictionaries, not just one.
If you like, I can help you refactor your code fully to extract multiple education records with example inputs and outputs — just ask!
- Your functions (
- User
this one also from cvs_manager.Educations_manager.EducationRepository import EducationRepository from cvs_manager.Educations_manager.data_extraction import extract_education_from_text from cvs_manager.schemas import EducationUncommitted
class EducationService: def init(self, education_repo: EducationRepository): self.education_repo = education_repo
async def save_education(self, candidate_id: int, email_body: str): education_data = extract_education_from_text(email_body) education = EducationUncommitted( candidate_id=candidate_id, degree=education_data["degree"], institution=education_data["institution"], graduation_date=education_data["graduation_date"], ) saved_education = await self.education_repo.create(education) return saved_education - Assistant
Your current
EducationService.save_educationmethod just callsextract_education_from_text(email_body), which — as we discussed — extracts only a single education entry.If you want to support saving multiple education records extracted from the CV (email_body), you need to:
- Modify your extraction to return a list of education dicts (see my previous message for
extract_all_educations). - Update your service method to iterate over that list and save each education record individually.
Example: Adapted
extract_all_educationsfor multiple resultsAssuming you implemented a function (e.g.,
extract_all_educations(text: str) -> List[Dict]returning a list of education dicts like:[ { "degree": "Bachelor's Degree", "institution": "MIT", "graduation_date": "2018-05-01" }, { "degree": "Master's Degree", "institution": "Stanford University", "graduation_date": None } ]
Updated service method to save multiple educations asynchronously
from typing import List from cvs_manager.Educations_manager.EducationRepository import EducationRepository from cvs_manager.Educations_manager.data_extraction import extract_all_educations # updated extractor returning list from cvs_manager.schemas import EducationUncommitted class EducationService: def __init__(self, education_repo: EducationRepository): self.education_repo = education_repo async def save_educations(self, candidate_id: int, email_body: str) -> List[EducationUncommitted]: education_records = extract_all_educations(email_body) # returns List[Dict] saved_educations = [] for education_data in education_records: education = EducationUncommitted( candidate_id=candidate_id, degree=education_data.get("degree", "Unknown"), institution=education_data.get("institution", "Unknown"), graduation_date=education_data.get("graduation_date"), ) saved = await self.education_repo.create(education) saved_educations.append(saved) return saved_educations
Key points:
- The extractor returns a list of records.
- The service loops through them and saves each one.
- Returns the list of saved entities.
If you still want fallback single-education support:
If your
extract_all_educationsreturns an empty list, you can fallback to yourextract_education_from_text:async def save_educations(self, candidate_id: int, email_body: str) -> List[EducationUncommitted]: education_records = extract_all_educations(email_body) if not education_records: # fallback single extraction education_records = [extract_education_from_text(email_body)] # save loop same as above...
If you want, I can also help you refactor your extractor to cleanly support multiple educations or help update the repo/service interface for batching inserts!
- Modify your extraction to return a list of education dicts (see my previous message for
- User
does this add autoincrement id to candidatelanguage table ?
- Assistant
You asked:
"does this add autoincrement id to candidatelanguage table ?"
However, I don't see any code related to a
candidatelanguagetable or model in your previous messages.
General answer
Whether an auto-incrementing ID is created for a database table (like
candidatelanguage) depends on how the table/model is defined.- In ORMs like Django, if your model defines a primary key field (usually
id = models.AutoField(primary_key=True)) or does not define any primary key explicitly, Django creates an auto-increment integer field namedidby default. - If you define a model with a composite primary key or a natural key, an
idfield may not be created automatically. - Similarly, in raw SQL, whether the primary key is auto-increment depends on how the
CREATE TABLEstatement is written.
What you need to check if
candidatelanguagehas an auto-increment id:- Django model:
class CandidateLanguage(models.Model): # if no primary key defined, Django automatically adds auto-increment `id` field candidate = models.ForeignKey(Candidate, on_delete=models.CASCADE) language = models.ForeignKey(Language, on_delete=models.CASCADE) proficiency = models.IntegerField(choices=LanguageProficiency.choices)In this case, the table will have an
idcolumn auto-incremented by default.- If your model explicitly uses composite keys (e.g., candidate + language as primary key):
class CandidateLanguage(models.Model): candidate = models.ForeignKey(Candidate, on_delete=models.CASCADE, primary_key=True) language = models.ForeignKey(Language, on_delete=models.CASCADE, primary_key=True) proficiency = models.IntegerField(choices=LanguageProficiency.choices) class Meta: unique_together = (('candidate', 'language'),)Here, no auto-increment id field will be generated.
To summarize:
- If you did not define any primary key field on your
candidatelanguagemodel, Django adds an auto-incrementidfield automatically. - If you have explicitly defined your primary key(s), or set
primary_key=Trueon fields, Django will not create theidfield. - So, look at your
CandidateLanguagemodel definition:- If it only contains your fields without any explicit primary key, you get an auto-increment
id. - Otherwise, no auto-increment id.
- If it only contains your fields without any explicit primary key, you get an auto-increment
To confirm, you can:
- Check the migration file for
candidatelanguage— see if it includes anidfield. - Inspect the actual database schema (e.g., using
psql \d candidatelanguageor equivalent). - Print the model's
._metain Django shell:CandidateLanguage._meta.pk.name
If you provide the model code or migration snippet, I can tell you more precisely.
- In ORMs like Django, if your model defines a primary key field (usually
- User
add tthat id here """add id to canidate language table
Revision ID: 65cb232dae81 Revises: 40f9991a0eda Create Date: 2025-04-24 17:53:51.880113
""" from typing import Sequence, Union
from alembic import op import sqlalchemy as sa
revision identifiers, used by Alembic.
revision: str = '65cb232dae81' down_revision: Union[str, None] = '40f9991a0eda' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.alter_column('candidate_language', 'candidate_id', existing_type=sa.INTEGER(), nullable=True) op.alter_column('candidate_language', 'language_id', existing_type=sa.INTEGER(), nullable=True)
# ### end Alembic commands ###def downgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.alter_column('candidate_language', 'language_id', existing_type=sa.INTEGER(), nullable=False) op.alter_column('candidate_language', 'candidate_id', existing_type=sa.INTEGER(), nullable=False) # ### end Alembic commands ### class CandidateLanguage(Base): tablename = "candidate_language" id = Column(Integer, primary_key=True, index=True, autoincrement=True) candidate_id = Column(Integer, ForeignKey("candidate.id")) language_id = Column(Integer, ForeignKey("language.id")) proficiency = Column(String, nullable=False)
- Assistant
You want to add an auto-increment integer
idprimary key column to your existingcandidate_languagetable via Alembic migration, since your current table presumably has a composite PK (candidate_id,language_id), or possibly noidcolumn. You also posted the (SQLAlchemy model) snippet defining:id = Column(Integer, primary_key=True, index=True, autoincrement=True) candidate_id = Column(Integer, ForeignKey("candidate.id")) language_id = Column(Integer, ForeignKey("language.id")) proficiency = Column(String, nullable=False)
Important points
- Adding a new
idcolumn with primary key and autoincrement to an existing table is not trivial if primary key already exists (you cannot have two primary keys). - You will probably need to drop the existing primary key constraint (if composite on candidate_id + language_id), add the new
idcolumn as autoincrement primary key, and optionally create a unique constraint on(candidate_id, language_id)to keep uniqueness. - Be careful with existing data and FK constraints, backup your DB before migration.
Alembic Migration example to add
idcolumn as autoincrement primary keyBelow is an example migration to:
- Add an
idcolumn (autoincrement integer) - Drop old PK if composite exists on
(candidate_id, language_id) - Set
idas new PK - Add unique constraint on
(candidate_id, language_id)to preserve uniqueness
Assumptions:
- The table currently has a composite PK on (
candidate_id,language_id) or no explicit PK. - Adjust constraint and index names below for your DB if needed.
Alembic migration script
"""Add id autoincrement PK to candidate_language table Revision ID: 65cb232dae81 Revises: 40f9991a0eda Create Date: 2025-04-24 17:53:51.880113 """ from alembic import op import sqlalchemy as sa revision = '65cb232dae81' down_revision = '40f9991a0eda' branch_labels = None depends_on = None def upgrade(): # 1. Drop existing primary key constraint (if named) # Replace 'candidate_language_pkey' with your actual PK constraint name op.drop_constraint('candidate_language_pkey', 'candidate_language', type_='primary') # 2. Add new 'id' column as nullable for now (some DBs do not allow NOT NULL with existing rows) op.add_column('candidate_language', sa.Column('id', sa.Integer(), autoincrement=True, nullable=True) ) # 3. Populate 'id' for existing rows (if needed) # Usually the DB will assign autoincrement on insert; existing rows have NULL id now. # If your DB supports, you can update rows here; else depends on DB behavior. # 4. Alter the column to make 'id' NOT NULL op.alter_column('candidate_language', 'id', nullable=False) # 5. Create primary key on 'id' op.create_primary_key('pk_candidate_language', 'candidate_language', ['id']) # 6. Add unique constraint to keep candidate_id + language_id unique op.create_unique_constraint('uq_candidate_language_candidate_language', 'candidate_language', ['candidate_id', 'language_id']) def downgrade(): # Reverse steps # Drop unique constraint op.drop_constraint('uq_candidate_language_candidate_language', 'candidate_language', type_='unique') # Drop primary key on 'id' op.drop_constraint('pk_candidate_language', 'candidate_language', type_='primary') # Drop 'id' column op.drop_column('candidate_language', 'id') # Recreate original composite primary key (adjust constraint name if needed) op.create_primary_key('candidate_language_pkey', 'candidate_language', ['candidate_id', 'language_id'])
Notes
- Constraint names: Adjust
'candidate_language_pkey'and other constraint names as per your DB — run\d candidate_languagein psql or check your DB schema to get actual names. - For MySQL, autoincrement primary keys require slightly different handling.
- If your table is large, adding a new non-null PK with autoincrement may lock the table for some time.
- If you want this run smoothly, consider doing it in multiple steps and test on a staging DB.
After Alembic migration
Your model code should look like your posted SQLAlchemy model:
class CandidateLanguage(Base): __tablename__ = "candidate_language" id = Column(Integer, primary_key=True, index=True, autoincrement=True) candidate_id = Column(Integer, ForeignKey("candidate.id")) language_id = Column(Integer, ForeignKey("language.id")) proficiency = Column(String, nullable=False)
If you want I can help generate the exact migration for your particular DB flavor or if you have your existing table schema / current migrations, that'd help too.
- Adding a new