Getting started

Introduction

Ardilla is specifically desiged to ease of use with intermedium developers in mind. The library help you abstract SQLite interactions in a simple pythonic way.

There is a clear tradeoff in flexibility and performace. If you require more complex database designs or interactions or a more performant solution, please take a look at alternative libraries.

Installation

pip install -U ardilla

desinging models

from datetime import datetime
from ardilla import Model, Field, ForeignField

class Author(Model):
    id: int = Field(pk=True, auto=True)
    name: str = Field(unique=True)

# Author autogenerated schema:
"""
CREATE TABLE IF NOT EXISTS author(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);
"""

class Book(Model):
    id: int = Field(pk=True, auto=True)
    name: str
    author_id: int = ForeignField(
        references=Author, 
        on_delete=ForeignField.CASCADE,
    )

# Book autogenerated schema:
"""
CREATE TABLE IF NOT EXISTS book(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES author(id) ON UPDATE NO ACTION ON DELETE CASCADE
);
"""

Using the engine

from ardilla import Engine

with Engine('path/to/db_file.sqlite3', enable_foreign_keys=True) as engine:
    author_crud = engine.crud(Author)
    book_crud = engine.crud(Book)

The crud objects hold all the logic to interact with a table in the database. They share a connection and are only good in the context where they're created. Alternatively you can use the regular open/close syntax

from ardilla import Engine

engine = Engine('path/to/db_file.sqlite3', enable_foreign_keys=True)
engine.connect() # always before creating cruds

author_crud = engine.crud(Author)
book_crud = engine.crud(Book)

engine.close() # always remember to close your connections

Creating

book_data = {
    "William Gibson": ["Neuromancer", "Count Zero", "Mona Lisa Overdrive"],
    "Douglas Adams": ["The Hitchhiker's Guide to the Galaxy", "The Restaurant at the End of the Universe", "Life, the Universe and Everything", "So Long, and Thanks for All the Fish"],
    "George Orwell": ["1984", "Animal Farm", "Homage to Catalonia"],
    "Aldous Huxley": ["Brave New World", "Island", "Point Counter Point"]
}

for author_name, books in book_data.items():
    author, was_created = author_crud.get_or_create(name=author_name)
    for book in books:
        book_crud.insert(name=book, author_id=author.id)

Reading

douglas_adams = author_crud.get_or_none(name='Douglas Adams')
books_by_adams = book_crud.get_many(author_id=douglas_adams.id)
print(books_by_adams)
>>> [Book(id=4, name="The Hitchhiker's Guide to the Galaxy", author_id=2), Book(id=5, name='The Restaurant at the End of the Universe', author_id=2), Book(id=6, name='Life, the Universe and Everything', author_id=2), Book(id=7, name='So Long, and Thanks for All the Fish', author_id=2)]

all_books = book_crud.get_all()

Updating

douglas_adams.name = douglas_adams.name.upper()

author_crud.save_one(douglas_adams)

Deleting

author_crud.delete(douglas_adams)
# we also delete all books linked to the author
george_orwell_id = 3
orwell_books = book_crud.get_many(author_id=george_orwell_id)
# delete all orwell books 
book_crud.delete_many(*orwell_books)

CRUD Methods

• crud.insert Inserts a record, rises errors if there's a conflict
• crud.insert_or_ignore Inserts a record or silently ignores if it already exists
• crud.save_one upserts an object
• crud.save_many upserts many objects
• crud.get_all equivalent to SELECT * FROM tablename
• crud.get_many returns all the objects that meet criteria
• crud.get_or_create returns an tuple of the object and a bool, True if the object was newly created
• crud.get_or_none Returns the first object meeting criteria if any
• crud.delete_one Deletes an object
• crud.delete_many Deletes many objects

Next

Learn about how to build your own Models