Metadata-Version: 2.4
Name: python-data-access
Version: 1.4.0
Summary: Lightweight and fast database access layer with query builder for MySQL, SQLite, and Flatfile databases
Home-page: https://github.com/expandmade-tb/python-data-access
Author: tbednarek
Author-email: thomas.bednarek@expandmade.com
License: MIT
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Operating System :: POSIX :: Linux
Classifier: Programming Language :: Python :: 3
Description-Content-Type: text/markdown
Requires-Dist: mysql-connector-python
Dynamic: author
Dynamic: author-email
Dynamic: classifier
Dynamic: description
Dynamic: description-content-type
Dynamic: home-page
Dynamic: license
Dynamic: requires-dist
Dynamic: summary


# Python Data Access (easydb)

[![PyPI Version](https://img.shields.io/pypi/v/python-data-access.svg)](https://pypi.org/project/python-data-access/)
[![Python Versions](https://img.shields.io/pypi/pyversions/python-data-access.svg)](https://pypi.org/project/python-data-access/)

A lightweight, fast, and secure database access layer and query builder for **MySQL**, **SQLite**, and **Flatfile** databases.

`easydb` simplifies database interactions for your Python applications by providing a single, unified interface. Write your data access logic once, and seamlessly migrate from a local Flatfile or SQLite database to a production MySQL database without changing a single line of query code.

### 💡 Why choose easydb?

- **Unified Interface:** Swap database engines instantly. Start with a flat file, move to SQLite, and scale to MySQL using the exact same Python code.
- **Query Builder:** Chainable, intuitive methods (`where`, `limit`, `orderby`) replace messy string concatenation.
- **Secure by Default:** Parameterized queries under the hood significantly reduce the risk of SQL injection attacks.
- **Modern & Type-Hinted:** Fully type-hinted architecture provides excellent autocompletion and static analysis (Pylance/MyPy) in modern IDEs.
- **Lightweight:** No bloated ORM overhead—just direct, efficient data access.

---

### 📦 Installation

```bash
pip install python-data-access
```

## 🚀 Quick Start

```python
from easydb import pda

# 1. Connect to SQLite (or switch to MySQL/Flatfile just by changing this line)
db = pda.Database().db_sq3('app.sqlite')

# 2. Open or define a table
class Customers(pda.Table):
    _name: str = 'Customers'

    def ddl(self):
        return pda.DDL(self._name) \
            .integer('id', auto_increment=True) \
            .text('email', size=64, not_null=True, unique=True) \
            .integer('active')

customers = Customers()

# 3. Insert data securely
customers.insert({'email': 'hello@example.com', 'active': 1})

# 4. Query with the chainable builder
active_users = customers.where('active', 1).orderby('email').findall()
```

---

## 🎮 Running the Demo

If you want to see `easydb` in action before writing any code of your own, check out the included demo script! 

The `example` directory contains a complete, working `demo.py` that sets up a local SQLite database, creates multiple linked tables (Customers, Orders, Products), inserts sample data, and runs through various query and update scenarios.

To run it, simply clone the repository change to the example dir and execute:
```bash
python3 demo.py
```

---

## 📖 API Documentation

### 🔌 1. Database Connections

Connect once, and the module handles the rest.

**SQLite**
```python
db = pda.Database().db_sq3('/path/to/dbtest.sqlite')
```
**MySQL**
```python
db = pda.Database().db_msq(host='localhost', database='dbtest', user='user', password='password')
```
**Flatfile** (Stores data directly in the OS filesystem—great for environments lacking SQL engines)
```python
db = pda.Database().db_flat(datapath='/path/to', database='dbtest.flat')
```

---

### 🏗️ 2. Table Management & DDL

You can open existing tables or define their schema (DDL) directly in Python.

**Open an existing table:**
```python
products = pda.Table('Products')
```

**Define a table schema using the `DDL` Builder:**
Using keyword arguments makes your schema highly readable.
```python
class Products(pda.Table):
    _name: str = 'Products'

    def ddl(self):
        return pda.DDL(self._name) \
            .integer('ProductId', auto_increment=True) \
            .text('Description', size=64, not_null=True, unique=True) \
            .real('Price') \
            .integer('Inactive')

products = Products()
```

**Drop a table:**
```python
Products().drop()
```

---

### 💾 3. CRUD Operations

**Insert:**
```python
result = products.insert({'Description': 'A Product Description', 'Price': 25})
```

**Update:**
```python
# Update by primary key
products.update(1, {'Price': 19.99})

# Bulk update with conditions
products.where('Inactive', 1).where('Description', 'A%', 'like').updateall({'Price': 0})
```

**Delete:**
```python
# Delete by primary key
products.delete(1)

# Bulk delete
products.where('Inactive', 1).deleteall()
```

---

### 🔍 4. Query Builder

The chainable query builder allows for expressive data retrieval.

```python
# Find by Primary Key
item = products.find(1)

# Count records
total_active = products.where('Inactive', 0).count()

# Chain limits, offsets, and ordering
results = products.where('Inactive', 0) \
                  .orderby('Description') \
                  .limit(10) \
                  .offset(10) \
                  .findall()

# Get the single first matching row
first_match = products.where('Price', 100, '<').findfirst()
```

---

### 🛡️ 5. Database Transactions

Ensure data integrity by grouping operations into database-level transactions.

```python
db.beginimmediate()

products.update(1, {'Price': 25.00})
order_details.where('ProductId', 1).updateall({'Price': 25.00})

db.committransaction()
```

---

### 📜 6. Custom SQL Execution

For complex joins or reports, load SQL from external files and execute it securely. 

Let's assume you have a file `production.sql`:
```sql
SELECT production_date, description, quantity
FROM Production
LEFT JOIN Products ON Production.product_id = Products.product_id
ORDER BY production_date, description
```

You can load and filter this query directly through the API:
```python
sql = products.getsql('production.sql')

# Apply query builder limits and filters to your raw SQL
result = products.limit(10).findall(sql)
result = products.where('Description', 'A%', 'like').findall(sql)
```

---

### 🔄 7. Utilities (CSV Import/Export)

```python
products.import_csv('product_importdata.csv')
products.export_csv('product_exportdata.csv')
```

---

## 🧪 Testing & Benchmarks

Test your database engines individually:
```bash
python3 -m unittest discover tests test_pda_mysql.py
python3 -m unittest discover tests test_pda_sqlite.py
python3 -m unittest discover tests test_pda_flat.py
```

Run the built-in benchmarking tool to compare database performance:
```bash
python3 benchmarks.py --rows 1000
```
