Data Operations

Inserting Records

The insert() method is used to add records to the database. You pass an instance of your model class to the method, and SQLiter will insert the record into the correct table:

user = User(name="Jane Doe", age=25, email="[email protected]")
result = db.insert(user)

The result variable will contain a new instance of the model, with the primary key value set to the newly-created primary key in the database. You should use this instance to access the primary key value and other fields:

print(f"New record inserted with primary key: {result.pk}")
print(f"Name: {result.name}, Age: {result.age}, Email: {result.email}")

Overriding the Timestamps

By default, SQLiter will automatically set the created_at and updated_at fields to the current Unix timestamp in UTC when a record is inserted. If you want to override this behavior, you can set the created_at and updated_at fields manually before calling insert():

import time

user.created_at = int(time.time())
user.updated_at = int(time.time())

However, by default this is disabled. Any model passed to insert() will have the created_at and updated_at fields set automatically and ignore any values passed in these 2 fields.

If you want to enable this feature, you can set the timestamp_override flag to True when inserting the record:

result = db.insert(user, timestamp_override=True)

Querying Records

SQLiter provides a simple and intuitive API for querying records from the database, Starting with the select() method and chaining other methods to filter, order, limit, and offset the results:

# Fetch all users
all_users = db.select(User).fetch_all()

# Filter users
young_users = db.select(User).filter(age=25).fetch_all()

# Order users
ordered_users = db.select(User).order("age", reverse=True).fetch_all()

# Limit and offset
paginated_users = db.select(User).limit(10).offset(20).fetch_all()

See Filtering Results for more advanced filtering options.

Updating Records

You can update records in the database by modifying the fields of the model instance and then calling the update() method. You just pass the model instance to the method:

user.age = 26
db.update(user)

On suffescul update, the updated_at field will be set to the current Unix timestamp in UTC by default.

Deleting Records

To delete a record from the database, you need to pass the model class and the primary key value of the record you want to delete:

db.delete(User, user.pk)

Commit your changes

By default, SQLiter will automatically commit changes to the database after each operation. If you want to disable this behavior, you can set auto_commit=False when creating the database connection:

db = SqliterDB("your_database.db", auto_commit=False)

You can then manually commit changes using the commit() method:

db.commit()

Close the Connection

When you're done with the database connection, you should close it to release resources:

db.close()

Note that closing the connection will also commit any pending changes, unless auto_commit is set to False.