getting_started.rst

Getting Started

In this guide, we're going to learn some basics of Estoult, such as creating, reading, updating and destroying records from a PostgreSQL database.

This guide will require you to have setup PostgreSQL beforehand.

Installing

To install the latest version, hosted on PyPI:

pip install estoult

If you are using SQLite you don't need to install anything else. Otherwise you need to have the correct database driver installed:

• PostgreSQL: psycopg2
• MySQL: mysqlclient

Creating the database object

Estoult has different database classes for each database driver: SQLiteDatabase, PostgreSQLDatabase and MySQLDatabase. We will use PostgreSQLDatabase here:

from estoult import *

db = PostgreSQLDatabase(
    database="my_db",
    user="postgres",
    password="postgres"
)

Creating schemas

The schema is a representation of data from our database. We create schemas by inheriting from the database Schema attribute.

class Author(db.Schema):
    __tablename__ = "authors"

    id = Field(int)
    first_name = Field(str)
    last_name = Field(str)

class Book(db.Schema):
    __tablename__ = "books"

    id = Field(int)
    name = Field(str)
    author_id = Field(int)

This defines the schema from the database that this schema maps to. In this case, we're saying that the Author schema maps to the authors table in the database, and the id, first_name and last_name are fields in that table.

Note

It is good practice to have your database table be named as a plural noun but schema as a singular noun.

Inserting and updating data

We can insert new rows into our tables like this:

new_author = {"first_name": "Kurt", "last_name": "Vonnegut"}
new_author = Author.insert(new_author)

new_book = {"name": "Player Piano", "author_id": new_author["id"]}
new_book = Book.insert(new_book)

To update the row, we use update:

new_book = Book.update(new_book, {"name": "Slaughterhouse-Five"})

Here we updated the row new_book with a new name.

update and insert return the "changeset" of the row. That is, it returns the row that is executed in database (after default, caster, null, etc... has been applied). For inserting it also returns the added primary_key to the set if missing.

Fetching a single record

Schema is for inserting/updating rows. When retrieving data or working with multiple rows we use the Query class.

my_book = (
    Query(Book)
    .get()
    .where(Book.id == 1)
    .execute()
)

print(my_book["id"])

Query builds your SQL query using a wide range of functions. We are using get to only retrieve one row and where to specify which. where accepts a number of clauses (or op, but that is for later) to send as arguments. When the query is built we call execute to run it.

Fetching multiple records

Instead of using get, use select to get multiple records.

my_books = (
     Query(Book)
     .select()
     .execute()
)

This will get all books.

Updating multiple records

You can do basic updates of records with Schema.

book_to_update = {"id": "123", "name": "Book"}
update = {"name": "New Book"}

Book.update(book_to_update, update)

This updates books where id is 123 and name is Book. You actually only need to pass in a unique field if you want to update a single book, but passing other fields aswell is fine.

Query can be used to make more complicated updates.

update_books = {"name": "Casseur de Logistille"}

(Query(Book)
    .update(update_books)
    .where(Book.id > 0)
    .execute())

This is updating all books with an id greater than 0.

Deleting records

Now that we've covered inserting, reading and updaing. The last thing is how to delete records in Estoult.

Similar to updating, we can use Schema or Query. Let's delete my_book which we retrieved earlier.

# Single book
Book.delete(my_book)

# Multiple books
(Query(Book)
    .delete()
    .where(Book.id >= my_book["id"])
    .execute())

The Query is deleting all books which have an id greater or equal to my_book["id"].