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
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
| Feature | SqlSymphony | SQLAlchemy | Peewee |
|---|---|---|---|
| 💫 Simplicity | ✔️ | ✔️ | ✔️ |
| 🚀 Performance | ✔️ | ❌ | ✔️ |
| 🌐 Database Agnosticism | ❌ | ✔️ | ❌ |
| 📚 Comprehensive Documentation | ✔️ | ✔️ | ✔️ |
| 🔥 Active Development | ✔️ | ✔️ | ❌ |
| 💻 Audit changes & reverts | ✔️ | ❌ | ❌ |
| ⚡ ASYNC Support | ✔️ | ✔️ (v2) | ❌ |
✨ 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.
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.
A security module created for hash functions.
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
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
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.
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")
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")
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
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 (?)'
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))"
)
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()
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()
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()
user = User(name='Charlie')
session.add(user)
session.commit()
user2 = User(name='John')
session.add(user2)
session.commit()
print(session.get_all())
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())
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())
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())
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()
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()
user = User(name='Charlie')
user.save()
user.commit()
user2 = User(name='John')
user2.save()
user2.commit()
print(user.objects.fetch())
user2 = User(name="Carl")
user2.save()
user2.commit()
user2.update(name="Bobby")
user2.commit()
print(user2.objects.fetch())
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())
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"))
Database attractions speed top:
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.
A class of database model.
SessionModel has some global variables that are needed to configure database fields:
__tablename__ - table name_ids - the value from which the primary key calculation beginsThe 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.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)A class for session.
Session has some global variables that are needed to configure database:
database_file - filepath to databasemodels - dictionary with saved models.manager - main database manager.audit_manager - audit manager instance.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.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.
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 beginsThe 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.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 modeladd_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)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.This class describes a sqlite db manager.
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.If you encounter any issues or have questions about SqlSymphony, please:
We welcome contributions from the community! If you'd like to help improve SqlSymphony, please check out the contributing guidelines to get started.
Our future goals for SQLSymphony include:
Distributed under the GNU GPL v3 License. See LICENSE for more information.
FAQs
The elegant and powerful SQLite3 ORM for Python
We found that sqlsymphony_orm demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
PyPI confirms no security flaws were exploited in the Ultralytics supply chain attack and highlights improvements for safer package publishing.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.