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}")

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)

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.