Troubleshooting

Common issues and solutions when using sqlo.

Installation Issues

Import Errors

Problem: ModuleNotFoundError: No module named 'sqlo'

Solution:

# Install sqlo
pip install sqlo

# Or with uv
uv add sqlo

# Verify installation
python -c "import sqlo; print(sqlo.__version__)"

Version Conflicts

Problem: Incompatible Python version

Solution:

# sqlo requires Python 3.9+
python --version

# If using older version, upgrade Python
# Or use pyenv/uv to manage Python versions

Query Building Errors

Missing WHERE Clause

Problem: ValueError: UPDATE without WHERE clause would affect all rows

Solution:

# ❌ Error: No WHERE clause
query = Q.update("users").set({"active": False})

# ✅ Fix 1: Add WHERE clause
query = Q.update("users").set({"active": False}).where("id", 123)

# ✅ Fix 2: Explicitly allow all rows
query = Q.update("users").set({"active": False}).allow_all_rows()

Same applies to DELETE:

# ❌ Error
query = Q.delete_from("users")

# ✅ Fix
query = Q.delete_from("users").where("inactive", True)
# Or
query = Q.delete_from("users").allow_all_rows()

CTE Without Name

Problem: ValueError: CTE must have a name. Use .as_('name') method.

Solution:

# ❌ Error: CTE without name
cte = Q.select("*").from_("users").where("active", True)
query = Q.select("*").with_(cte).from_("active_users")

# ✅ Fix: Name the CTE
cte = Q.select("*").from_("users").where("active", True).as_("active_users")
query = Q.select("*").with_(cte).from_("active_users")

Invalid Identifiers

Problem: ValueError: Invalid table/column name

Solution:

# ❌ Error: Invalid characters
query = Q.select("*").from_("users; DROP TABLE users")

# ✅ Fix: Use only valid identifiers (alphanumeric, underscore, dot)
query = Q.select("*").from_("users")

# For complex expressions, use Raw
from sqlo import Raw
query = Q.select(Raw("COUNT(*)")).from_("users")

Empty IN Clause

Problem: Unexpected behavior with empty lists in WHERE IN

Solution:

# sqlo automatically handles empty lists
ids = []

# Generates: WHERE FALSE (never matches)
query = Q.select("*").from_("users").where_in("id", ids)

# NOT IN with empty list generates: WHERE TRUE (matches all)
query = Q.select("*").from_("users").where_not_in("id", ids)

# Better: Check before building query
if ids:
    query = Q.select("*").from_("users").where_in("id", ids)
else:
    # Handle empty case explicitly
    query = Q.select("*").from_("users").where(Raw("FALSE"))

SQL

Generation Issues

Incorrect Column Quoting

Problem: Columns not properly quoted

Expected: Columns should be automatically quoted with backticks

Solution:

# Columns are auto-quoted
query = Q.select("order", "group").from_("select")
# SELECT `order`, `group` FROM `select`

# For raw SQL, quote manually
from sqlo import Raw
query = Q.select(Raw("`order` + 1")).from_("items")

Parameter Binding Issues

Problem: Parameters not properly bound

Solution:

# ✅ Correct: Parameterized
query = Q.select("*").from_("users").where("name", user_input)
sql, params = query.build()
# SQL: WHERE `name` = %s
# Params: (user_input,)

# ❌ Incorrect: String interpolation (SQL injection risk!)
query = Q.select("*").from_("users").where(Raw(f"name = '{user_input}'"))

Runtime Errors

MySQL Errors

Problem: MySQL Error 1064: Syntax error

Debug: Use debug mode to see generated SQL

query = Q.select("*").from_("users").where("id", 123).debug()
sql, params = query.build()
# Prints SQL and parameters

Common causes:

Reserved keywords not quoted
Incorrect dialect
Complex Raw expressions

Solution:

# 1. Reserved keywords (auto-handled)
query = Q.select("order").from_("users")  # OK

# 2. Check dialect
Q.set_dialect("mysql")  # Default

# 3. Verify Raw expressions
from sqlo import Raw
# Make sure Raw SQL is valid
query = Q.select(Raw("COUNT(*)")).from_("users")

Type Errors

Problem: TypeError: 'NoneType' object is not iterable

Common cause: Missing return value or None passed where list expected

Solution:

# ❌ Error
values = None
query = Q.insert_into("users").values(values)

# ✅ Fix
values = [{"name": "Alice", "email": "alice@example.com"}]
query = Q.insert_into("users").values(values)

# Or check first
if values:
    query = Q.insert_into("users").values(values)

Performance Issues

Slow Queries

Problem: Query takes too long to execute

Debug:

# Use EXPLAIN to analyze query plan
query = Q.select("*").from_("users").where("email", "test@example.com")
explain_query = query.explain()
sql, params = explain_query.build()

# Execute with database
# cursor.execute(sql, params)
# for row in cursor.fetchall():
#     print(row)

Common fixes:

Add indexes on WHERE columns
Use LIMIT to restrict results
Avoid SELECT *
Use joins instead of subqueries (or vice versa)

# ❌ Slow: No limit
query = Q.select("*").from_("large_table").where("status", "active")

# ✅ Better: Add limit
query = Q.select("*").from_("large_table").where("status", "active").limit(100)

# ✅ Best: Select only needed columns
query = Q.select("id", "name").from_("large_table").where("status", "active").limit(100)

Memory Issues

Problem: Out of memory when processing large result sets

Solution: Use pagination or streaming

# Pagination
page = 1
per_page = 1000

while True:
    query = (
        Q.select("*")
        .from_("large_table")
        .limit(per_page)
        .offset((page - 1) * per_page)
    )
    
    # cursor.execute(*query.build())
    # rows = cursor.fetchall()
    # if not rows:
    #     break
    
    # Process batch
    # for row in rows:
    #     process(row)
    
    page += 1

Testing Issues

Assertion Failures

Problem: Generated SQL doesn’t match expected

Solution: Use debug mode and compare

query = Q.select("id", "name").from_("users").where("active", True)
sql, params = query.build()

# Debug: Print actual SQL
print(f"SQL: {sql}")
print(f"Params: {params}")

# Compare with expected
expected_sql = "SELECT `id`, `name` FROM `users` WHERE `active` = %s"
assert sql == expected_sql
assert params == (True,)

Mock Database Issues

Problem: Tests fail with mock database

Solution: Test query building separately from execution

# Test query building
def test_user_query():
    query = Q.select("*").from_("users").where("id", 123)
    sql, params = query.build()
    
    assert "SELECT" in sql
    assert "users" in sql
    assert params == (123,)

# Test execution separately with real/test database
def test_user_query_execution(db_connection):
    query = Q.select("*").from_("users").where("id", 123)
    cursor = db_connection.cursor()
    cursor.execute(*query.build())
    result = cursor.fetchone()
    assert result is not None

IDE and Type Checking

Type Hints Not Working

Problem: IDE doesn’t show autocomplete

Solution:

# Make sure you have type stubs
from sqlo import Q, Condition, Raw, func

# Use explicit types if needed
from sqlo.query.select import SelectQuery

query: SelectQuery = Q.select("*").from_("users")

mypy Errors

Problem: mypy shows type errors

Common causes:

Mixed types in values
Incorrect method chaining

Solution:

# Configure mypy (pyproject.toml)
# [tool.mypy]
# ignore_missing_imports = true

# Or use type: ignore for specific lines
query = Q.select("*").from_("users")  # type: ignore

Common Mistakes

Forgetting to Call build()

Problem: Passing query object instead of SQL

Solution:

query = Q.select("*").from_("users").where("id", 123)

# ❌ Wrong: Passing query object
# cursor.execute(query)

# ✅ Correct: Extract SQL and params
sql, params = query.build()
cursor.execute(sql, params)

Reusing Query Objects

Problem: Query objects are mutable

Solution:

# Query objects can be reused and modified
base_query = Q.select("*").from_("users")

# These create new objects (method chaining returns new instances)
active_users = base_query.where("active", True)
inactive_users = base_query.where("active", False)

# Both are independent

Mixing Dialects

Problem: Using features from different SQL dialects

Solution:

# Set dialect globally
Q.set_dialect("mysql")

# Or per query (if supported in future versions)
# Currently sqlo only supports MySQL

Getting Help

Debug Mode

Always start by enabling debug mode:

# Global debug
Q.set_debug(True)

query = Q.select("*").from_("users").where("id", 123)
sql, params = query.build()
# Automatically prints SQL and params

# Per-query debug
query = Q.select("*").from_("users").where("id", 123).debug()

Check Version

import sqlo
print(sqlo.__version__)

# Make sure you're using the latest version
# pip install --upgrade sqlo

Report Issues

If you encounter a bug:

Check existing issues: https://github.com/nan-guo/sqlo/issues
Create minimal reproducible example
Include:
- sqlo version
- Python version
- Generated SQL (from debug mode)
- Expected vs actual behavior

# Minimal example for bug report
from sqlo import Q

query = Q.select("*").from_("users").where("id", 123)
sql, params = query.build()

print(f"sqlo version: {sqlo.__version__}")
print(f"SQL: {sql}")
print(f"Params: {params}")
print(f"Expected: ...")

Troubleshooting

Installation Issues

Import Errors

Version Conflicts

Query Building Errors

Missing WHERE Clause

CTE Without Name

Invalid Identifiers

Empty IN Clause

SQL

Incorrect Column Quoting

Parameter Binding Issues

Runtime Errors

MySQL Errors

Type Errors

Performance Issues

Slow Queries

Memory Issues

Testing Issues

Assertion Failures

Mock Database Issues

IDE and Type Checking

Type Hints Not Working

mypy Errors

Common Mistakes

Forgetting to Call build()

Reusing Query Objects

Mixing Dialects

Getting Help

Debug Mode

Check Version

Report Issues

See Also