56 lines
1.9 KiB
Markdown
56 lines
1.9 KiB
Markdown
# Database Migration & Data Upload Guide
|
|
|
|
This project uses **Flask-Migrate** (powered by Alembic) for managing database schema changes and a custom Flask CLI command for uploading XLS data.
|
|
|
|
## Migration Workflow
|
|
|
|
Follow these steps to manage database schema and upload data:
|
|
|
|
1. **Set Flask App Environment Variable:**
|
|
Open your terminal in the project root (`digifi-Analytics/`) and set the `FLASK_APP` variable:
|
|
|
|
```bash
|
|
# For Windows
|
|
set FLASK_APP=run.py
|
|
|
|
# For macOS/Linux
|
|
export FLASK_APP=run.py
|
|
```
|
|
|
|
2. **Initialize Migration Repository (First-time setup only):**
|
|
This command sets up the `migrations/` directory structure. You've likely already run this, so you might get a "Directory already exists" message, which is fine.
|
|
|
|
```bash
|
|
flask --app run.py db init
|
|
```
|
|
|
|
3. **Generate a New Migration Script:**
|
|
After making changes to `salary_analytics/app/models.py` (e.g., adding a new table or column), use this command to create a migration file. This file will contain the `upgrade()` and `downgrade()` logic.
|
|
|
|
```bash
|
|
flask --app run.py db migrate -m "Descriptive message for your changes"
|
|
```
|
|
|
|
4. **Review the Generated Migration:**
|
|
Open the newly created `.py` file in `migrations/versions/` (e.g., `XXXXXXXXXXXX_your_message.py`) and review the `upgrade()` and `downgrade()` functions. Ensure they accurately reflect your intended schema changes.
|
|
|
|
5. **Apply Migrations to the Database:**
|
|
This command executes the pending migration scripts, updating your database schema. **Always apply migrations after generating them.**
|
|
|
|
```bash
|
|
flask --app run.py db upgrade
|
|
```
|
|
|
|
## Uploading XLS Data
|
|
|
|
Once your `analytics_raw_transactions` table is created via migration, you can upload your XLS files using the custom command:
|
|
|
|
```bash
|
|
flask --app run.py upload-xls <path_to_your_xls_file>
|
|
```
|
|
|
|
**Example:**
|
|
|
|
```bash
|
|
flask --app run.py upload-xls data/transactions.xls
|
|
``` |