SQLSymphony

SQLSymphony: The elegant and powerful SQLite3 ORM for Python
Explore the docs »

Comparison with Alternatives . Why Choose SQLSymphony · Key Features · Getting Started · Basic Usage · Specification · Documentation · License

SQLSymphony: The elegant and powerful SQLite3 ORM for Python

Caution

At the moment, SQLSymphony is archived and not supported. I create new ORM library later.

SQLSymphony is a lightweight ✨, powerful 💪, and high-performance⚡️, Object-Relational Mapping (ORM) library for Python, designed to simplify the interaction with SQLite3 databases. It provides a Pythonic, object-oriented interface that allows developers to focus on their application's bussiness logic rather than the underlying database management.

SQLSymphony ORM - powerful and simple ORM for python

🌟 Comparison with Alternatives

Feature	SqlSymphony	SQLAlchemy	Peewee
💫 Simplicity	✔️	✔️	✔️
🚀 Performance	✔️	❌	✔️
🌐 Database Agnosticism	❌	✔️	❌
📚 Comprehensive Documentation	✔️	✔️	✔️
🔥 Active Development	✔️	✔️	❌
💻 Audit changes & reverts	✔️	❌	❌
⚡ ASYNC Support	COMING SOON	✔️ (v2)	❌

(back to top)

🤔 Why Choose SqlSymphony?

✨ Simplicity: SqlSymphony offers a straightforward and intuitive API for performing CRUD operations, filtering, sorting, and more, making it a breeze to work with databases in your Python projects.

💪 Flexibility: The library is designed to be database-agnostic, allowing you to switch between different SQLite3 implementations without modifying your codebase.

⚡️ Performance: SqlSymphony is optimized for performance, leveraging techniques like lazy loading and eager loading to minimize database queries and improve overall efficiency.

📚 Comprehensive Documentation: SqlSymphony comes with detailed documentation, including usage examples and API reference, to help you get started quickly and efficiently.

🔍 Maintainability: The codebase follows best practices in software engineering, including principles like SOLID, Clean Code, and modular design, ensuring the library is easy to extend and maintain.

🧪 Extensive Test Coverage: SqlSymphony is backed by a comprehensive test suite, ensuring the library's reliability and stability.

(back to top)

📚 Key Features

• Intuitive API: Pythonic, object-oriented interface for interacting with SQLite3 databases.
• Database Agnosticism: Seamlessly switch between different SQLite3 implementations.
• Performance Optimization: Lazy loading, eager loading, and other techniques for efficient database queries.
• Comprehensive Documentation: Detailed usage examples and API reference to help you get started.
• Modular Design: Clean, maintainable codebase that follows best software engineering practices.
• Extensive Test Coverage: Robust test suite to ensure the library's reliability and stability.

(back to top)

🚀 Getting Started

SQLSymphony is available on PyPI. Simply install the package into your project environment with PIP:

pip install sqlsymphony_orm

Once installed, you can start using the library in your Python projects. Check out the documentation for detailed usage examples and API reference.

(back to top)

💻 Usage Examples

Security.Hashing

A security module created for hash functions.

PlainHasher

from sqlsymphony_orm.security.hashing import PlainHasher, HashAlgorithm

hasher = PlainHasher(HashAlgorithm.SHA512) # also: SHA256, SHA512, MD5, BLAKE2B, BLAKE2S

hash1 = hasher.hash('password')
hash2 = hasher.hash('pasword')

hasher.verify('password', hash1) # True
hasher.verify('password', hash2) # False

SaltedHasher

from sqlsymphony_orm.security.hashing import SaltedHasher, HashAlgorithm

hasher = SaltedHasher(HashAlgorithm.SHA512, salt='SALT') # also: SHA256, SHA512, MD5, BLAKE2B, BLAKE2S
hasher2 = SaltedHasher(HashAlgorithm.SHA512, salt='SALT2') # also: SHA256, SHA512, MD5, BLAKE2B, BLAKE2S

hash1 = hasher.hash('password')
hash2 = hasher2.hash('password')

hasher.verify('password', hash1) # True
hasher.verify('password', hash2) # False

Migrations from old model to new model

During migration, a migrations directory is created, where database backups are stored, as well as a file sqlsymphony_migrates.json, which stores information about migrations. If you want to restore the database, then call the revert_migration function with the index_key parameter (by default -1, that is, the last migration), it will take the name of the database backup from sqlsymphony_migrates.json and replace the current database with the backup one. The current database file is taken from Session.

Migrate from old model to new model

from sqlsymphony_orm.datatypes.fields import IntegerField, RealField, TextField
from sqlsymphony_orm.models.session_models import SessionModel
from sqlsymphony_orm.models.session_models import SQLiteSession
from sqlsymphony_orm.queries import QueryBuilder
from sqlsymphony_orm.migrations.migrations_manager import SQLiteMigrationManager
from time import time

start = time()
session = SQLiteSession("example.db")


class User(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f"<User {self.pk}>"


class User2(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)
	password = TextField(default="password1234")

	def __repr__(self):
		return f"<User {self.pk}>"


class Comment(SessionModel):
	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	user_id = IntegerField(null=False)


user = User(name="John")
user2 = User(name="Bob")
user3 = User(name="Ellie")
session.add(user)
session.commit()
session.add(user2)
session.commit()
session.add(user3)
session.commit()
session.delete(user3)
session.commit()
session.update(model=user2, name="Anna")
session.commit()

comment = Comment(name=user.name, user_id=user.pk)
session.add(comment)
session.commit()

print(
	session.filter(QueryBuilder().SELECT("*").FROM(User.table_name).WHERE(name="Anna"))
)
print(session.get_all())
print(session.get_all_by_model(User))
print(user.pk)

migrations_manager = SQLiteMigrationManager(session)
migrations_manager.migrate_from_model(User, User2, "Users", "UserAnd")

session.close()

end = time()

total = round(end - start, 2)

print(f"Execution time: {total}s")

Revert last migration (rollback database)

from sqlsymphony_orm.datatypes.fields import IntegerField, RealField, TextField
from sqlsymphony_orm.models.session_models import SessionModel
from sqlsymphony_orm.models.session_models import SQLiteSession
from sqlsymphony_orm.queries import QueryBuilder
from sqlsymphony_orm.migrations.migrations_manager import SQLiteMigrationManager
from time import time

start = time()
session = SQLiteSession("example.db")


class User(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f"<User {self.pk}>"


class User2(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)
	password = TextField(default="password1234")

	def __repr__(self):
		return f"<User {self.pk}>"


class Comment(SessionModel):
	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	user_id = IntegerField(null=False)


user = User(name="John")
user2 = User(name="Bob")
user3 = User(name="Ellie")
session.add(user)
session.commit()
session.add(user2)
session.commit()
session.add(user3)
session.commit()
session.delete(user3)
session.commit()
session.update(model=user2, name="Anna")
session.commit()

comment = Comment(name=user.name, user_id=user.pk)
session.add(comment)
session.commit()

print(
	session.filter(QueryBuilder().SELECT("*").FROM(User.table_name).WHERE(name="Anna"))
)
print(session.get_all())
print(session.get_all_by_model(User))
print(user.pk)

migrations_manager = SQLiteMigrationManager(session)
migrations_manager.migrate_from_model(User, User2, "Users", "UserAnd")
migrations_manager.revert_migration(-1) # -1 is last index key

session.close()

end = time()

total = round(end - start, 2)

print(f"Execution time: {total}s")

Fields

Basic Features

Cache Performance

from sqlsymphony_orm.performance.cache import cached, SingletonCache, InMemoryCache


@cached(SingletonCache(InMemoryCache, max_size=1000, ttl=60))
def fetch_data(param1: str, param2: str):
	return {'data': f'{param1} and {param2}'}

result1 = fetch_data('foo', 'bar')
print(result1) # caching
result2 = fetch_data('foo', 'bar')
print(result2) # cached

result3 = fetch_data('baz', 'qux')
print(result3) # not cached

RAW SQL Query

from sqlsymphony_orm.database.connection import SQLiteDBConnector
from sqlsymphony_orm.queries import raw_sql_query

connector = SQLiteDBConnector().connect('database.db')


@raw_sql_query(connector=connector, values=('John',))
def insert():
	return 'INSERT INTO Users (name) VALUES (?)'

Session SQL Query Executor

from sqlsymphony_orm.database.manager import SQLiteDatabaseSession
from sqlsymphony_orm.database.connection import SQLiteDBConnector

with SQLiteDatabaseSession(connector, commit=True) as session:
	session.fetch(
		"CREATE TABLE IF NOT EXISTS BALABOLA (id INTEGER PRIMARY KEY, name VarChar(32))"
	)

MultiModel Manager

from sqlsymphony_orm.datatypes.fields import IntegerField, CharField, RealField, TextField, SlugField
from sqlsymphony_orm.models.orm_models import Model
from sqlsymphony_orm.database.manager import SQLiteMultiModelManager


class BankAccount(Model):
	__tablename__ = 'BankAccounts'
	__database__ = 'bank.db'

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f'<BankAccount {self.pk}>'

account = BankAccount(name="John", cash=100.0)

mm_manager = SQLiteMultiModelManager('database.db')
mm_manager.add_model(account)
mm_manager.model_manager(account._model_name).create_table(account._table_name, account.get_formatted_sql_fields())
mm_manager.model_manager(account._model_name).insert(account._table_name, account.get_formatted_sql_fields(), account.pk, account)
mm_manager.model_manager(account._model_name).commit()

Creating a Model

Session Style

With this method, you create and manage models and objects through an instance of the session class:

from sqlsymphony_orm.datatypes.fields import IntegerField, RealField, TextField
from sqlsymphony_orm.models.session_models import SessionModel
from sqlsymphony_orm.models.session_models import SQLiteSession
from sqlsymphony_orm.queries import QueryBuilder

session = SQLiteSession('example.db')


class User(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f'<User {self.pk}>'


user = User(name='John')
user2 = User(name='Bob')
user3 = User(name='Ellie')
session.add(user)
session.add(user2)
session.add(user3)
session.commit()
session.delete(user3)
session.commit()
session.update(model=user2, name='Anna')
session.commit()

print(session.filter(QueryBuilder().SELECT(*User._original_fields.keys()).FROM(User.table_name).WHERE(name='Anna')))

session.close()

Performing CRUD Operations

Drop table

from sqlsymphony_orm.datatypes.fields import IntegerField, RealField, TextField
from sqlsymphony_orm.models.session_models import SessionModel
from sqlsymphony_orm.models.session_models import SQLiteSession
from sqlsymphony_orm.queries import QueryBuilder

session = SQLiteSession('example.db')


class User(SessionModel):
	__tablename__ = "Users"

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f'<User {self.pk}>'


user = User(name='John')
user2 = User(name='Bob')
user3 = User(name='Ellie')
session.add(user)
session.add(user2)
session.add(user3)
session.commit()
session.delete(user3)
session.commit()
session.update(model=user2, name='Anna')
session.commit()

print(session.filter(QueryBuilder().SELECT(*User._original_fields.keys()).FROM(User.table_name).WHERE(name='Anna')))

session.drop_table()
session.close()

Create a new record

user = User(name='Charlie')
session.add(user)
session.commit()

user2 = User(name='John')
session.add(user2)
session.commit()

print(session.get_all())

Update record

user = User(name='John')
user2 = User(name='Bob')
session.add(user)
session.add(user2)

session.update(model=user2, name='Anna')
session.commit()

print(session.get_all())

Delete record

user = User(name='John')
user2 = User(name='Bob')
user3 = User(name='Ellie')

session.add(user)
session.add(user2)
session.add(user3)

session.commit()

session.delete(user3)

session.commit()

print(session.get_all())

Filter

from sqlsymphony_orm.queries import QueryBuilder

user = User(name='John')
user2 = User(name='Bob')
user3 = User(name='Ellie')
session.add(user)
session.add(user2)
session.add(user3)
session.commit()
session.delete(user3)
session.commit()
session.update(model=user2, name='Anna')
session.commit()

print(session.filter(QueryBuilder().SELECT(*User._original_fields.keys()).FROM(User.table_name).WHERE(name='Anna')))
print(session.get_all())

(back to top)

Model Style

With this method, you create and manage models and objects through an instance of the model class:

from sqlsymphony_orm.datatypes.fields import IntegerField, CharField, RealField, TextField, SlugField
from sqlsymphony_orm.models.orm_models import Model
from sqlsymphony_orm.database.manager import SQLiteMultiModelManager


class BankAccount(Model):
	__tablename__ = 'BankAccounts'
	__database__ = 'bank.db'

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f'<BankAccount {self.pk}>'


account = BankAccount(name='John', cash=100.0)
account2 = BankAccount(name='Bob', cash=100000000.0)
account2.save()
account2.commit()
account.save()
account.commit()

cash = float(input('Enter sum: '))
account.update(cash=account.cash + cash)
account.commit()
account2.update(cash=account2.cash - cash)
account2.commit()

print(account.cash, account2.cash)
print(BankAccount.objects.fetch())
print(BankAccount.objects.filter(name="Bob", first=True))

BankAccount.objects.drop_table()

mm_manager = SQLiteMultiModelManager('database.db')
mm_manager.add_model(account)
mm_manager.model_manager(account._model_name).create_table(account._table_name, account.get_formatted_sql_fields())
mm_manager.model_manager(account._model_name).insert(account._table_name, account.get_formatted_sql_fields(), account.pk, account)
mm_manager.model_manager(account._model_name).commit()

Performing CRUD Operations

Drop table

from sqlsymphony_orm.datatypes.fields import IntegerField, CharField, RealField, TextField, SlugField
from sqlsymphony_orm.models.orm_models import Model


class BankAccount(Model):
	__tablename__ = 'BankAccounts'
	__database__ = 'bank.db'

	id = IntegerField(primary_key=True)
	name = TextField(null=False)
	cash = RealField(null=False, default=0.0)

	def __repr__(self):
		return f'<BankAccount {self.pk}>'


account = BankAccount(name='John', cash=100.0)
account2 = BankAccount(name='Bob', cash=100000000.0)
account2.save()
account2.commit()
account.save()
account.commit()

cash = float(input('Enter sum: '))
account.update(cash=account.cash + cash)
account.commit()
account2.update(cash=account2.cash - cash)
account2.commit()

print(account.cash, account2.cash)
print(BankAccount.objects.fetch())
print(BankAccount.objects.filter(name="Bob", first=True))

BankAccount.objects.drop_table()

Create a new record

user = User(name='Charlie')
user.save()
user.commit()

user2 = User(name='John')
user2.save()
user2.commit()

print(user.objects.fetch())

Update record

user2 = User(name="Carl")
user2.save()
user2.commit()

user2.update(name="Bobby")
user2.commit()

print(user2.objects.fetch())

Delete record

user = User(name="Charlie")
user.save()
user.commit()

user2 = User(name="Carl")
user2.save()
user2.commit()

user3 = User(name="John")
user3.save()
user3.commit()

user3.delete() # delete user3
# OR
user3.delete(field_name="name", field_value="Carl") # delete user2

user3.commit()

print(user.objects.fetch())

Filter

user = User(name="Charlie")
user.save()
user.commit()

user2 = User(name="Carl")
user2.save()
user2.commit()

user2.update(name="Bobby")
user2.commit()

user3 = User(name="John")
user3.save()
user3.commit()

user3.delete()
user3.commit()

print(user.objects.fetch())
print(user.objects.filter(name="Bobby"))

(back to top)

🔧 Specifications

Database attractions speed top:

1. Raw SQL Query
2. Sessions Method
3. Models Method

Session Specification

Session Models are a new, recommended way to work with models and the database. This method is suitable for more complex projects. If you have a light project, it is better to use regular Models.

SessionModel

A class of database model.

Self Variables

SessionModel has some global variables that are needed to configure database fields:

• __tablename__ - table name
• _ids - the value from which the primary key calculation begins

The session model also has the following parameters, which are set at the stage of creating a class object:

• table_name - same as __tablename__
• model_name - class model name. if __tablename__ or __database__ are None, then their value is changed to the class model name.
• _original_fields - dictionary with original fields. The dictionary looks like this: '<field name>'='<field class>'
• fields - fields dictionary.
• unique_id - an UUID4 of instance.
• _hooks - a pre-action simple hooks dictionary.
• _last_action - dictionary storing the last action.

Methods

Session Model has some methods and functions for interactions with database:

• pk (property) - a primary key value.
• view_table_info() - print beautiful table with some info about model.
• get_formatted_sql_fields(skip_primary_key: bool = False) - return an dictionary with formatted fields for sql query (ex. insert)
• _class_get_formatted_sql_fields(skip_primary_key: bool = False) - return an dictionary with formatted fields for sql query (class method)

Session

A class for session.

Self Variables

Session has some global variables that are needed to configure database:

• database_file - filepath to database
• models - dictionary with saved models.
• manager - main database manager.
• audit_manager - audit manager instance.

Methods

Session has some methods and functions for interactions with database:

• get_all() - get all added models.
• get_all_by_model(self, needed_model: SessionModel) - get all saved models by model type.
• drop_table(self, table_name: str) - drop table.
• filter(self, query: 'QueryBuilder', first: bool=False) - filter and get models by query.
• update(self, model: SessionModel, **kwargs) - update model.
• add(self, model: SessionModel, ignore: bool=False) - add (with OR IGNORE sql prefix if ignore is True) new model.
• delete(self, model: SessionModel) - delete model.
• commit() - commit changes.
• close() - close connection.
• reconnect() - reconnect to database.

Model Specification

The Model class is needed to create a model. It acts as an element in the database and allows, through the objects subclass, to manage other objects of the class through the object.

Self Variables

Model has some global variables that are needed to configure database fields:

• __tablename__ - table name
• __database__ - database filepath
• __type__ - type of database. Default: ModelManagerType.SQLITE3.
• _ids - the value from which the primary key calculation begins

The model also has the following parameters, which are set at the stage of creating a class object:

• table_name - same as __tablename__
• database_name - same as __database_name__
• model_name - class model name. if __tablename__ or __database__ are None, then their value is changed to the class model name.
• _original_fields - dictionary with original fields. The dictionary looks like this: '<field name>'='<field class>'
• objects - an ModelManager instance. Example, if __type__ is ModelManagerType.SQLITE3, SQLiteModelManager.
• fields - fields dictionary.
• _hooks - a pre-action simple hooks dictionary.
• audit_manager - an audit manager instance.
• unique_id - an UUID4 of instance.
• _last_action - dictionary storing the last action.

Methods

Model has some methods and functions for interactions with database:

• pk (property) - a primary key value.
• commit() - method for commit changes to database.
• get_audit_history() - return audit history list.
• view_table_info() - print beautiful table with some info about model
• add_hook(before_action: str, func: Callable, func_args: tuple = ()) - add a hook.
• save(ignore: bool = False) - insert model to database.
• update(**kwargs) - update any value of model.
• delete(field_name: str = None, field_value: Any = None) - delete self model or delete model by field name and value.
• rollback_last_action() - revert last changes (one action)
• get_formatted_sql_fields(skip_primary_key: bool = False) - return an dictionary with formatted fields for sql query (ex. insert)
• _class_get_formatted_sql_fields(skip_primary_key: bool = False) - return an dictionary with formatted fields for sql query (class method)

Objects Instance

Below you can see the methods belonging to the objects instance. Through it you can manage this and other instances of the model:

• objects.drop_table(table_name: str=None) - drop table. If table_name is None, drop current model table, if table_name is not None, drop table by name.
• insert(table_name: str, formatted_fields: dict, pk: int, model_class: 'Model', ignore: bool = False) - insert fields by model.
• update(table_name: str, key: str, orig_field: str, new_value: str) - update element in database.
• filter(first: bool=False, *args, **kwargs) - filter and get model by kwargs.
• commit() - commit changes.
• create_table(table_name: str, fields: dict) - create table.
• delete(table_name: str, field_name: str, field_value: Any) - delete element from database.
• fetch() - fetch last query and return fetched result.

SQLiteModelManager

This class describes a sqlite db manager.

Self variables

SQLite model manager has some global variables that are needed to configure management process and database connection:

• model_class - main model class instance.
• _model_fields - original fields of model.
• q - basic QueryBuilder instance for fetch.
• _connector - database connector.

(back to top)

💬 Support

If you encounter any issues or have questions about SqlSymphony, please:

• Check the documentation for answers
• Open an issue on GitHub
• Reach out to the project maintainers via the mailing list

(back to top)

🤝 Contributing

We welcome contributions from the community! If you'd like to help improve SqlSymphony, please check out the contributing guidelines to get started.

(back to top)

🔮 Roadmap

Our future goals for SQLSymphony include:

• 📚 Expanding support for more SQLite3 features
• 🚀 Improving performance through further optimizations
• ✅ Enhancing the testing suite and code coverage
• 🌍 Translating the documentation to multiple languages
• 🔧 Implementing advanced querying capabilities
• 🚀 Add asynchronous operation mode
• ☑️ Add more fields
• ⌨️ Create ForeignKey field
• ⌨️ Create RelationShip
• 🖇️ Create more query-get methods
• ✈️ Create examples for flask, fastapi
• 🌐 Create software for remote connection to SQLSymphony Database

(back to top)

License

Distributed under the GNU GPL v3 License. See LICENSE for more information.

(back to top)