Akron ORM - Universal Python ORM

Overview

The insert() method adds new records to database tables. It automatically handles auto-increment primary keys, validates foreign key constraints, and prevents duplicate entries on unique fields across all supported databases.

Signature

insert(table_name: str, data: Dict[str, Any]) -> int

Parameters

table_name

Type: str

Name of the table to insert data into. The table must exist before insertion.

data

Type: Dict[str, Any]

Dictionary mapping column names to their values. Must be a non-empty dictionary with valid column names and appropriate data types.

Returns

Type: int

The ID of the inserted record (from the database's lastrowid or equivalent). For auto-increment primary keys, this will be the generated ID value.

Examples

Basic Data Insertion

Simple User Insertion

1from akron import Akron
2
3db = Akron("sqlite:///example.db")
4
5# First, create the table
6db.create_table("users", {
7    "id": "int",
8    "username": "str",
9    "email": "str",
10    "age": "int",
11    "active": "bool"
12})
13
14# Insert a new user
15user_id = db.insert("users", {
16    "username": "alice_smith",
17    "email": "alice@example.com",
18    "age": 28,
19    "active": True
20})
21
22print(f"Inserted user with ID: {user_id}")
23
24# Insert another user (id will auto-increment)
25user_id_2 = db.insert("users", {
26    "username": "bob_jones", 
27    "email": "bob@example.com",
28    "age": 32,
29    "active": False
30})
31
32print(f"Inserted second user with ID: {user_id_2}")

Expected Output

Inserted user with ID: 1
Inserted second user with ID: 2

Inserting with Explicit IDs

You can specify explicit ID values when needed:

Explicit ID Insertion

1from akron import Akron
2
3db = Akron("sqlite:///products.db")
4
5db.create_table("categories", {
6    "id": "int",
7    "name": "str",
8    "description": "str"
9})
10
11# Insert categories with specific IDs
12electronics_id = db.insert("categories", {
13    "id": 100,
14    "name": "Electronics",
15    "description": "Electronic devices and accessories"
16})
17
18books_id = db.insert("categories", {
19    "id": 200,  
20    "name": "Books",
21    "description": "Physical and digital books"
22})
23
24print(f"Electronics category ID: {electronics_id}")
25print(f"Books category ID: {books_id}")

Expected Output

Electronics category ID: 100
Books category ID: 200

Foreign Key Relationships

Insert data with foreign key references:

Related Data Insertion

1from akron import Akron
2
3db = Akron("mysql://user:password@localhost/blog")
4
5# Create tables with relationships
6db.create_table("authors", {
7    "id": "int",
8    "name": "str",
9    "email": "str"
10})
11
12db.create_table("posts", {
13    "id": "int",
14    "title": "str",
15    "content": "str",
16    "author_id": "int->authors.id",
17    "published": "bool",
18    "created_at": "str"
19})
20
21# Insert an author first
22author_id = db.insert("authors", {
23    "name": "Jane Doe",
24    "email": "jane@blog.com"
25})
26
27print(f"Created author with ID: {author_id}")
28
29# Insert posts referencing the author
30post_id_1 = db.insert("posts", {
31    "title": "Getting Started with Akron ORM",
32    "content": "Akron ORM makes database operations simple...",
33    "author_id": author_id,  # Foreign key reference
34    "published": True,
35    "created_at": "2024-01-15"
36})
37
38post_id_2 = db.insert("posts", {
39    "title": "Advanced Database Relationships",
40    "content": "Learn how to work with complex schemas...",
41    "author_id": author_id,  # Same author
42    "published": False,
43    "created_at": "2024-01-16"
44})
45
46print(f"Created posts with IDs: {post_id_1}, {post_id_2}")

Expected Output

Created author with ID: 1
Created posts with IDs: 2, 3

Batch Insertions

Insert multiple records efficiently:

Multiple Record Insertion

1from akron import Akron
2
3db = Akron("postgres://user:password@localhost/inventory")
4
5db.create_table("products", {
6    "id": "int",
7    "name": "str", 
8    "price": "float",
9    "stock": "int",
10    "category": "str"
11})
12
13# Product data to insert
14products = [
15    {"name": "Laptop", "price": 999.99, "stock": 15, "category": "Electronics"},
16    {"name": "Mouse", "price": 29.99, "stock": 50, "category": "Electronics"},
17    {"name": "Desk Chair", "price": 199.99, "stock": 8, "category": "Furniture"},
18    {"name": "Monitor", "price": 299.99, "stock": 12, "category": "Electronics"}
19]
20
21# Insert each product
22inserted_ids = []
23for product in products:
24    product_id = db.insert("products", product)
25    inserted_ids.append(product_id)
26    print(f"Inserted {product['name']} with ID: {product_id}")
27
28print(f"\nAll product IDs: {inserted_ids}")

Expected Output

Inserted Laptop with ID: 1
Inserted Mouse with ID: 2
Inserted Desk Chair with ID: 3
Inserted Monitor with ID: 4

All product IDs: [1, 2, 3, 4]

Database-Specific Behavior

🗄️ SQLite

Returns cursor.lastrowid for the inserted record:

1# SQLite behavior
2user_id = db.insert("users", {"name": "Alice", "age": 25})
3# Returns the SQLite rowid (usually equals id column)

🐬 MySQL

Returns cursor.lastrowid from MySQL's AUTO_INCREMENT:

1# MySQL behavior  
2user_id = db.insert("users", {"name": "Bob", "age": 30})
3# Returns the AUTO_INCREMENT value

🐘 PostgreSQL

Returns the SERIAL primary key value:

1# PostgreSQL behavior
2user_id = db.insert("users", {"name": "Charlie", "age": 35})
3# Returns the SERIAL sequence value

🍃 MongoDB

Returns the ObjectId as an integer representation:

1# MongoDB behavior
2user_id = db.insert("users", {"name": "Diana", "age": 27})
3# Returns a unique identifier for the document

Error Handling

Handling Insertion Errors

1from akron import Akron
2from akron.exceptions import AkronError
3
4db = Akron("sqlite:///example.db")
5
6# Create table with unique constraint
7db.create_table("users", {
  "id": "int",
  "username": "str",  # This should be unique in real apps
  "email": "str"
11})
12
13try:
  # First insertion - this works
  user1_id = db.insert("users", {
      "username": "john_doe",
      "email": "john@example.com"
  })
  print(f"First user created with ID: {user1_id}")
  
  # This might fail if username/email uniqueness is enforced
  user2_id = db.insert("users", {
      "username": "john_doe",  # Duplicate username
      "email": "john2@example.com"
  })
  
27except AkronError as e:
  print(f"Insertion failed: {e}")
  if "UNIQUE constraint failed" in str(e):
      print("This username already exists!")
      
      # Insert with different username
      user2_id = db.insert("users", {
          "username": "john_doe_2",
          "email": "john2@example.com"
      })
      print(f"Second user created with ID: {user2_id}")
38
39# Foreign key constraint error
40try:
  # This will fail - author_id 999 doesn't exist
  db.insert("posts", {
      "title": "Test Post",
      "content": "Test content",
      "author_id": 999  # Non-existent author
  })
47except AkronError as e:
  print(f"Foreign key error: {e}")
  if "Foreign key constraint failed" in str(e):
      print("Referenced author does not exist!")

Expected Output

First user created with ID: 1
Insertion failed: Duplicate entry on unique field: UNIQUE constraint failed: users.username
This username already exists!
Second user created with ID: 2
Foreign key error: Foreign key constraint failed: FOREIGN KEY constraint failed
Referenced author does not exist!

CLI Usage

Insert data using the Akron CLI with the seed command:

CLI Data Insertion

1# Insert a single record
2akron seed users --db sqlite:///example.db --data '{"username": "alice", "email": "alice@example.com", "age": 28}'
3
4# Insert with foreign key reference
5akron seed posts --db sqlite:///blog.db --data '{"title": "My First Post", "content": "Hello world!", "author_id": 1}'
6
7# Insert product data
8akron seed products --db mysql://user:pass@localhost/shop --data '{"name": "Laptop", "price": 999.99, "stock": 10}'

Expected Output

Seeded data into users.
Seeded data into posts.
Seeded data into products.

Best Practices

🔒 Data Validation

Validate data before insertion to prevent constraint violations
Use appropriate data types (int, str, float, bool)
Handle potential duplicate key errors gracefully
Verify foreign key references exist before insertion

⚡ Performance

For bulk insertions, consider using transactions
Insert referenced tables (parent) before dependent tables (child)
Use batch processing for large datasets
Consider using specific IDs for lookup tables

🛡️ Error Handling

Always wrap insertions in try-catch blocks
Check for specific error types (unique, foreign key, etc.)
Provide meaningful error messages to users
Consider rollback strategies for failed batch insertions

📝 Data Consistency

Use consistent naming conventions for columns
Include timestamps (created_at, updated_at) when relevant
Validate foreign key values before insertion
Consider using UUIDs for distributed systems

Next Steps

After inserting data, you can retrieve and manipulate it:

Query Data

Find and retrieve your inserted records

→ learn about find()

Update Records

Modify existing data

→ learn about update()