Metadata-Version: 2.4
Name: easy-mysql-builder
Version: 0.3.0
Summary: Beginner-friendly helpers for generating SQL queries and talking to MySQL.
Requires-Python: >=3.8
Description-Content-Type: text/markdown
Provides-Extra: mysql
Requires-Dist: mysql-connector-python>=8.0; extra == "mysql"

# easy-mysql-builder

Generate common MySQL queries with simple Python functions, and run them
against a MySQL database. Built for beginners learning SQL.

## Install

```bash
pip install -e .                 # from this folder
pip install mysql-connector-python   # only needed for database access
```

## Quick tour

```python
from easy_mysql_builder import select, insert, update, preview, gt, like, between

# SELECT
sql, params = select("students", ["name", "age"],
                     where={"age": gt(18), "city": "Chennai"},
                     order_by="-age", limit=5)
print(preview(sql, params))
# SELECT `name`, `age` FROM `students` WHERE `age` > 18 AND `city` = 'Chennai'
#   ORDER BY `age` DESC LIMIT 5;

# INSERT
sql, params = insert("students", {"name": "Asha", "age": 20})

# UPDATE (refuses to run without a WHERE unless allow_all=True)
sql, params = update("students", {"age": 21}, where={"name": "Asha"})
```

### Filtering conditions

| You write                         | SQL produced                |
|-----------------------------------|-----------------------------|
| `{"city": "Chennai"}`             | `city = %s`                 |
| `{"age": gt(18)}`                 | `age > %s`                  |
| `{"age": between(18, 30)}`        | `age BETWEEN %s AND %s`     |
| `{"name": like("A%")}`            | `name LIKE %s`              |
| `{"id": [1, 2, 3]}`               | `id IN (%s, %s, %s)`        |
| `{"email": None}`                 | `email IS NULL`             |

Also available: `gte, lt, lte, ne, not_like, in_, not_in, is_null, not_null`.
Use `join="OR"` to combine conditions with OR.

### DELETE, counting, joins and more

```python
from easy_mysql_builder import (delete, upsert, count, group_by, exists, join_select,
                       gt, MySQLDatabase)

# DELETE (refuses to run without a WHERE unless allow_all=True)
sql, params = delete("students", where={"age": gt(60)})

# Insert, or update the row if the key already exists
sql, params = upsert("students", {"id": 1, "name": "Asha", "age": 21})

# COUNT / GROUP BY / EXISTS
sql, params = count("students", where={"city": "Chennai"})   # column: total
sql, params = group_by("marks", "subject", "AVG", "score",
                       having=gt(50), order_by="-result")     # column: result
sql, params = exists("students", {"email": "asha@example.com"})  # column: found

# JOIN (use table.column names)
sql, params = join_select("students", "marks", on=("id", "student_id"),
                          columns=["students.name", "marks.score"],
                          where={"marks.score": gt(50)}, kind="LEFT")

# Read a single value
total = db.fetch_value(*count("students"))
```

Also available: `aggregate` (SUM / AVG / MIN / MAX of one column).

### Creating and changing tables

```python
from easy_mysql_builder import (create_table, add_column, create_index, describe,
                       show_tables, drop_column, drop_table, truncate)

db.execute(*create_table("students",
           {"id": "INT AUTO_INCREMENT", "name": "VARCHAR(50) NOT NULL", "age": "INT"},
           primary_key="id"))
db.execute(*add_column("students", "email", "VARCHAR(80)"))
db.execute(*create_index("students", "name"))
print(db.fetch_all(*describe("students")))

# Destructive: these need confirm=True
db.execute(*drop_table("students", confirm=True))
```

Column types are checked against an allow-list (INT, BIGINT, VARCHAR(n),
DECIMAL(p,s), TEXT, DATE, DATETIME, ...) with the modifiers NOT NULL, NULL,
AUTO_INCREMENT, UNIQUE, UNSIGNED and PRIMARY KEY.

### Keep your password out of your code

Put your settings in a file named `.env` (copy `.env.example` and edit it):

```
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=school
DB_PORT=3306
```

Then connect without typing the password anywhere in your script:

```python
from easy_mysql_builder import MySQLDatabase, select

with MySQLDatabase.from_env(env_file=".env") as db:
    print(db.fetch_all(*select("students")))
```

Variables already set in your system are never overwritten by the file.
Add `.env` to `.gitignore` (already included) so it is never shared.
Use `from_env(prefix="SCHOOL_")` to read `SCHOOL_HOST`, `SCHOOL_USER`, ...

### Transactions: all changes succeed or none do

`db.execute` normally saves (commits) after every statement. To group
several statements together, use a transaction:

```python
from easy_mysql_builder import update

with db.transaction():
    db.execute(*update("accounts", {"balance": 900}, where={"id": 1}))
    db.execute(*update("accounts", {"balance": 1100}, where={"id": 2}))
# both saved together; if anything fails inside, BOTH are undone
```

MySQL saves automatically on `CREATE`, `DROP`, `TRUNCATE` and `ALTER`, so keep
those outside a transaction.

Get a single value quickly:

```python
total = db.fetch_value(*count("students"))
```

### Talking to MySQL

```python
from easy_mysql_builder import MySQLDatabase, select, insert, gt

with MySQLDatabase(user="root", password="secret", database="school") as db:
    db.execute(*insert("students", {"name": "Asha", "age": 20}))
    rows = db.fetch_all(*select("students", where={"age": gt(18)}))
    for row in rows:
        print(row["name"], row["age"])
```

`db.fetch_all(*select(...))` unpacks the `(sql, params)` tuple for you.

## Safety notes
- Values are sent as parameters (`%s`), never pasted into the SQL text.
- Table and column names are validated (letters, digits, underscores only).
- `update()` and `delete()` need a `where` (or `allow_all=True`); `drop_table()`,
  `truncate()` and `drop_column()` need `confirm=True`.
- `preview()` is for learning/printing only; never execute its output.

## Run the tests

```bash
python -m unittest discover tests
```
