GOAT Addendum: Maru and the perception of Code S - Page 7

To create an Excel dataset containing participants and results from major/premier tournaments over the last 10 years from the aligulac database dump, follow these steps. We'll use Python and assume you can execute these steps in an environment where you have access to PostgreSQL and Python.

### Step 1: Set Up Your Database
1. **Unzip the SQL dump**: If you haven't already, unzip your `aligulac.sql.gz` file.
```bash
gzip -d aligulac.sql.gz
```

2. **Create a PostgreSQL database**:
```sql
CREATE DATABASE aligulac;
```

3. **Import the SQL dump into PostgreSQL**:
```bash
psql -d aligulac -f aligulac.sql
```

### Step 2: Install Required Python Libraries
You'll need `pandas` for data manipulation, `sqlalchemy` for database connection, and `openpyxl` to write to Excel.
```bash
pip install pandas sqlalchemy openpyxl psycopg2
```

### Step 3: Python Script to Extract Data and Export to Excel
Now, let's write a Python script that connects to the PostgreSQL database, queries the data, and writes it to an Excel file.

```python
import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime, timedelta

# Database connection
engine = create_engine('postgresql://username:password@localhost:5432/aligulac')

# Calculate the date 10 years ago
ten_years_ago = datetime.now() - timedelta(days=365*10)

# SQL Query to fetch major/premier tournament results from the last 10 years
query = """
SELECT p.tag AS player_tag, p.country, m.date, m.sca, m.scb, m.rca, m.rcb, e.name AS event_name
FROM match m
JOIN player p ON p.id = m.pla_id
JOIN event e ON e.id = m.eventobj_id
WHERE m.date >= %s AND e.big = True
ORDER BY m.date DESC;
"""

# Execute the query
df = pd.read_sql(query, engine, params={ten_years_ago})

# Export to Excel
output_file = 'tournament_results.xlsx'
df.to_excel(output_file, index=False, engine='openpyxl')

print(f"Data exported to {output_file}")
```

### Explanation:
1. *atabase Connection**: Adjust the `create_engine` line to match your PostgreSQL username, password, and database details.
2. **SQL Query**: This queries the `match`, `player`, and `event` tables. It joins these tables to fetch player tags, country codes, match dates, scores, races, and event names for matches that occurred in the last ten years in major/premier events (where `big = True`).
3. **Export to Excel**: The script then writes this data to an Excel file using the `pandas` library.

### Step 4: Execute the Script
Run the Python script in your environment. This will generate an Excel file named `tournament_results.xlsx` in your current directory containing the dataset you need.

This workflow assumes a functional Python environment with database access. Adjust credentials and connection details as necessary for your setup.