MentoDB

Sqlite3 based powerful database project.

Requirements:

• Python 3.9.6 or greater version

• pydantic -> pip install pydantic

• pandas -> pip install pandas

• numpy -> pip install numpy

• Import these two module before start:

from pydantic import BaseModel
from pydantic.dataclasses import dataclass

Working with Base Models

The following code demonstrates how to work with base models in Python using pydantic and dataclasses.

Creating a Model Extended from Base Model

The following code creates a model named MyModel that extends from BaseModel:

@dataclass
class MyModel(BaseModel):
    id: int
    name: str
    job: str
    price: int

Creating a SQL Table with a Model

Here's an example of how to create a SQL table with the MyModel model:

# Initialize a connection with MentoConnection (similar to "sqlite3.Connection")
con = MentoConnection("./database/new.db", check_same_thread=False)

# Create a database cursor with the connection object.
cursor = Mento(con)

# Create a table with the following structure: (id int, name text, job text, price int)
cursor.create("sample_table", model=MyModel)

Using Primary Key and Unique Column Matches When Creating Tables

Primary Key:

@dataclass
class PrimaryKeySample(BaseModel):
    id: PrimaryKey(int)
    name: str
    age: int
    price: int

# Create a table with the following structure: (id int primary key, name text, age int, price int)
cursor.create("primary_sample", model=PrimaryKeySample)

Unique Matches:

@dataclass
class Sample(BaseModel):
    id: PrimaryKey(int)
    name: str
    age: int
    price: int
    check_match: UniqueMatch("id", "name")

# Create a table with unique match control.
cursor.create("unique_matches_sample", model=Sample)

# Set the check_model parameter to check if there are matches.
# If the table has matched data, the insert process will be stopped.
cursor.check_model = Sample

Data Statements

Create

• Create a table if it does not already exist:

cursor.create("sample", model=Sample)

• Create a table without checking if it already exists:

cursor.create("sample", model=Sample, exists_check=False)

• Create multiple tables:

cursor.create_many(dict(first=MyModel, second=PrimaryKeySample, third=Sample))

Insert

cursor.insert(
    "sample",
    data=dict(id=1, name="fswair", age=18, price=4250),
    # If your model has a UniqueMatch control and you want to check matches, set the model with the check_model keyword argument.
    check_model=Sample
    )

Select

• Return all rows as a list of dictionaries:

cursor.select("sample")
# Output: [{id: 1, name: fswair, age: 18, price: 4250}]

SELECT

The following are the different methods for returning data from the table using the select statement in the cursor object:

• cursor.select("sample"): Returns all rows as list[dict] -> [{id: 1, name: fswair, age: 18, price: 4250}]

• cursor.select("sample", where={"id": 1, "name": "fswair"}): Returns all rows matched with the where condition. The condition looks like (in SQL): SELECT * FROM TABLE WHERE id = 1 AND name = 'fswair'.

• cursor.select("sample", where={"id": 1, "name": "fswair"}, order_by="id"): Returns all rows matched with the where condition sorted as ORDER BY. The condition looks like (in SQL): SELECT * FROM TABLE WHERE id = 1 AND name = 'fswair' ORDER BY id.

• cursor.select("sample", select_column="id"): Returns all row's id columns as list[dict] -> [{id: 1}, {id: 2}]

• cursor.select("sample", filter=lambda id: id % 3 == 0): Returns all rows matched with the lambda filter (lambda arg must be column name). Example output: list[dict] -> [{id: 3, name: fswair, age: 18, price: 4250}].

• cursor.select("sample", regexp={"id": ["\d{1,3}"]}): Returns all rows matched with regexp patterns (regexp dict must be one key as column name, value could be pattern or list of patterns). Example output: list[dict] -> [{id: 999, name: fswair, age: 18, price: 4250}].

Response Formatters for Select Statement

The following are the response formatters for the select statement:

• cursor.select("table", as_json=True): Returns data as JSON.

• cursor.select("table", as_dataframe=True): Returns data as a DataFrame (using Pandas).

• cursor.select("table", as_dataframe=True).to_csv(): Returns data as a CSV file.

• cursor.select("table", model=Sample, as_model=True): Returns object list (accessible with attributes).

UPDATE

The following updates the data matched with the where condition:

cursor.update(
    "sample",
    set_data=dict(name="fswair-up", age=20),
    where={"id": 1, "name": "fswair"}
    )