MySQL Support

Complete guide to using MySQL with Akron ORM - powerful relational database for web applications and enterprise systems.

Overview

MySQL is one of the world's most popular relational databases, known for its reliability, performance, and ease of use. It's perfect for web applications, e-commerce sites, and data warehousing applications.

🐬 MySQL Advantages

  • • High performance and scalability
  • • ACID compliance with InnoDB
  • • Rich SQL feature set
  • • Excellent replication support
  • • Strong community and enterprise support

Prerequisites

Before using MySQL with Akron, ensure you have the required dependency installed:

Install MySQL Connector
1# Install the MySQL connector
2pip install mysql-connector-python
3
4# Or using conda
5conda install mysql-connector-python

Connection Setup

Basic Connection

MySQL Connection Examples
1from akron import Akron
2
3# Basic connection
4db = Akron("mysql://user:password@localhost:3306/database")
5
6# Connection with specific options
7db = Akron("mysql://user:password@localhost:3306/database?charset=utf8mb4&autocommit=true")
8
9# Remote MySQL server
10db = Akron("mysql://admin:secret@mysql.example.com:3306/production_db")
11
12# Connection with SSL
13db = Akron("mysql://user:pass@localhost:3306/db?ssl_disabled=false&ssl_verify_cert=true")

Connection URL Format

mysql://[user]:[password]@[host]:[port]/[database]?[options]

user: MySQL username

password: MySQL password

host: Server hostname or IP (default: localhost)

port: MySQL port (default: 3306)

database: Database name

options: Additional connection parameters

Data Types

MySQL offers a rich set of data types. Akron automatically maps Python types to appropriate MySQL types:

Python TypeMySQL TypeRange/Notes
intINT-2,147,483,648 to 2,147,483,647
strVARCHAR(255)UTF-8 strings up to 255 chars
floatDOUBLEDouble precision floating point
boolBOOLEANTRUE/FALSE (stored as TINYINT)
datetimeDATETIMEYYYY-MM-DD HH:MM:SS
dateDATEYYYY-MM-DD

Complete Example

MySQL Full CRUD Example
1from akron import Akron
2from pydantic import BaseModel
3from akron.models import ModelMixin
4from datetime import datetime
5
6# Define your model
7class Product(BaseModel, ModelMixin):
8 id: int
9 name: str
10 description: str
11 price: float
12 in_stock: bool = True
13 created_at: datetime
14
15# Connect to MySQL database
16db = Akron("mysql://user:password@localhost:3306/ecommerce")
17
18# Create table
19Product.create_table(db)
20
21# Insert products
22product1 = Product(
23 id=1,
24 name="Laptop Pro",
25 description="High-performance laptop",
26 price=1299.99,
27 created_at=datetime.now()
28)
29
30product2 = Product(
31 id=2,
32 name="Wireless Mouse",
33 description="Ergonomic wireless mouse",
34 price=29.99,
35 created_at=datetime.now()
36)
37
38Product.insert(db, product1)
39Product.insert(db, product2)
40
41# Query products
42all_products = Product.select(db)
43print(f"Total products: {len(all_products)}")
44
45# Find expensive products
46expensive_products = Product.select(db, where={"price": {"$gt": 100}})
47print(f"Expensive products: {len(expensive_products)}")
48
49# Update product price
50Product.update(db, {"id": 1}, {"price": 1199.99})
51
52# Mark product as out of stock
53Product.update(db, {"id": 2}, {"in_stock": False})
54
55# Delete discontinued products
56Product.delete(db, {"in_stock": False})
57
58# Get final inventory
59inventory = Product.select(db, where={"in_stock": True})
60print(f"Products in stock: {len(inventory)}")

Performance Optimization

MySQL-Specific Optimizations

Indexing Strategy

Create indexes on frequently queried columns:

1# Create indexes for better query performance
2db.execute_raw("CREATE INDEX idx_product_name ON products(name);")
3db.execute_raw("CREATE INDEX idx_product_price ON products(price);")
4db.execute_raw("CREATE INDEX idx_product_created ON products(created_at);")
5
6# Composite index for complex queries
7db.execute_raw("CREATE INDEX idx_product_stock_price ON products(in_stock, price);")

Query Optimization

Use EXPLAIN to analyze query performance:

1# Analyze query execution plan
2result = db.execute_raw("EXPLAIN SELECT * FROM products WHERE price > 100 AND in_stock = true;")
3for row in result:
4 print(row)

Connection Pooling

MySQL connector automatically manages connection pooling:

1# Connection with pool settings
2db = Akron("mysql://user:pass@localhost:3306/db?pool_name=akron_pool&pool_size=10")

CLI Commands

Use Akron CLI for MySQL database management:

MySQL CLI Examples
1# Create a table
2akron create-table products --db "mysql://user:pass@localhost:3306/store" \
3 --schema '{"id": "int", "name": "str", "price": "float", "in_stock": "bool"}'
4
5# Inspect database schema
6akron inspect-schema --db "mysql://user:pass@localhost:3306/store"
7
8# Seed with sample data
9akron seed products --db "mysql://user:pass@localhost:3306/store" \
10 --data '[{"id": 1, "name": "Laptop", "price": 999.99, "in_stock": true}]'
11
12# Execute custom SQL
13akron raw-sql --db "mysql://user:pass@localhost:3306/store" \
14 --query "SHOW TABLES;"
15
16# Create migration
17akron makemigrations add_products_table \
18 --db "mysql://user:pass@localhost:3306/store" \
19 --schema '{"id": "int", "name": "str", "price": "float", "category": "str"}'
20
21# Run migrations
22akron migrate --db "mysql://user:pass@localhost:3306/store"

Advanced Features

Transactions

1# Manual transaction control
2try:
3 db.execute_raw("START TRANSACTION;")
4
5 # Multiple operations
6 Product.insert(db, product1)
7 Product.insert(db, product2)
8 Product.update(db, {"id": 1}, {"price": 1099.99})
9
10 db.execute_raw("COMMIT;")
11 print("Transaction completed successfully")
12except Exception as e:
13 db.execute_raw("ROLLBACK;")
14 print(f"Transaction failed: {e}")

Stored Procedures

1# Create a stored procedure
2procedure_sql = """
3CREATE PROCEDURE GetProductsByPrice(IN min_price DECIMAL(10,2))
4BEGIN
5 SELECT * FROM products WHERE price >= min_price ORDER BY price;
6END
7"""
8db.execute_raw(procedure_sql)
9
10# Call the stored procedure
11result = db.execute_raw("CALL GetProductsByPrice(100.00);")
12for row in result:
13 print(row)

Full-Text Search

1# Create full-text index
2db.execute_raw("ALTER TABLE products ADD FULLTEXT(name, description);")
3
4# Perform full-text search
5search_results = db.execute_raw(
6 "SELECT * FROM products WHERE MATCH(name, description) AGAINST('laptop wireless' IN NATURAL LANGUAGE MODE);"
7)
8for product in search_results:
9 print(f"Found: {product['name']}")

Best Practices

✅ Do

  • • Use InnoDB storage engine for ACID compliance
  • • Create appropriate indexes for query optimization
  • • Use prepared statements (Akron does this automatically)
  • • Monitor slow query log for performance issues
  • • Use connection pooling for high-traffic applications
  • • Regularly backup your databases

❌ Don't

  • • Store large binary data directly in MySQL
  • • Use SELECT * in production queries
  • • Ignore MySQL error logs
  • • Create too many indexes (affects write performance)
  • • Use MyISAM for applications requiring transactions
  • • Store sensitive data without encryption

Common Issues & Solutions

Access denied for user

Authentication failed. Check username, password, and user privileges.

1# Grant necessary privileges in MySQL
2GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';
3FLUSH PRIVILEGES;

Connection timeout

Network issues or server overload. Configure timeout settings.

1# Connection with timeout settings
2db = Akron("mysql://user:pass@host:3306/db?connect_timeout=10&read_timeout=30")

Table doesn't exist

Ensure the table exists or create it using Akron.

1# Check if table exists and create if needed
2try:
3 Product.select(db, limit=1)
4except Exception:
5 print("Table doesn't exist, creating...")
6 Product.create_table(db)

Related Documentation

Akron Constructor

Learn about connection setup and configuration

CLI Commands

Command-line tools for database management

PostgreSQL Support

Compare with PostgreSQL features

Model Definitions

Learn about Pydantic model integration