sqlitedao

SqliteDao

       

A simplified DAO for SQL abstraction for personal projects.

pip install sqlitedao

Demo project: sqlite_img_app

Examples

Create Table easily:

from sqlitedao import SqliteDao

dao = SqliteDao.get_instance(DB_PATH)

create_table_columns = {
    "name": "text",
    "position": "text",
    "age": "integer",
    "height": "text"
}
dao.create_table(TEST_TABLE_NAME, create_table_columns)

Or with a bit more control:

from sqlitedao import ColumnDict

columns = ColumnDict()
columns\
    .add_column("name", "text", "PRIMARY KEY")\
    .add_column("position", "text")\
    .add_column("age", "integer")\
    .add_column("height", "text")
create_table_indexes = {
    "name_index": ["position"]
}
dao.create_table(TEST_TABLE_NAME, columns, create_table_indexes)

Retrieve items as a list of python dictionaries:

from sqlitedao import SearchDict

search = SearchDict().add_filter("age", 50, operator=">")
rows = xdao.search_table(TEST_TABLE_NAME, search)
# [{"name": "Michael Jordan", "position": "SG", "age": 56, "height": "6-6"}]

Create DAO classes by inheriting TableItem easily and deal with less code:

from sqlitedao import TableItem, SearchDict

class Player(TableItem):

    TABLE_NAME = TEST_TABLE_NAME
    INDEX_KEYS = ["name"]
    ALL_COLUMNS = {
        "name": str,
        "position": str,
        "age": int,
        "height": str
    }

    def __init__(self, row_tuple=None, **kwargs):
        super().__init__(row_tuple, **kwargs)
        self.load_tuple()

    def load_tuple(self):
        self.name = self.row_tuple["name"]
        self.position = self.row_tuple["position"]
        self.age = self.row_tuple["age"]
        self.height = self.row_tuple["height"]

    def grow(self):
        self.age += 1
        self.row_tuple['age'] += 1


# Perform DAO action with above structured class
dao.insert_item(item)
dao.insert_items(items)
dao.update_item(changed_item)
dao.update_items(changed_items)
dao.find_item(item_with_only_index_populated)

# Pagination with sqlite is easier than ever!
old = SearchDict().add_filter("age", 35, ">")
old_team_1 = dao.get_items_page(Player, old, None, limit = 10)
old_team_2 = dao.get_items_page(Player, old, old_team_1[-1], limit = 10)

see test files for more examples. This can greatly simplify and ease the creation cost for pet projects based on sqlite.