MySQL Query Tool

Universal MySQL database query tool supporting all SQL operations.

Features

• All SQL Operations: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.
• Connection Config: Save connection info for reuse
• Multiple Output Formats: Table (default) or JSON
• Natural Language: Convert natural language to SQL (see below)

Prerequisites

Install MySQL connector:

pip install mysql-connector-python

Usage

Basic Query

Execute SQL directly:

python scripts/mysql_query.py --sql "SELECT * FROM users LIMIT 10"

Save Connection Config

First time setup (saves to ~/.qclaw/mysql_config.json):

python scripts/mysql_query.py \
  --host localhost \
  --port 3306 \
  --user root \
  --password your_password \
  --database your_db \
  --sql "SELECT 1" \
  --save-config

After saving, simply provide --sql:

python scripts/mysql_query.py --sql "SELECT * FROM users"

Override Config

Temporarily use different connection:

python scripts/mysql_query.py \
  --host prod-db.example.com \
  --user admin \
  --password secret \
  --database production \
  --sql "SELECT COUNT(*) FROM orders"

Output Formats

Table format (default):

python scripts/mysql_query.py --sql "SELECT * FROM users" --format table

JSON format:

python scripts/mysql_query.py --sql "SELECT * FROM users" --format json

Natural Language to SQL

When user provides natural language, convert to SQL first, then execute.

Examples:

Workflow:

1. Understand user's natural language request
2. Generate corresponding SQL
3. Show the SQL to user for confirmation (optional but recommended)
4. Execute with mysql_query.py --sql ""
5. Return formatted results

Safety Notes

• This tool supports all SQL operations (no restrictions)
• User is responsible for ensuring safety of write operations
• Recommend confirming SQL with user before executing destructive operations (DROP, DELETE without WHERE, etc.)

Examples

Query Example

User: "查询 users 表前 10 条数据"

python scripts/mysql_query.py --sql "SELECT * FROM users LIMIT 10"

Output:

id   | name    | email           | created_at
----------------------------------------------
1    | Alice   | alice@test.com | 2024-01-01 10:00:00
2    | Bob     | bob@test.com   | 2024-01-02 11:00:00
...

Write Example

User: "把 user_id=5 的用户名改成 'Charlie'"

python scripts/mysql_query.py --sql "UPDATE users SET name='Charlie' WHERE id=5"

Output:

✅ Query executed successfully
Affected rows: 1

DDL Example

User: "创建一个 test_table 表，包含 id 和 name 字段"

python scripts/mysql_query.py --sql "CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100))"

Output:

✅ Query executed successfully
Affected rows: 0

Error Handling

Common errors and solutions:

• "mysql.connector not installed": Run pip install mysql-connector-python
• "Access denied": Check username/password
• "Unknown database": Check database name
• "Connection refused": Check host/port, ensure MySQL is running

References

For detailed examples and syntax reference, see:

• references/mysql_examples.md - Comprehensive query examples (JOINs, subqueries, aggregations, pagination, window functions, etc.)
• references/mysql_cheatsheet.md - Quick syntax reference (data types, functions, operators, common patterns)

Advanced Usage

Complex Queries

For complex queries (joins, subqueries, etc.), refer to references/mysql_examples.md for patterns.

Example - JOIN with aggregation:

SELECT 
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id
HAVING order_count > 5
ORDER BY total_amount DESC
LIMIT 20

Using Window Functions (MySQL 8.0+)

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

See references/mysql_examples.md for more window function examples.

Batch Operations

For batch operations, use transactions (autocommit is enabled by default, modify script if needed):

# Modify script to support --no-autocommit flag if needed

Troubleshooting

Q: Chinese characters appear as question marks?

A: Ensure database/table charset is utf8mb4, add --charset utf8mb4 if needed (modify script to support this).

Q: Connection timeout?

A: Check firewall settings, ensure MySQL allows remote connections (bind-address = 0.0.0.0).

Q: "Incorrect string value"?

A: Ensure utf8mb4 charset is used everywhere (database, table, connection).