db-prawo-jazdy

Slightly modified version of prawo-jazdy-resources by maciej, to suit the needs of nuxt-prawo-jazdy

Files summary

main.py is an extremely badly-written script that reads files (pytania.csv and punkty.csv) shared by the Ministry of Infrastructure and imports them into a postgresql database

• db/pytania.csv is the katalog_pytania_egzminacyjne_kandydat__14112024.xlsx file from here
• db/punkty.csv isn't published by the Ministry, so you have to send a public information request to acquire it - we have received an XLSX

both of them are converted to CSV

Requirements

• postgres server
• python3
• packages from requirements.txt (pip install -r requirements.txt)
• files above (either already CSV'd from this repo or converted by yourself from official, most probably fresher sources)

Database structure

CREATE TABLE IF NOT EXISTS tasks (
    id integer NOT NULL,
    correct_answer character(5),
    media_url text,
    weight smallint
);

CREATE TABLE IF NOT EXISTS questions (
    task_id integer,
    lang character(2),
    text text
);

CREATE TABLE IF NOT EXISTS tasks_advanced (
    id integer NOT NULL,
    correct_answer character(1),
    media_url text,
    weight smallint
);

CREATE TABLE IF NOT EXISTS questions_advanced (
    task_id integer,
    lang character(2),
    text text,
    answer_a text,
    answer_b text,
    answer_c text
);

CREATE TABLE IF NOT EXISTS categories (
    name text,
    task_id integer
);

Criteria

Categories are as such:

• A
• B
• C
• D
• T
• AM
• A1
• A2
• B1
• C1
• D1
• PT

Languages as such:

• PL
• EN
• UA
• DE

Basic tasks query

According to the Ministry of Infrastructure, in an exam there are (basic):

• 4 questions for 1 point
• 6 questions for 2 points
• 10 questions for 3 points

so NO_OF_QUESTIONS and WEIGHT_OF_QUESTIONS should be set accordingly to the requirements above (and of course set category & lang to your liking)

SELECT
    tasks.id,
    tasks.correct_answer,
    tasks.media_url,
    tasks.weight,
    questions.text
FROM tasks
    LEFT JOIN questions ON tasks.id = questions.task_id
    LEFT JOIN categories ON tasks.id = categories.task_id
WHERE
    categories.name = 'B' AND
    questions.lang = 'PL' AND
    tasks.weight = WEIGHT_OF_QUESTIONS
ORDER BY random()
LIMIT (NO_OF_QUESTIONS);

Advanced tasks query

According to the Ministry of Infrastructure, in an exam there are (advanced):

• 2 questions for 1 point
• 4 questions for 2 points
• 6 questions for 3 points

so NO_OF_QUESTIONS and WEIGHT_OF_QUESTIONS should be set accordingly to the requirements above (and of course set category & lang to your liking)

SELECT
    tasks_advanced.id,
    tasks_advanced.correct_answer,
    tasks_advanced.media_url,
    tasks_advanced.weight,
    questions_advanced.text,
    questions_advanced.answer_a,
    questions_advanced.answer_b,
    questions_advanced.answer_c
FROM tasks_advanced
    LEFT JOIN questions_advanced ON tasks_advanced.id = questions_advanced.task_id
    LEFT JOIN categories ON tasks_advanced.id = categories.task_id
WHERE
    categories.name = 'B' AND
    questions_advanced.lang = 'PL' AND
    tasks_advanced.weight = WEIGHT_OF_QUESTIONS
ORDER BY random()
LIMIT (NO_OF_QUESTIONS);