# Simple SQLAlchemy
[](https://www.python.org/downloads/)
[](https://www.sqlalchemy.org/)
[](https://opensource.org/licenses/MIT)
A simplified, enhanced SQLAlchemy package that combines the power of SQLAlchemy ORM with convenient string-schema validation for modern database operations.
## ⚡ Quick Start (90% Use Case)
Get started in 30 seconds with the most common database operations:
```python
from simple_sqlalchemy import DbClient, CommonBase, BaseCrud
from sqlalchemy import Column, String, Integer, Boolean
# 1. Setup database and model
db = DbClient("sqlite:///app.db")
class User(CommonBase):
__tablename__ = 'users'
name = Column(String(100), nullable=False)
email = Column(String(100), unique=True)
active = Column(Boolean, default=True)
CommonBase.metadata.create_all(db.engine)
# 2. Create CRUD operations
user_crud = BaseCrud(User, db)
# 3. Use it! - Returns validated dictionaries ready for APIs
users = user_crud.query_with_schema(
schema_str="id:int, name:string, email:email?, active:bool",
filters={"active": True},
sort_by="name",
limit=10
)
# Returns: [{"id": 1, "name": "John", "email": "john@example.com", "active": true}, ...]
# Paginated results for web APIs
paginated = user_crud.paginated_query_with_schema(
schema_str="id:int, name:string, email:email?",
page=1,
per_page=20,
filters={"active": True}
)
# Returns: {"items": [...], "total": 150, "page": 1, "per_page": 20, "has_next": true}
# Create users
user_id = user_crud.create({"name": "Alice", "email": "alice@example.com"})
# Returns: 123 (the new user ID)
```
**That's it!** You now have type-safe, validated database operations perfect for APIs.
## 🚀 Features
- **🎯 90% Convenience**: String-schema operations for common use cases
- **🔧 10% Power**: Full SQLAlchemy access when you need it
- **🌍 Timezone Aware**: Automatic UTC timezone handling for datetime fields
- **📊 Enhanced Filtering**: null/not-null, comparisons, lists, ranges
- **📄 Pagination**: Built-in pagination with metadata
- **🔍 Search**: Text search across multiple fields
- **📈 Aggregation**: Group by, count, sum, avg with schema validation
- **🔄 Hybrid Approach**: Mix SQLAlchemy models and validated dicts
- **🗃️ Database Agnostic**: SQLite, PostgreSQL, MySQL support
- **⚡ Zero Breaking Changes**: Enhances existing SQLAlchemy patterns
## 📦 Installation
```bash
pip install simple-sqlalchemy
```
For PostgreSQL vector support:
```bash
pip install simple-sqlalchemy[postgres]
```
## 🏗️ Core Architecture
### DbClient - Your Database Entry Point
The `DbClient` is your main interface to the database:
```python
from simple_sqlalchemy import DbClient
# Create database connection
db = DbClient("sqlite:///app.db")
# or
db = DbClient("postgresql://user:pass@localhost/db")
# Use context manager for automatic cleanup
with DbClient("sqlite:///app.db") as db:
# Your database operations
pass
# Advanced configuration
db = DbClient(
"postgresql://user:pass@localhost/db",
echo=True, # Log SQL queries
pool_size=10, # Connection pool size
max_overflow=20 # Max connections beyond pool_size
)
```
### BaseCrud - Enhanced CRUD Operations
`BaseCrud` provides both traditional SQLAlchemy operations and modern string-schema operations:
```python
from simple_sqlalchemy import BaseCrud
user_crud = BaseCrud(User, db) # One instance per model
```
## 📋 String-Schema Operations (Recommended)
### Basic Queries
```python
# Simple query with schema validation
users = user_crud.query_with_schema(
schema_str="id:int, name:string, email:email?, active:bool",
filters={"active": True},
sort_by="name",
limit=50
)
# Enhanced filtering options
users = user_crud.query_with_schema(
schema_str="id:int, name:string, email:email?, age:int?",
filters={
"active": True, # Equality
"email": {"not": None}, # IS NOT NULL
"age": {">=": 18, "<": 65}, # Comparisons
"department": ["Engineering", "Product"], # IN clause
"created_at": {"between": ["2024-01-01", "2024-12-31"]} # Range
},
search_query="john", # Text search
search_fields=["name", "email"], # Fields to search in
sort_by="created_at",
sort_desc=True
)
```
### Pagination
```python
# Perfect for web APIs
result = user_crud.paginated_query_with_schema(
schema_str="id:int, name:string, email:email?, department:string?",
page=1,
per_page=20,
filters={"active": True},
search_query="alice",
search_fields=["name", "email"]
)
print(f"Page {result['page']} of {result['total_pages']}")
print(f"Total users: {result['total']}")
print(f"Has next: {result['has_next']}")
for user in result['items']:
print(f"- {user['name']}: {user['email']}")
```
### Aggregation
```python
# Department statistics
stats = user_crud.aggregate_with_schema(
aggregations={
"count": "count(id)",
"avg_age": "avg(age)",
"max_salary": "max(salary)"
},
schema_str="department:string?, count:int, avg_age:float?, max_salary:float?",
group_by=["department"],
filters={"active": True}
)
for stat in stats:
dept = stat['department'] or 'No Department'
print(f"{dept}: {stat['count']} users, avg age {stat['avg_age']:.1f}")
```
### CRUD Operations
```python
# Create (returns ID by default)
user_id = user_crud.create({
"name": "John Doe",
"email": "john@example.com",
"active": True
})
# Create and get data back
user = user_crud.create(
data={"name": "Jane Doe", "email": "jane@example.com"},
return_schema="id:int, name:string, email:email, created_at:datetime"
)
# Update (returns boolean by default)
success = user_crud.update(user_id, {"name": "John Smith"})
# Update and get data back
updated_user = user_crud.update(
id=user_id,
data={"name": "John Smith"},
return_schema="id:int, name:string, updated_at:datetime"
)
# Delete
deleted = user_crud.delete(user_id) # Returns True/False
```
## 🔧 Traditional SQLAlchemy Operations
When you need the full power of SQLAlchemy (relationships, change tracking, model methods):
```python
# Get SQLAlchemy model instances
user = user_crud.get_by_id(123) # Returns User instance
users = user_crud.get_multi(
filters={
"active": True,
"email": {"not": None}, # Enhanced filtering still works!
"department": ["Engineering", "Product"]
},
sort_by="name",
limit=10
)
# Use SQLAlchemy features
user.posts.append(new_post) # Relationships
user.send_welcome_email() # Model methods
user.name = "New Name" # Direct attribute access
# Save changes
updated_user = user_crud.update(user.id, {"name": user.name})
# Search across fields
search_results = user_crud.search(
search_query="alice",
search_fields=["name", "email"],
filters={"active": True}
)
# Count with enhanced filtering
count = user_crud.count(filters={
"active": True,
"age": {">=": 25},
"department": {"not_in": ["HR"]}
})
```
## 🔄 Hybrid Approach (Best of Both Worlds)
Mix SQLAlchemy power with schema validation:
```python
# Get SQLAlchemy instance for complex operations
user = user_crud.get_by_id(123)
# Use SQLAlchemy features
user.posts.append(new_post)
user.update_last_login()
user.calculate_metrics()
# Convert to validated dict for API response
api_response = user_crud.to_dict(user, "id:int, name:string, email:email, last_login:datetime")
# Or convert multiple instances
users = user_crud.get_multi(filters={"active": True})
api_users = user_crud.to_dict_list(users, "id:int, name:string, department:string?")
```
## 📊 Enhanced Filtering Reference
All filtering options work with both string-schema and traditional operations:
```python
filters = {
# Equality
"name": "John",
"active": True,
# Null checks
"email": None, # IS NULL
"phone": {"not": None}, # IS NOT NULL
# Comparisons
"age": {">=": 18}, # age >= 18
"salary": {"<": 100000}, # salary < 100000
"score": {"between": [80, 100]}, # BETWEEN 80 AND 100
# Lists
"status": ["active", "pending"], # IN ('active', 'pending')
"department": {"not_in": ["HR", "Legal"]}, # NOT IN
# String patterns
"name": {"like": "%john%"}, # LIKE (case-sensitive)
"email": {"ilike": "%@gmail.com"} # ILIKE (case-insensitive)
}
```
## 🔍 Advanced Features
### Complex Queries with SearchHelper
For complex custom queries that BaseCrud can't handle:
```python
from simple_sqlalchemy import SearchHelper
# Create search helper for complex queries
search_helper = db.create_search_helper(User)
# Custom query with complex JOINs
def complex_admin_query(session):
return session.query(User).join(Role).join(Permission).filter(
Permission.name == 'admin',
User.last_login > datetime.now() - timedelta(days=30)
).group_by(User.id).having(func.count(Role.id) > 2)
# Execute with pagination
results = search_helper.paginated_search_with_count(
query_func=complex_admin_query,
page=1,
per_page=20
)
# Batch processing for large datasets
def process_inactive_users(users_batch):
for user in users_batch:
user.send_reactivation_email()
search_helper.batch_process(
query_func=lambda s: s.query(User).filter(User.active == False),
process_func=process_inactive_users,
batch_size=100
)
```
### Many-to-Many Relationships
```python
from simple_sqlalchemy import M2MHelper
# Setup many-to-many helper
user_roles = M2MHelper(
db_client=db,
source_model=User,
target_model=Role,
association_table='user_roles' # Your association table
)
# Add relationships
user_roles.add_relationship(user_id=1, target_id=2) # Add role to user
user_roles.add_relationships(user_id=1, target_ids=[2, 3, 4]) # Add multiple roles
# Query relationships
user_role_ids = user_roles.get_target_ids(source_id=1) # Get user's role IDs
users_with_role = user_roles.get_source_ids(target_id=2) # Get users with specific role
# Remove relationships
user_roles.remove_relationship(user_id=1, target_id=2)
user_roles.clear_relationships(source_id=1) # Remove all roles from user
```
### PostgreSQL Vector Support
```python
from simple_sqlalchemy.postgres import VectorHelper
# Setup vector operations (requires pgvector extension)
vector_helper = VectorHelper(db, embedding_dim=384)
# Store embeddings
vector_helper.store_embedding(
table_name='documents',
record_id=123,
embedding=[0.1, 0.2, 0.3, ...], # 384-dimensional vector
metadata={"title": "Document Title", "category": "tech"}
)
# Similarity search
similar_docs = vector_helper.similarity_search(
table_name='documents',
query_embedding=[0.1, 0.2, 0.3, ...],
limit=10,
threshold=0.8
)
# Batch operations
embeddings_data = [
{"id": 1, "embedding": [0.1, 0.2, ...], "metadata": {"title": "Doc 1"}},
{"id": 2, "embedding": [0.3, 0.4, ...], "metadata": {"title": "Doc 2"}},
]
vector_helper.batch_store_embeddings('documents', embeddings_data)
```
## 🧪 Testing and Development
### Test Utilities
```python
from simple_sqlalchemy.testing import TestDbClient, create_test_data
# Create test database
test_db = TestDbClient() # Uses in-memory SQLite
# Create test data
test_users = create_test_data(User, count=10, overrides={
"active": True,
"department": "Engineering"
})
# Use in tests
def test_user_operations():
user_crud = BaseCrud(User, test_db)
users = user_crud.query_with_schema("id:int, name:string", filters={"active": True})
assert len(users) == 10
```
### Custom Schema Definitions
```python
# Define reusable schemas
user_crud.add_schema("basic", "id:int, name:string, email:email?")
user_crud.add_schema("full", "id:int, name:string, email:email?, active:bool, created_at:datetime")
user_crud.add_schema("api", "id:int, name:string, email:email?, department:string?")
# Use predefined schemas
users = user_crud.query_with_schema("basic", filters={"active": True})
paginated = user_crud.paginated_query_with_schema("api", page=1, per_page=20)
```
## 🎯 Best Practices
### When to Use What
**Use String-Schema Operations (90% of cases):**
- API endpoints that return JSON
- Data validation and type safety
- Simple CRUD operations
- Pagination and search
- Aggregation queries
**Use Traditional SQLAlchemy (10% of cases):**
- Complex business logic in model methods
- Relationship manipulation (adding/removing related objects)
- Transaction management across multiple models
- Custom SQLAlchemy features (events, hybrid properties)
**Use SearchHelper for:**
- Complex JOINs across multiple tables
- Custom aggregations with HAVING clauses
- Batch processing of large datasets
- Statistical analysis functions
### Performance Tips
```python
# Good: Use schema to fetch only needed columns
users = user_crud.query_with_schema(
"id:int, name:string", # Only fetch id and name
limit=100
)
# Avoid: Fetching all columns when you only need a few
users = user_crud.get_multi(limit=100) # Fetches all columns
# Good: Use aggregation for counts
stats = user_crud.aggregate_with_schema(
aggregations={"count": "count(id)"},
schema_str="department:string, count:int",
group_by=["department"]
)
# Good: Use pagination for large datasets
result = user_crud.paginated_query_with_schema(
"id:int, name:string",
page=1,
per_page=50
)
```
## 🔧 Configuration
### Database Connection Options
```python
# SQLite with custom options
db = DbClient(
"sqlite:///app.db",
echo=True, # Log SQL queries
connect_args={"check_same_thread": False}
)
# PostgreSQL with connection pooling
db = DbClient(
"postgresql://user:pass@localhost/db",
pool_size=10,
max_overflow=20,
pool_timeout=30,
pool_recycle=3600
)
# MySQL
db = DbClient(
"mysql+pymysql://user:pass@localhost/db",
echo=False,
pool_size=5
)
```
### Environment-Based Configuration
```python
import os
from simple_sqlalchemy import DbClient
# Use environment variables
DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///app.db")
db = DbClient(DATABASE_URL)
# Different configs for different environments
if os.getenv("ENVIRONMENT") == "production":
db = DbClient(DATABASE_URL, echo=False, pool_size=20)
elif os.getenv("ENVIRONMENT") == "development":
db = DbClient(DATABASE_URL, echo=True, pool_size=5)
else: # testing
db = DbClient("sqlite:///:memory:", echo=False)
```
## 📚 Examples
Check out the `examples/` directory for comprehensive examples:
- `examples/enhanced_crud_examples.py` - Complete BaseCrud usage
- `examples/basic_usage.py` - Getting started examples
- `examples/advanced_usage.py` - Complex queries and relationships
- `examples/postgres_examples.py` - PostgreSQL-specific features
## 🤝 Contributing
We welcome contributions! Please see our [Contributing Guide](CONTRIBUTING.md) for details.
## 📄 License
This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
## 🔗 Links
- [Documentation](https://simple-sqlalchemy.readthedocs.io/)
- [PyPI Package](https://pypi.org/project/simple-sqlalchemy/)
- [GitHub Repository](https://github.com/your-org/simple-sqlalchemy)
- [Issue Tracker](https://github.com/your-org/simple-sqlalchemy/issues)
Raw data
{
"_id": null,
"home_page": null,
"name": "simple-sqlalchemy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "Simple SQLAlchemy Contributors <contributors@simple-sqlalchemy.dev>",
"keywords": "sqlalchemy, orm, database, crud, pagination, search",
"author": null,
"author_email": "Simple SQLAlchemy Contributors <contributors@simple-sqlalchemy.dev>",
"download_url": "https://files.pythonhosted.org/packages/61/dc/a6ea16738abd0d7a8780435ad656ee61586b94b245fc6decf6762545c9f4/simple_sqlalchemy-0.1.1.tar.gz",
"platform": null,
"description": "# Simple SQLAlchemy\n\n[](https://www.python.org/downloads/)\n[](https://www.sqlalchemy.org/)\n[](https://opensource.org/licenses/MIT)\n\nA simplified, enhanced SQLAlchemy package that combines the power of SQLAlchemy ORM with convenient string-schema validation for modern database operations.\n\n## \u26a1 Quick Start (90% Use Case)\n\nGet started in 30 seconds with the most common database operations:\n\n```python\nfrom simple_sqlalchemy import DbClient, CommonBase, BaseCrud\nfrom sqlalchemy import Column, String, Integer, Boolean\n\n# 1. Setup database and model\ndb = DbClient(\"sqlite:///app.db\")\n\nclass User(CommonBase):\n __tablename__ = 'users'\n name = Column(String(100), nullable=False)\n email = Column(String(100), unique=True)\n active = Column(Boolean, default=True)\n\nCommonBase.metadata.create_all(db.engine)\n\n# 2. Create CRUD operations\nuser_crud = BaseCrud(User, db)\n\n# 3. Use it! - Returns validated dictionaries ready for APIs\nusers = user_crud.query_with_schema(\n schema_str=\"id:int, name:string, email:email?, active:bool\",\n filters={\"active\": True},\n sort_by=\"name\",\n limit=10\n)\n# Returns: [{\"id\": 1, \"name\": \"John\", \"email\": \"john@example.com\", \"active\": true}, ...]\n\n# Paginated results for web APIs\npaginated = user_crud.paginated_query_with_schema(\n schema_str=\"id:int, name:string, email:email?\",\n page=1,\n per_page=20,\n filters={\"active\": True}\n)\n# Returns: {\"items\": [...], \"total\": 150, \"page\": 1, \"per_page\": 20, \"has_next\": true}\n\n# Create users\nuser_id = user_crud.create({\"name\": \"Alice\", \"email\": \"alice@example.com\"})\n# Returns: 123 (the new user ID)\n```\n\n**That's it!** You now have type-safe, validated database operations perfect for APIs.\n\n## \ud83d\ude80 Features\n\n- **\ud83c\udfaf 90% Convenience**: String-schema operations for common use cases\n- **\ud83d\udd27 10% Power**: Full SQLAlchemy access when you need it\n- **\ud83c\udf0d Timezone Aware**: Automatic UTC timezone handling for datetime fields\n- **\ud83d\udcca Enhanced Filtering**: null/not-null, comparisons, lists, ranges\n- **\ud83d\udcc4 Pagination**: Built-in pagination with metadata\n- **\ud83d\udd0d Search**: Text search across multiple fields\n- **\ud83d\udcc8 Aggregation**: Group by, count, sum, avg with schema validation\n- **\ud83d\udd04 Hybrid Approach**: Mix SQLAlchemy models and validated dicts\n- **\ud83d\uddc3\ufe0f Database Agnostic**: SQLite, PostgreSQL, MySQL support\n- **\u26a1 Zero Breaking Changes**: Enhances existing SQLAlchemy patterns\n\n## \ud83d\udce6 Installation\n\n```bash\npip install simple-sqlalchemy\n```\n\nFor PostgreSQL vector support:\n\n```bash\npip install simple-sqlalchemy[postgres]\n```\n\n## \ud83c\udfd7\ufe0f Core Architecture\n\n### DbClient - Your Database Entry Point\n\nThe `DbClient` is your main interface to the database:\n\n```python\nfrom simple_sqlalchemy import DbClient\n\n# Create database connection\ndb = DbClient(\"sqlite:///app.db\")\n# or\ndb = DbClient(\"postgresql://user:pass@localhost/db\")\n\n# Use context manager for automatic cleanup\nwith DbClient(\"sqlite:///app.db\") as db:\n # Your database operations\n pass\n\n# Advanced configuration\ndb = DbClient(\n \"postgresql://user:pass@localhost/db\",\n echo=True, # Log SQL queries\n pool_size=10, # Connection pool size\n max_overflow=20 # Max connections beyond pool_size\n)\n```\n\n### BaseCrud - Enhanced CRUD Operations\n\n`BaseCrud` provides both traditional SQLAlchemy operations and modern string-schema operations:\n\n```python\nfrom simple_sqlalchemy import BaseCrud\n\nuser_crud = BaseCrud(User, db) # One instance per model\n```\n\n## \ud83d\udccb String-Schema Operations (Recommended)\n\n### Basic Queries\n\n```python\n# Simple query with schema validation\nusers = user_crud.query_with_schema(\n schema_str=\"id:int, name:string, email:email?, active:bool\",\n filters={\"active\": True},\n sort_by=\"name\",\n limit=50\n)\n\n# Enhanced filtering options\nusers = user_crud.query_with_schema(\n schema_str=\"id:int, name:string, email:email?, age:int?\",\n filters={\n \"active\": True, # Equality\n \"email\": {\"not\": None}, # IS NOT NULL\n \"age\": {\">=\": 18, \"<\": 65}, # Comparisons\n \"department\": [\"Engineering\", \"Product\"], # IN clause\n \"created_at\": {\"between\": [\"2024-01-01\", \"2024-12-31\"]} # Range\n },\n search_query=\"john\", # Text search\n search_fields=[\"name\", \"email\"], # Fields to search in\n sort_by=\"created_at\",\n sort_desc=True\n)\n```\n\n### Pagination\n\n```python\n# Perfect for web APIs\nresult = user_crud.paginated_query_with_schema(\n schema_str=\"id:int, name:string, email:email?, department:string?\",\n page=1,\n per_page=20,\n filters={\"active\": True},\n search_query=\"alice\",\n search_fields=[\"name\", \"email\"]\n)\n\nprint(f\"Page {result['page']} of {result['total_pages']}\")\nprint(f\"Total users: {result['total']}\")\nprint(f\"Has next: {result['has_next']}\")\n\nfor user in result['items']:\n print(f\"- {user['name']}: {user['email']}\")\n```\n\n### Aggregation\n\n```python\n# Department statistics\nstats = user_crud.aggregate_with_schema(\n aggregations={\n \"count\": \"count(id)\",\n \"avg_age\": \"avg(age)\",\n \"max_salary\": \"max(salary)\"\n },\n schema_str=\"department:string?, count:int, avg_age:float?, max_salary:float?\",\n group_by=[\"department\"],\n filters={\"active\": True}\n)\n\nfor stat in stats:\n dept = stat['department'] or 'No Department'\n print(f\"{dept}: {stat['count']} users, avg age {stat['avg_age']:.1f}\")\n```\n\n### CRUD Operations\n\n```python\n# Create (returns ID by default)\nuser_id = user_crud.create({\n \"name\": \"John Doe\",\n \"email\": \"john@example.com\",\n \"active\": True\n})\n\n# Create and get data back\nuser = user_crud.create(\n data={\"name\": \"Jane Doe\", \"email\": \"jane@example.com\"},\n return_schema=\"id:int, name:string, email:email, created_at:datetime\"\n)\n\n# Update (returns boolean by default)\nsuccess = user_crud.update(user_id, {\"name\": \"John Smith\"})\n\n# Update and get data back\nupdated_user = user_crud.update(\n id=user_id,\n data={\"name\": \"John Smith\"},\n return_schema=\"id:int, name:string, updated_at:datetime\"\n)\n\n# Delete\ndeleted = user_crud.delete(user_id) # Returns True/False\n```\n\n## \ud83d\udd27 Traditional SQLAlchemy Operations\n\nWhen you need the full power of SQLAlchemy (relationships, change tracking, model methods):\n\n```python\n# Get SQLAlchemy model instances\nuser = user_crud.get_by_id(123) # Returns User instance\nusers = user_crud.get_multi(\n filters={\n \"active\": True,\n \"email\": {\"not\": None}, # Enhanced filtering still works!\n \"department\": [\"Engineering\", \"Product\"]\n },\n sort_by=\"name\",\n limit=10\n)\n\n# Use SQLAlchemy features\nuser.posts.append(new_post) # Relationships\nuser.send_welcome_email() # Model methods\nuser.name = \"New Name\" # Direct attribute access\n\n# Save changes\nupdated_user = user_crud.update(user.id, {\"name\": user.name})\n\n# Search across fields\nsearch_results = user_crud.search(\n search_query=\"alice\",\n search_fields=[\"name\", \"email\"],\n filters={\"active\": True}\n)\n\n# Count with enhanced filtering\ncount = user_crud.count(filters={\n \"active\": True,\n \"age\": {\">=\": 25},\n \"department\": {\"not_in\": [\"HR\"]}\n})\n```\n\n## \ud83d\udd04 Hybrid Approach (Best of Both Worlds)\n\nMix SQLAlchemy power with schema validation:\n\n```python\n# Get SQLAlchemy instance for complex operations\nuser = user_crud.get_by_id(123)\n\n# Use SQLAlchemy features\nuser.posts.append(new_post)\nuser.update_last_login()\nuser.calculate_metrics()\n\n# Convert to validated dict for API response\napi_response = user_crud.to_dict(user, \"id:int, name:string, email:email, last_login:datetime\")\n\n# Or convert multiple instances\nusers = user_crud.get_multi(filters={\"active\": True})\napi_users = user_crud.to_dict_list(users, \"id:int, name:string, department:string?\")\n```\n\n## \ud83d\udcca Enhanced Filtering Reference\n\nAll filtering options work with both string-schema and traditional operations:\n\n```python\nfilters = {\n # Equality\n \"name\": \"John\",\n \"active\": True,\n\n # Null checks\n \"email\": None, # IS NULL\n \"phone\": {\"not\": None}, # IS NOT NULL\n\n # Comparisons\n \"age\": {\">=\": 18}, # age >= 18\n \"salary\": {\"<\": 100000}, # salary < 100000\n \"score\": {\"between\": [80, 100]}, # BETWEEN 80 AND 100\n\n # Lists\n \"status\": [\"active\", \"pending\"], # IN ('active', 'pending')\n \"department\": {\"not_in\": [\"HR\", \"Legal\"]}, # NOT IN\n\n # String patterns\n \"name\": {\"like\": \"%john%\"}, # LIKE (case-sensitive)\n \"email\": {\"ilike\": \"%@gmail.com\"} # ILIKE (case-insensitive)\n}\n```\n\n## \ud83d\udd0d Advanced Features\n\n### Complex Queries with SearchHelper\n\nFor complex custom queries that BaseCrud can't handle:\n\n```python\nfrom simple_sqlalchemy import SearchHelper\n\n# Create search helper for complex queries\nsearch_helper = db.create_search_helper(User)\n\n# Custom query with complex JOINs\ndef complex_admin_query(session):\n return session.query(User).join(Role).join(Permission).filter(\n Permission.name == 'admin',\n User.last_login > datetime.now() - timedelta(days=30)\n ).group_by(User.id).having(func.count(Role.id) > 2)\n\n# Execute with pagination\nresults = search_helper.paginated_search_with_count(\n query_func=complex_admin_query,\n page=1,\n per_page=20\n)\n\n# Batch processing for large datasets\ndef process_inactive_users(users_batch):\n for user in users_batch:\n user.send_reactivation_email()\n\nsearch_helper.batch_process(\n query_func=lambda s: s.query(User).filter(User.active == False),\n process_func=process_inactive_users,\n batch_size=100\n)\n```\n\n### Many-to-Many Relationships\n\n```python\nfrom simple_sqlalchemy import M2MHelper\n\n# Setup many-to-many helper\nuser_roles = M2MHelper(\n db_client=db,\n source_model=User,\n target_model=Role,\n association_table='user_roles' # Your association table\n)\n\n# Add relationships\nuser_roles.add_relationship(user_id=1, target_id=2) # Add role to user\nuser_roles.add_relationships(user_id=1, target_ids=[2, 3, 4]) # Add multiple roles\n\n# Query relationships\nuser_role_ids = user_roles.get_target_ids(source_id=1) # Get user's role IDs\nusers_with_role = user_roles.get_source_ids(target_id=2) # Get users with specific role\n\n# Remove relationships\nuser_roles.remove_relationship(user_id=1, target_id=2)\nuser_roles.clear_relationships(source_id=1) # Remove all roles from user\n```\n\n### PostgreSQL Vector Support\n\n```python\nfrom simple_sqlalchemy.postgres import VectorHelper\n\n# Setup vector operations (requires pgvector extension)\nvector_helper = VectorHelper(db, embedding_dim=384)\n\n# Store embeddings\nvector_helper.store_embedding(\n table_name='documents',\n record_id=123,\n embedding=[0.1, 0.2, 0.3, ...], # 384-dimensional vector\n metadata={\"title\": \"Document Title\", \"category\": \"tech\"}\n)\n\n# Similarity search\nsimilar_docs = vector_helper.similarity_search(\n table_name='documents',\n query_embedding=[0.1, 0.2, 0.3, ...],\n limit=10,\n threshold=0.8\n)\n\n# Batch operations\nembeddings_data = [\n {\"id\": 1, \"embedding\": [0.1, 0.2, ...], \"metadata\": {\"title\": \"Doc 1\"}},\n {\"id\": 2, \"embedding\": [0.3, 0.4, ...], \"metadata\": {\"title\": \"Doc 2\"}},\n]\nvector_helper.batch_store_embeddings('documents', embeddings_data)\n```\n\n## \ud83e\uddea Testing and Development\n\n### Test Utilities\n\n```python\nfrom simple_sqlalchemy.testing import TestDbClient, create_test_data\n\n# Create test database\ntest_db = TestDbClient() # Uses in-memory SQLite\n\n# Create test data\ntest_users = create_test_data(User, count=10, overrides={\n \"active\": True,\n \"department\": \"Engineering\"\n})\n\n# Use in tests\ndef test_user_operations():\n user_crud = BaseCrud(User, test_db)\n users = user_crud.query_with_schema(\"id:int, name:string\", filters={\"active\": True})\n assert len(users) == 10\n```\n\n### Custom Schema Definitions\n\n```python\n# Define reusable schemas\nuser_crud.add_schema(\"basic\", \"id:int, name:string, email:email?\")\nuser_crud.add_schema(\"full\", \"id:int, name:string, email:email?, active:bool, created_at:datetime\")\nuser_crud.add_schema(\"api\", \"id:int, name:string, email:email?, department:string?\")\n\n# Use predefined schemas\nusers = user_crud.query_with_schema(\"basic\", filters={\"active\": True})\npaginated = user_crud.paginated_query_with_schema(\"api\", page=1, per_page=20)\n```\n\n## \ud83c\udfaf Best Practices\n\n### When to Use What\n\n**Use String-Schema Operations (90% of cases):**\n\n- API endpoints that return JSON\n- Data validation and type safety\n- Simple CRUD operations\n- Pagination and search\n- Aggregation queries\n\n**Use Traditional SQLAlchemy (10% of cases):**\n\n- Complex business logic in model methods\n- Relationship manipulation (adding/removing related objects)\n- Transaction management across multiple models\n- Custom SQLAlchemy features (events, hybrid properties)\n\n**Use SearchHelper for:**\n\n- Complex JOINs across multiple tables\n- Custom aggregations with HAVING clauses\n- Batch processing of large datasets\n- Statistical analysis functions\n\n### Performance Tips\n\n```python\n# Good: Use schema to fetch only needed columns\nusers = user_crud.query_with_schema(\n \"id:int, name:string\", # Only fetch id and name\n limit=100\n)\n\n# Avoid: Fetching all columns when you only need a few\nusers = user_crud.get_multi(limit=100) # Fetches all columns\n\n# Good: Use aggregation for counts\nstats = user_crud.aggregate_with_schema(\n aggregations={\"count\": \"count(id)\"},\n schema_str=\"department:string, count:int\",\n group_by=[\"department\"]\n)\n\n# Good: Use pagination for large datasets\nresult = user_crud.paginated_query_with_schema(\n \"id:int, name:string\",\n page=1,\n per_page=50\n)\n```\n\n## \ud83d\udd27 Configuration\n\n### Database Connection Options\n\n```python\n# SQLite with custom options\ndb = DbClient(\n \"sqlite:///app.db\",\n echo=True, # Log SQL queries\n connect_args={\"check_same_thread\": False}\n)\n\n# PostgreSQL with connection pooling\ndb = DbClient(\n \"postgresql://user:pass@localhost/db\",\n pool_size=10,\n max_overflow=20,\n pool_timeout=30,\n pool_recycle=3600\n)\n\n# MySQL\ndb = DbClient(\n \"mysql+pymysql://user:pass@localhost/db\",\n echo=False,\n pool_size=5\n)\n```\n\n### Environment-Based Configuration\n\n```python\nimport os\nfrom simple_sqlalchemy import DbClient\n\n# Use environment variables\nDATABASE_URL = os.getenv(\"DATABASE_URL\", \"sqlite:///app.db\")\ndb = DbClient(DATABASE_URL)\n\n# Different configs for different environments\nif os.getenv(\"ENVIRONMENT\") == \"production\":\n db = DbClient(DATABASE_URL, echo=False, pool_size=20)\nelif os.getenv(\"ENVIRONMENT\") == \"development\":\n db = DbClient(DATABASE_URL, echo=True, pool_size=5)\nelse: # testing\n db = DbClient(\"sqlite:///:memory:\", echo=False)\n```\n\n## \ud83d\udcda Examples\n\nCheck out the `examples/` directory for comprehensive examples:\n\n- `examples/enhanced_crud_examples.py` - Complete BaseCrud usage\n- `examples/basic_usage.py` - Getting started examples\n- `examples/advanced_usage.py` - Complex queries and relationships\n- `examples/postgres_examples.py` - PostgreSQL-specific features\n\n## \ud83e\udd1d Contributing\n\nWe welcome contributions! Please see our [Contributing Guide](CONTRIBUTING.md) for details.\n\n## \ud83d\udcc4 License\n\nThis project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.\n\n## \ud83d\udd17 Links\n\n- [Documentation](https://simple-sqlalchemy.readthedocs.io/)\n- [PyPI Package](https://pypi.org/project/simple-sqlalchemy/)\n- [GitHub Repository](https://github.com/your-org/simple-sqlalchemy)\n- [Issue Tracker](https://github.com/your-org/simple-sqlalchemy/issues)\n",
"bugtrack_url": null,
"license": null,
"summary": "A simplified, enhanced SQLAlchemy package with common patterns and utilities",
"version": "0.1.1",
"project_urls": {
"Bug Tracker": "https://github.com/simple-sqlalchemy/simple-sqlalchemy/issues",
"Documentation": "https://simple-sqlalchemy.readthedocs.io/",
"Homepage": "https://github.com/simple-sqlalchemy/simple-sqlalchemy",
"Repository": "https://github.com/simple-sqlalchemy/simple-sqlalchemy"
},
"split_keywords": [
"sqlalchemy",
" orm",
" database",
" crud",
" pagination",
" search"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "02dc340f4dbf790e43129cee0aaf2770a9042fb477499425bb436976048ebaf7",
"md5": "4dec126b1e0f7816e3745a7f4dd3e873",
"sha256": "9950d5e0766174e393af40b51411bfb71681b92006d608eafbbbe5e55e81a1c0"
},
"downloads": -1,
"filename": "simple_sqlalchemy-0.1.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4dec126b1e0f7816e3745a7f4dd3e873",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 38824,
"upload_time": "2025-08-18T01:34:35",
"upload_time_iso_8601": "2025-08-18T01:34:35.289953Z",
"url": "https://files.pythonhosted.org/packages/02/dc/340f4dbf790e43129cee0aaf2770a9042fb477499425bb436976048ebaf7/simple_sqlalchemy-0.1.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "61dca6ea16738abd0d7a8780435ad656ee61586b94b245fc6decf6762545c9f4",
"md5": "b7d1ff4c713419a2e7844bfc1e12d28f",
"sha256": "0c4eabaac23a91c4761d881ce5cf7017bec8c6dd97252aadc77fd4c8ebd6fc7f"
},
"downloads": -1,
"filename": "simple_sqlalchemy-0.1.1.tar.gz",
"has_sig": false,
"md5_digest": "b7d1ff4c713419a2e7844bfc1e12d28f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 111808,
"upload_time": "2025-08-18T01:34:36",
"upload_time_iso_8601": "2025-08-18T01:34:36.265196Z",
"url": "https://files.pythonhosted.org/packages/61/dc/a6ea16738abd0d7a8780435ad656ee61586b94b245fc6decf6762545c9f4/simple_sqlalchemy-0.1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-18 01:34:36",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "simple-sqlalchemy",
"github_project": "simple-sqlalchemy",
"github_not_found": true,
"lcname": "simple-sqlalchemy"
}
JSON
