Solution to SQL engine coding problem

sqlengine.py

import re

from typing import Any

PARSER = re.compile(r'^SELECT (.*) FROM [a-z]+(?: WHERE (.*?))?(?: ORDER BY (.*?))?(?: LIMIT (\d+))?$')

# Example
# print(PARSER.match("SELECT a, d FROM library WHERE b = 1 AND d = 3 ORDER BY c, d").groups())
# This prints: ('a, d', 'b = 1 AND d = 3', 'c, d')

LIBRARY = [
    {
        'title': 'The Ministry for the Future',
        'author': 'Kim Stanley Robinson',
        'year': 2020,
    },
    {
        'title': 'Circe',
        'author': 'Madeline Miller',
        'year': 2018,
    },
    {
        'title': 'Song of Achilles',
        'author': 'Madeline Miller',
        'year': 2012,
    },
    {
        'title': 'Besigning Climate Solutions',
        'author': 'Hal Harvey',
        'year': 2018,
    },
]

class SqlEngine:
    def __init__(self, parser: re.Pattern[str]) -> None:
        self._parser = parser
    
    def _parse(self, query: str) -> tuple[str, ...]:
        matches = self._parser.match(query)
        return (matches.groups() if matches is not None else ())
    
    def query(self, query: str) -> list[dict[str, Any]]:
        parsed = self._parse(query)
        if len(parsed) == 0:
            raise ValueError("Invalid SQL query")
        fields: list[str] = []
        conditions: dict[str, Any] = {}
        order_by: list[str] = []
        limit: int = len(LIBRARY)
        is_or = False
        if len(parsed) >= 1:
            fields_ = parsed[0]
            fields = [f.strip() for f in fields_.split(",")]
            if len(fields) == 1 and fields[0] == "*":
                fields = ["title", "author", "year"]
            if parsed[1] is not None:
                if "OR" in parsed[1]:
                    is_or = True
                conditions_ = re.split(r"(\s*AND\s*|\s*OR\s*)", parsed[1])
                for cond in conditions_:
                    if "=" in cond:
                        cond = cond.strip()
                        sep = cond.split("=")
                        k = sep[0].strip()
                        v: str | int = sep[1].strip().replace("'", "")
                        if k == "year":
                            v = int(v)
                        conditions[k] = v
            if parsed[2] is not None:
                order_by_ = parsed[2]
                order_by = [o.strip() for o in order_by_.split(",")]
            if parsed[3] is not None:
                limit_ = int(parsed[3])
                limit = min(limit, limit_)
        loc_library = LIBRARY.copy()
        final_result = []
        for item in loc_library:
            respected = 0
            to_respect = len(conditions)
            for k in conditions:
                if item[k] == conditions[k]:
                    respected+=1
            if not is_or:
                if to_respect == respected:
                    final_result.append(item)
            else:
                if respected > 0:
                    final_result.append(item)
        final_result = sorted(final_result, key=lambda item: tuple((item[ord] for ord in order_by)))
        final_result = [{k: item[k] for k in fields} for item in final_result]
        limit = min(limit, len(final_result))
        return final_result[:limit]

def main():
    engine = SqlEngine(parser=PARSER)
    test_queries = ["SELECT * FROM library WHERE author = 'Madeline Miller' ORDER BY year", "SELECT title FROM library WHERE author = 'Madeline Miller' AND year = 2012", "SELECT title, year FROM library", "SELECT year FROM library WHERE author = 'Madeline Miller' ORDER BY year LIMIT 1", "SELECT title FROM library WHERE author =  'Hal Harvey' OR year = 2018 ORDER BY author", "SELECT * FROM library WHERE author = 'Madeline Miller' OR year = 2018 ORDER BY year, author LIMIT 2"]
    expected: list[list[dict[str, Any]]] = [
        [{
            'title': 'Song of Achilles',
            'author': 'Madeline Miller',
            'year': 2012,
        },
        {
            'title': 'Circe',
            'author': 'Madeline Miller',
            'year': 2018,
        },],
        [{
            'title': 'Song of Achilles',
        }],
        [{
        'title': 'The Ministry for the Future',
        'year': 2020,
        },
        {
            'title': 'Circe',
            'year': 2018,
        },
        {
            'title': 'Song of Achilles',
            'year': 2012,
        },
        {
            'title': 'Besigning Climate Solutions',
            'year': 2018,
        },],
        [{
            'year': 2012,
        },],
        [{
            'title': 'Besigning Climate Solutions',
        },
        {
            'title': 'Circe',
        },],
        [{
            'title': 'Song of Achilles',
            'author': 'Madeline Miller',
            'year': 2012,
        },
        {
            'title': 'Besigning Climate Solutions',
            'author': 'Hal Harvey',
            'year': 2018,
        },
        ]
    ]
    for i,q in enumerate(test_queries):
        result = engine.query(q)
        print(result)
        try:
            assert result == expected[i]
        except AssertionError:
            print(f"Failed test on {q}: expecting {expected[i]}, got {result}")

if __name__ == "__main__":
    main()