tiny_sqlite_orm

A simple ORM (Object-Relational Mapping) library for interacting with SQLite databases in Python, allowing you to work with your data in an object-oriented manner. The library focuses on simplicity and ease of use for small projects.

Installation

You can install it by running:

pip install tiny_sqlite_orm

How to Use

Setting Up the Database Connection

First, create an instance of the Database class to connect to the SQLite database.

from tiny_sqlite_orm import Database

# Connect to the database (or create it if it doesn't exist)
db = Database('my_database.db')

Defining Models

Models are defined as subclasses of the Table class. Each field in the model is an instance of a Field class. Here's an example of how to create a simple model:

from tiny_sqlite_orm import Table, TextField, IntegerField

class User(Table):

    # Table fields
    name = TextField(unique=True)
    age = IntegerField()

Creating the Tables

After defining your models, you can create the tables in the database:

# Create the tables if they don't already exist
db.create_tables_if_not_exists([User])

Inserting Data

You can insert new records into the database by running:

# Create a new user
user = User.create(name="John", age=30)
print(user.name)  # Returns: John

Selecting Data

To query data from the database, you can use the select method:

# Fetch users with age greater than or equal to 15
users = User.objects.select(age__ge=15)

# Iterate over the results
for user in users:
    print(user.name, user.age)

# Access the first and last user
first_user = users.first()
last_user = users.last()

See more about using select filters.

Updating Records

To update a record, you can:

• Modify the object's attributes and call the save method:

user = User.objects.select(name="John").first()
if user:
    user.age = 31
    user.save()

• Use the Table.objects.update() method:

User.objects.update(
    # Updates the age to 31
    fields={'age': 31},
    # Where name is "John"
    name="John"
)

Deleting Records

You can delete a record by calling the delete method on the object:

# Delete a user
user = User.objects.select(name="John").first()
if user:
    user.delete()

Or you can delete records using the delete method:

# Delete all users with the name John
User.objects.delete(name="John")

Using ForeignKey

You can define foreign key relationships between models. Here's an example with a Post model referencing a User:

from tiny_sqlite_orm import ForeignKeyField

class Post(Table):
    title = TextField()
    author = ForeignKeyField(User)

# Create the Post table
db.create_tables_if_not_exists([Post])

# Create a post related to a user
Post.create(title="My first post", author=user)

Aggregation Support

The library supports aggregation operations such as count, sum, avg, max, and min:

# Count the number of users
total_users = User.objects.count()

# Get the average age of users
average_age = User.objects.avg('age')

print(f'Total users: {total_users}')
print(f'Average age: {average_age}')

Using Select Filters

This method supports a variety of filters using __ (double underscore) syntax to specify conditions. Here are some common operators you can use:

• No filters: Checks for equality (e.g., field=value).
• field__ne: Checks for inequality (e.g., field__ne=value).
• field__gt: Checks if the field is greater than a value (e.g., field__gt=value).
• field__ge: Checks if the field is greater than or equal to a value (e.g., field__ge=value).
• field__lt: Checks if the field is less than a value (e.g., field__lt=value).
• field__le: Checks if the field is less than or equal to a value (e.g., field__le=value).
• field__in: Checks if the field is in one of the values passed (e.g., field__in=[1, 2, 'test']).

The two below only work for string fields:

• field__contains: Checks if the field's value contains the value (case sensitive) (e.g., field__contains="a").
• field__icontains: Also checks if the field's value contains the value, but is case insensitive.

Here’s an example of how to use these operators in a query:

# Fetch users with age greater than or equal to 15
users = User.objects.select(age__ge=15)

# Fetch users whose name starts with exactly 'Jo'
users_with_Jo = User.objects.select(name__contains='Jo')

# Fetch users whose age is either 25 or 30
users_25_or_30 = User.objects.select(age__in=[25, 30])

# Iterate over the results
for user in users:
    print(user.name, user.age)

# Access the first and last user
first_user = users.first()
last_user = users.last()

Testing

To run tests, run:

python run_tests.py

Contributions

Contributions are welcome! Feel free to open a pull request or suggest improvements.

License

This project is licensed under the MIT License. See the LICENSE file for details.