Project Overview
Objective:
Develop a PostgreSQL database named "Questionbank" using pgAdmin to store data extracted from State Engineering Services exam PDFs, specifically from the Preliminary Answer Key (PAK) and Final Answer Key (FAK) documents.

Database Schema
exams Table
Columns:
exam_id (SERIAL, PRIMARY KEY)
exam_name (VARCHAR(255), NOT NULL)
advertisement_number (VARCHAR(20), UNIQUE, NOT NULL)
exam_code (VARCHAR(20), UNIQUE, NULLABLE)
year (INT, NOT NULL)
questions Table
Columns:
question_id (SERIAL, PRIMARY KEY)
exam_id (INT, FOREIGN KEY referencing exams.exam_id ON DELETE CASCADE)
question_number (INT, NOT NULL)
question_text (TEXT, NOT NULL)
options Table
Columns:
option_id (SERIAL, PRIMARY KEY)
question_id (INT, FOREIGN KEY referencing questions.question_id ON DELETE CASCADE)
option_label (CHAR(1), CHECK IN ('A', 'B', 'C', 'D'))
option_text (TEXT, NOT NULL)
answers Table
Columns:
answer_id (SERIAL, PRIMARY KEY)
question_id (INT, UNIQUE, FOREIGN KEY referencing questions.question_id ON DELETE CASCADE)
correct_option (CHAR(1), CHECK IN ('A', 'B', 'C', 'D'))
Data Sources
Preliminary Answer Key (PAK) PDF
First Page: Contains unstructured exam details (Exam/Post Name, Exam Date, Advertisement Number) requiring OCR extraction.
Subsequent Pages: Contain structured questions and options directly extractable without OCR.
Final Answer Key (FAK) PDF
Contains structured tables mapping question numbers to correct options, extractable directly without OCR.
Tools & Libraries
Python Libraries:
pdfminer.six: For extracting text from PDFs.
pytesseract: For performing OCR on unstructured text.
langdetect: For detecting the language of extracted text.
psycopg2-binary: PostgreSQL adapter for Python.
logging: For logging script activities and errors.
External Tools:
Poppler: Required by pdf2image for PDF processing.
Tesseract OCR: Required by pytesseract for OCR tasks.
Ollama Backend & LLM: Considered for handling and translating Gujarati text (optional at this stage).
Issues & Resolutions
Schema Mismatch:
Issue:
Encountered an error while inserting exam details:
column "advertisement_number" of relation "exams" does not exist
Resolution:
Updated the exams table schema to include the advertisement_number column and ensured it matches the insertion script.
Parsing Failure:
Issue:
Script extracts 0 questions and 0 answers despite having structured text in the PDFs.
Sample Extracted Text from PAK:

102. The plan of a map was photocopied to a reduced size such that a line originally 120 mm, measures
90 mm. The original scale of plan was 1:1000. What will be the revised scale?
(A) 1: 1200 (B) 1: 1232
(C) 1: 1333 (D) 1: 1121
103. A lighthouse is visible above the horizon at a certain station at the sea level. If the distance between
the station and the lighthouse is 40 km, then the height of lighthouse is approximately
(A) 108 m (B) 126 m
(C) 132 m (D) 148 m
Potential Causes:
Regex Misalignment: The current regex patterns do not accurately match the structure of the extracted text.
Formatting Variations: Inconsistent use of punctuation, spacing, or line breaks in the PDFs leading to parsing failures.
Database Connection Issues:
Error Message:

psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "postgres"
Resolution:
Set a secure password for the postgres user.
Updated the pg_hba.conf file to use md5 authentication.
Restarted the PostgreSQL service to apply changes.
Modified the Python script to include the correct password in the connection parameters.
UnboundLocalError in Script:
Error Message:

UnboundLocalError: cannot access local variable 'conn' where it is not associated with a value
UnboundLocalError: cannot access local variable 'cursor' where it is not associated with a value
Resolution:
Enhanced error handling using try-except-finally blocks.
Utilized context managers (with statements) to manage database connections and cursors, ensuring they are properly closed even when exceptions occur.
Current Status
Exam Details Extraction:
Successfully extracted and inserted into the exams table with exam_id: 5.
Questions and Options Extraction:
Issue: Script is currently extracting 0 questions and 0 options from the PAK PDF.
Answers Extraction:
Issue: Script is currently extracting 0 answers from the FAK PDF.
Sample Data for Clarification
Extracted Questions and Options Format:


102. The plan of a map was photocopied to a reduced size such that a line originally 120 mm, measures
90 mm. The original scale of plan was 1:1000. What will be the revised scale?
(A) 1: 1200 (B) 1: 1232
(C) 1: 1333 (D) 1: 1121
103. A lighthouse is visible above the horizon at a certain station at the sea level. If the distance between
the station and the lighthouse is 40 km, then the height of lighthouse is approximately
(A) 108 m (B) 126 m
(C) 132 m (D) 148 m
Key Steps Taken So Far
Setup:
Created the PostgreSQL database "Questionbank" using pgAdmin.
Defined and adjusted the database schema to include necessary columns like advertisement_number.
Extracting Exam Details:
Used OCR (pytesseract) to extract unstructured exam details from the first page of the PAK PDF.
Inserted the extracted exam details into the exams table.
Processing PAK and FAK PDFs:
Attempted to extract questions and options from the structured pages of the PAK PDF directly without OCR.
Attempted to extract answers from the FAK PDF directly without OCR.
Encountered issues with the parsing logic, resulting in zero questions and answers being extracted.
Database Connection:
Resolved password authentication issues by setting a password for the postgres user and updating the script's connection parameters.
Error Handling Improvements:
Enhanced the Python script's error handling to prevent crashes and provide meaningful log messages.
Next Steps for Assistance
Refinement of Parsing Logic:
Objective: Ensure the regex patterns accurately match the structure of extracted text to successfully parse questions, options, and answers.
Action Needed: Review and adjust the regex patterns based on the actual format of the extracted text samples.
Verification of Text Extraction:
Objective: Confirm that the text extraction from PAK and FAK PDFs is working as expected.
Action Needed: Add logging and print statements to inspect the complete extracted text before parsing.
Handling Variations in PDF Formatting:
Objective: Account for any inconsistencies or variations in how questions and answers are formatted across different PDFs.
Action Needed: Implement more flexible and robust parsing strategies, possibly incorporating natural language processing (NLP) techniques.
Multilingual Content Management:
Objective: Efficiently handle pages in Gujarati by either skipping them or processing them using translation models.
Action Needed: Utilize language detection to identify Gujarati pages and decide on an appropriate strategy (e.g., translation via a local LLM like Ollama).
Testing with Diverse Samples:
Objective: Ensure the script works across various PAK and FAK PDFs with different structures and content formats.
Action Needed: Test the parser with multiple sample PDFs and adjust the logic based on findings.
Additional Considerations
Security:
Ensure that database credentials are managed securely, avoiding hardcoding them in scripts. Utilize environment variables or secure configuration files.
Performance:
For large PDFs or bulk processing, consider optimizing the extraction and insertion processes to handle data efficiently.
Logging and Monitoring:
Maintain comprehensive logs to track the script's operations, aiding in troubleshooting and ensuring data integrity.
Backup:
Regularly backup the PostgreSQL database to prevent data loss.
Summary:
The project involves creating and populating a PostgreSQL database with exam questions and answers extracted from PAK and FAK PDFs. While OCR has been successfully implemented for the first, unstructured page of the PAK PDF, challenges remain in parsing and extracting structured questions, options, and answers from subsequent pages. The current parsing logic fails to extract data, resulting in zero entries. Assistance is needed to refine the parsing strategies, verify text extraction accuracy, handle multilingual content, and ensure seamless database integration.