Akron ORM - Universal Python ORM

Overview

The find() method retrieves records from database tables. It supports flexible filtering conditions, returns type-safe results as dictionaries, and works consistently across all supported databases including MongoDB.

Signature

find(table_name: str, filters: Optional[Dict[str, Any]] = None) -> List[Dict[str, Any]]

Parameters

table_name

Type: str

Name of the table to query. The table must exist before querying.

filters (optional)

Type: Optional[Dict[str, Any]]

Default: None

Dictionary mapping column names to values for filtering results. If None or empty, returns all records in the table. Supports exact value matching.

None - Returns all records
- Returns all records
{"column": value} - Returns records where column equals value
{"col1": val1, "col2": val2} - Returns records matching all conditions (AND)

Returns

Type: List[Dict[str, Any]]

List of dictionaries, where each dictionary represents a record with column names as keys and their corresponding values. Returns an empty list if no records match the filters.

Examples

Basic Queries

Retrieve All Records

1from akron import Akron
2
3db = Akron("sqlite:///example.db")
4
5# Setup: Create table and insert sample data
6db.create_table("users", {
7    "id": "int",
8    "username": "str",
9    "email": "str", 
10    "age": "int",
11    "active": "bool"
12})
13
14# Insert some test data
15db.insert("users", {"username": "alice", "email": "alice@example.com", "age": 28, "active": True})
16db.insert("users", {"username": "bob", "email": "bob@example.com", "age": 32, "active": False})
17db.insert("users", {"username": "charlie", "email": "charlie@example.com", "age": 25, "active": True})
18
19# Find all users
20all_users = db.find("users")
21print("All users:")
22for user in all_users:
23    print(f"  {user}")
24
25# Count total users  
26print(f"\nTotal users: {len(all_users)}")

Expected Output

All users:
  {'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 28, 'active': True}
  {'id': 2, 'username': 'bob', 'email': 'bob@example.com', 'age': 32, 'active': False}
  {'id': 3, 'username': 'charlie', 'email': 'charlie@example.com', 'age': 25, 'active': True}

Total users: 3

Filtered Queries

Use filters to find specific records:

Single Condition Filtering

1# Find only active users
2active_users = db.find("users", {"active": True})
3print("Active users:")
4for user in active_users:
5    print(f"  {user['username']} (age {user['age']})")
6
7# Find user by ID
8user_by_id = db.find("users", {"id": 2})
9print(f"\nUser with ID 2: {user_by_id}")
10
11# Find user by username
12user_by_name = db.find("users", {"username": "alice"})
13print(f"\nUser 'alice': {user_by_name}")
14
15# Find users by age
16young_users = db.find("users", {"age": 25})
17print(f"\nUsers age 25: {young_users}")

Expected Output

Active users:
  alice (age 28)
  charlie (age 25)

User with ID 2: [{'id': 2, 'username': 'bob', 'email': 'bob@example.com', 'age': 32, 'active': False}]

User 'alice': [{'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 28, 'active': True}]

Users age 25: [{'id': 3, 'username': 'charlie', 'email': 'charlie@example.com', 'age': 25, 'active': True}]

Multiple Conditions (AND)

Use multiple filter conditions to narrow down results:

Multiple Filter Conditions

1# Find active users who are 28 years old
2specific_users = db.find("users", {
3    "active": True,
4    "age": 28
5})
6print(f"Active users age 28: {specific_users}")
7
8# Find inactive users named bob
9inactive_bob = db.find("users", {
10    "active": False,
11    "username": "bob"
12})
13print(f"\nInactive user named bob: {inactive_bob}")
14
15# Multiple conditions that return no results
16no_results = db.find("users", {
17    "active": True,
18    "age": 50  # No users this age
19})
20print(f"\nUsers active and age 50: {no_results}")

Expected Output

Active users age 28: [{'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 28, 'active': True}]

Inactive user named bob: [{'id': 2, 'username': 'bob', 'email': 'bob@example.com', 'age': 32, 'active': False}]

Users active and age 50: []

Working with Related Data

Query tables with foreign key relationships:

Querying Related Tables

1# Setup blog schema
2db.create_table("authors", {
3    "id": "int",
4    "name": "str",
5    "email": "str"
6})
7
8db.create_table("posts", {
9    "id": "int",
10    "title": "str", 
11    "content": "str",
12    "author_id": "int->authors.id",
13    "published": "bool",
14    "views": "int"
15})
16
17# Insert sample data
18author1_id = db.insert("authors", {"name": "Jane Doe", "email": "jane@blog.com"})
19author2_id = db.insert("authors", {"name": "John Smith", "email": "john@blog.com"})
20
21db.insert("posts", {"title": "Python Tips", "content": "Great tips...", "author_id": author1_id, "published": True, "views": 150})
22db.insert("posts", {"title": "Database Design", "content": "Schema best practices...", "author_id": author1_id, "published": False, "views": 45})
23db.insert("posts", {"title": "Web Development", "content": "Modern frameworks...", "author_id": author2_id, "published": True, "views": 200})
24
25# Find all posts by a specific author
26jane_posts = db.find("posts", {"author_id": author1_id})
27print(f"Posts by Jane Doe ({author1_id}):")
28for post in jane_posts:
29    print(f"  '{post['title']}' - Published: {post['published']}, Views: {post['views']}")
30
31# Find only published posts
32published_posts = db.find("posts", {"published": True})
33print(f"\nPublished posts: {len(published_posts)}")
34
35# Find high-traffic published posts
36popular_posts = db.find("posts", {"published": True, "views": 200})
37print(f"Popular published posts: {popular_posts}")

Expected Output

Posts by Jane Doe (1):
  'Python Tips' - Published: True, Views: 150
  'Database Design' - Published: False, Views: 45

Published posts: 2
Popular published posts: [{'id': 3, 'title': 'Web Development', 'content': 'Modern frameworks...', 'author_id': 2, 'published': True, 'views': 200}]

Processing Query Results

Work with the returned data efficiently:

Result Processing Patterns

1# Get all users and process them
2users = db.find("users")
3
4# Extract specific fields
5usernames = [user["username"] for user in users]
6print(f"Usernames: {usernames}")
7
8# Filter results in Python (after database query)
9adult_users = [user for user in users if user["age"] >= 30]
10print(f"\nAdult users: {len(adult_users)}")
11
12# Calculate statistics
13total_age = sum(user["age"] for user in users)
14average_age = total_age / len(users) if users else 0
15print(f"Average age: {average_age:.1f}")
16
17# Find specific user safely
18def find_user_by_username(username):
19    results = db.find("users", {"username": username})
20    return results[0] if results else None
21
22alice = find_user_by_username("alice")
23if alice:
24    print(f"\nFound Alice: {alice['email']}")
25else:
26    print("\nAlice not found")
27
28# Check if any records exist
29has_inactive_users = bool(db.find("users", {"active": False}))
30print(f"Has inactive users: {has_inactive_users}")

Expected Output

Usernames: ['alice', 'bob', 'charlie']

Adult users: 1

Average age: 28.3

Found Alice: alice@example.com
Has inactive users: True

Database-Specific Behavior

🗄️ SQLite

Uses standard SQL SELECT statements with WHERE clauses:

1# Generated SQL for SQLite
2SELECT * FROM users WHERE active = ? AND age = ?
3# Parameters: [True, 28]

🐬 MySQL

Uses MySQL-compatible SQL with proper parameter binding:

1# Generated SQL for MySQL
2SELECT * FROM users WHERE active = %s AND age = %s
3# Parameters: [True, 28]

🐘 PostgreSQL

Uses PostgreSQL syntax with proper type handling:

1# Generated SQL for PostgreSQL
2SELECT * FROM users WHERE active = %s AND age = %s
3# Parameters: [True, 28]

🍃 MongoDB

Translates filters to MongoDB query documents:

1# MongoDB query equivalent
2db.users.find({"active": True, "age": 28})
3# Returns cursor converted to list of dictionaries

Error Handling

Handling Query Errors

1from akron import Akron
2from akron.exceptions import AkronError, TableNotFoundError
3
4db = Akron("sqlite:///example.db")
5
6try:
7    # This will fail if table doesn't exist
8    results = db.find("nonexistent_table")
9    
10except TableNotFoundError as e:
11    print(f"Table not found: {e}")
12    
13except AkronError as e:
14    print(f"Query error: {e}")
15
16# Safe querying with existence check
17def safe_find(table_name, filters=None):
18    try:
19        return db.find(table_name, filters)
20    except TableNotFoundError:
21        print(f"Table '{table_name}' does not exist")
22        return []
23    except AkronError as e:
24        print(f"Query failed: {e}")
25        return []
26
27# Use safe querying
28users = safe_find("users", {"active": True})
29missing_table_data = safe_find("missing_table")

Expected Output

Table not found: no such table: nonexistent_table
Table 'missing_table' does not exist

CLI Usage

Query data using raw SQL with the Akron CLI:

CLI Querying

1# Query all records
2akron raw-sql --db sqlite:///example.db --sql "SELECT * FROM users"
3
4# Query with conditions
5akron raw-sql --db sqlite:///example.db --sql "SELECT username, age FROM users WHERE active = 1"
6
7# Query with joins (if supported)
8akron raw-sql --db mysql://user:pass@localhost/blog --sql "SELECT posts.title, authors.name FROM posts JOIN authors ON posts.author_id = authors.id"
9
10# Count records
11akron raw-sql --db postgres://user:pass@localhost/db --sql "SELECT COUNT(*) FROM users WHERE age >= 30"

Expected Output

(1, 'alice', 'alice@example.com', 28, 1)
(2, 'bob', 'bob@example.com', 32, 0)
(3, 'charlie', 'charlie@example.com', 25, 1)
SQL executed.

Best Practices

🎯 Query Optimization

Use specific filters to limit result sets rather than fetching all data
Filter at the database level rather than in Python when possible
Consider creating indexes on frequently queried columns
Avoid querying large tables without filters in production

🔍 Result Processing

Check if results list is empty before accessing elements
Use list comprehensions for efficient data transformation
Consider pagination for large result sets
Cache frequently accessed query results when appropriate

🛡️ Safety

Always handle the case where no results are found
Validate filter values before querying
Use try-catch blocks for database-related operations
Be careful with user-provided filter values

📊 Data Access Patterns

Query by primary key (id) for single record lookup
Use boolean flags for status-based filtering
Query related data separately, then combine in Python
Consider denormalizing data for frequently accessed combinations

Next Steps

After retrieving data, you can modify or remove it:

Update Records

Modify existing data based on your queries

→ learn about update()

Delete Records

Remove unwanted data

→ learn about delete()