Insert, Delete and Update

Assume you have created the following data model in the current project's models folder (and the database also has corresponding tables):

from pylite_orm import DbModel, DbField, DbType

class User(DbModel):
    id = DbField(db_type = DbType.INT, pk = True)
    name = DbField(db_type = DbType.TEXT)
    age = DbField(db_type = DbType.INT)
    created_at = DbField(db_type = DbType.TEXT, default_factory = datetime.now)

All subsequent data operations are based on database sessions (DbSession).

Inserting Data

from pylite_orm import DbConn, DbSession
from .models import User

my_db = DbConn('mydb.db')

with DbSession(my_db) as dbs:
    user_info = User(name = 'Tom', age = 25)
    dbs.insert(User).item(user_info).exec()

Its semantics are clear: use the current dbs session's insert method, insert the item and execute immediately.

The User parameter specifies the data model (representing the underlying data table).
The user_info parameter specifies what data to insert.
The exec method means execute immediately.

Sometimes the data model instance is assigned in advance in the application, rather than at the moment of insertion. It looks like this:

user_info = User()
user_info.name = 'Tom'
user_info.age = 25

# Other business codes
...

with DbSession(my_db) as dbs:
    dbs.insert(User).item(user_info).exec()

Of course, the above is just inserting a single record. Sometimes you need to batch insert data:

users = [
    User(name = 'Tom', age = 25),
    User(name = 'Jerry', age = 23),
    User(name = 'Alice', age = 22)
]

with DbSession(my_db) as dbs:
    dbs.insert(User).item(users).exec()

Passing a list of data model instances instead of a single instance to item is batch insertion, which is very intuitive.

For Web projects, the data submitted by the frontend is JSON. Frameworks like FastAPI typically use Pydantic models to receive it. Then you just need to convert it to a dictionary, which can also become the parameter for item.

This means the inserted data can be not only a DbModel instance, but also a dictionary.

from fastapi import FastAPI
from pydantic import BaseModel
from pylite_orm import DbConn, DbSession

app = FastAPI()

class UserIn(BaseModel):
    name: str
    age: int

@app.post('/user')
def create_user(user_in: UserIn):
    user_info = user_in.model_dump()  # Becomes dictionary {'name': 'Tom', 'age': 25}

    with DbSession(my_db) as dbs:     # Assume mydb was created externally, use directly here
        dbs.insert(User).item(user_info).exec()

So can batch dictionaries work? Of course:

users = [
    {'name': 'Tom', 'age': 25},
    {'name': 'Jerry', 'age': 23},
    {'name': 'Alice', 'age': 22}
]

with DbSession(my_db) as dbs:
    dbs.insert(User).item(users).exec()

Data insertion operations have return values, although the previous example code didn't use them. If it's a single record (whether a model instance or dictionary), after successful insertion, it returns the inserted primary key value (usually the id value). If it's batch data, it returns the number of successfully inserted records.

user_id = dbs.insert(User).item(user_info).exec()
print(user_id)  # Successfully inserted id value

total = dbs.insert(User).item(users).exec()
print(total)    # Number of successfully inserted records

Deleting Data

Deleting data is much simpler:

# Delete the user with id 1
with DbSession(my_db) as dbs:
    total = dbs.delete(User).filter(user_id == 1).exec()

# Delete all users with age >= 25
with DbSession(my_db) as dbs:
    total = dbs.delete(User).filter(User.age >= 25).exec()

# Delete all users whose name starts with A
with DbSession(my_db) as dbs:
    total = dbs.delete(User).filter(User.name.startswith('A')).exec()

# Delete all users (this is a dangerous operation!)
with DbSession(my_db) as dbs:
    total = dbs.delete(User).exec()

Deleting data is implemented through the database session instance's delete method. Its essence is to query records that meet the conditions and then delete them, so it's followed by the filter method. The filter method will be covered extensively in the subsequent query chapter.

Delete operations also have return values, which is the number of successfully deleted records.

Updating Data

Let's look at an example directly:

# Assume there's already a user instance with id 1
user_info = dbs.select(User).filter(User.id == 1).first()

# Change the user's age to 30
user_info.age = 30

# Now update it
with DbSession(my_db) as dbs:
    total = dbs.update(User).item(user_info).filter(User.id == 1).exec()

For how to query data (the select(), first() etc. that appear in the code), there's a dedicated introduction in the query chapter.

Here you can see that the update operation uses the session instance's update method, passes the data to be updated to item, and then makes constraints through the filter method.

Many times, updates don't need full updates, only hoping to save the changed content. But unfortunately, when using a DbModel instance to carry data and pass it to item(), it's a full update. Because it contains all fields, and you can't control it.

If you only want to save the fields that have changed, the best practice is to use a dictionary. The DbModel instance itself has an asdict() method that can convert the instance to a dictionary. If you specify the exc_unset parameter as True during conversion, i.e., excluding unchanged data, then the converted dictionary will only retain the fields that have changed.

user_info = dbs.select(User).filter(User.id == 1).first()
user_info.age = 30

user_dict = user_info.asdict()
print(user_dict)  # {'id': 1, 'name': 'Tom', 'age': 30}

user_dict = user_info.asdict(exc_unset = True)
print(user_dict)  # {'age': 30}

# Now update it (only update the age field)
with DbSession(my_db) as dbs:
    dbs.update(User).item(user_dict).filter(User.id == 1).exec()

Now you know why you need to use filter when updating data: because judging which records to update through the data passed to item is unreliable - that data may not be complete.

Summary of Insert, Delete, and Update

🔹All operations are based on DbSession instances (emphasized once again)

🔹Insert data: use the insert method, item parameter is a DbModel instance or dictionary. Return value is the successfully saved primary key value or the number of successfully saved records

🔹Delete data: use the delete method, no item, but has filter. Return value is the number of successfully deleted records

🔹Update data: use the update method, item parameter is a DbModel instance or dictionary, and must also have filter. Return value is the number of successfully updated records

🔹It is strongly recommended to use transactions (i.e., the with statement in the example) for insert, delete, and update operations. Especially when operating on multiple tables simultaneously, transactions ensure that they "either all succeed or all fail".

Whether inserting, deleting, or updating, you must first specify the data model, such as User in this example. It represents which table you want to operate on.

pylite-orm, like other ORMs, has syntax that doesn't seem short, more like an object-oriented version of SQL statements.

The good news is that pylite-orm's type annotations are done very well, which means you can get intelligent hints or code auto-completion in your IDE.

Additionally, chain calls allow you to write all the code at once, or write it in segments with business logic.

Finally, note that without the exec() method to finish, nothing will happen.

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search