Travel Agency Database Models

This repository contains Python SQLAlchemy model classes for a travel agency database schema. The models are designed to work with a MySQL database hosted on GibsonAI.

📋 Database Schema

The database consists of four main entities:

• TravelUser - Users of the travel agency platform
• TravelDestination - Available travel destinations
• TravelBooking - Bookings made by users for destinations
• TravelReview - Reviews written by users for destinations

🏗️ Model Structure

TravelUser

• id - Primary key (auto-increment)
• uuid - Unique identifier for external references
• first_name, last_name - User's name
• email - Unique email address
• password - Hashed password
• date_created, date_updated - Timestamps

Relationships:

• bookings - One-to-many with TravelBooking
• reviews - One-to-many with TravelReview

TravelDestination

• id - Primary key (auto-increment)
• uuid - Unique identifier
• name - Unique destination name
• description - Detailed description
• price - Price per person (Decimal)
• rating - Average rating 0.0-5.0
• date_created, date_updated - Timestamps

Relationships:

• bookings - One-to-many with TravelBooking
• reviews - One-to-many with TravelReview

TravelBooking

• id - Primary key (auto-increment)
• uuid - Unique identifier
• user_id - Foreign key to TravelUser
• destination_id - Foreign key to TravelDestination
• booking_date - When booking was made
• date - Travel date
• status - Enum: 'pending', 'confirmed', 'cancelled'
• number_of_people - Number of travelers
• date_created, date_updated - Timestamps

Relationships:

• user - Many-to-one with TravelUser
• destination - Many-to-one with TravelDestination

TravelReview

• id - Primary key (auto-increment)
• uuid - Unique identifier
• user_id - Foreign key to TravelUser
• destination_id - Foreign key to TravelDestination
• rating - Rating 1.0-5.0 (Decimal)
• comment - Optional review text
• review_date - When review was submitted
• date_created, date_updated - Timestamps

Relationships:

• user - Many-to-one with TravelUser
• destination - Many-to-one with TravelDestination

🚀 Installation

1. Install dependencies:

   pip install -r requirements.txt
   

2. Set up environment variables: Create a .env file with your database connection details:

   DATABASE_URL=mysql+pymysql://username:password@host:port/database
   # Or use individual components:
   DB_HOST=your-host
   DB_USER=your-username
   DB_PASSWORD=your-password
   DB_NAME=your-database
   DB_PORT=3306
   

3. Initialize the database:

   python database.py
   

💻 Usage Examples

Basic Usage

from models import TravelUser, TravelDestination, TravelBooking, TravelReview
from database import get_db_session

# Get a database session
session = get_db_session()

try:
    # Create a new user
    user = TravelUser(
        first_name="John",
        last_name="Doe", 
        email="[email protected]",
        password="hashed_password"
    )
    session.add(user)
    session.commit()
    
    # Query users
    users = session.query(TravelUser).all()
    for user in users:
        print(f"{user.full_name} - {user.email}")
        
finally:
    session.close()

Using the DatabaseManager Utility

from database import get_db_session
from models import DatabaseManager

session = get_db_session()
db_ops = DatabaseManager(session)

try:
    # Get user by email
    user = db_ops.get_user_by_email("[email protected]")
    
    # Get user's bookings
    bookings = db_ops.get_user_bookings(user.id)
    
    # Create a new booking
    booking = db_ops.create_booking(
        user_id=user.id,
        destination_id=1,
        travel_date=date(2024, 12, 25),
        number_of_people=2
    )
    
    # Create a review
    review = db_ops.create_review(
        user_id=user.id,
        destination_id=1,
        rating=4.5,
        comment="Amazing trip!"
    )
    
finally:
    session.close()

Model Properties and Methods

# User properties
user = session.query(TravelUser).first()
print(user.full_name)  # "John Doe"
print(user.to_dict())  # Dictionary representation

# Destination properties  
destination = session.query(TravelDestination).first()
print(destination.average_rating)  # Calculated from reviews
print(destination.review_count)    # Number of reviews

# Booking properties
booking = session.query(TravelBooking).first()
print(booking.total_price)    # price * number_of_people
print(booking.is_upcoming)    # True if date > today
print(booking.is_confirmed)   # True if status is confirmed

# Review properties
review = session.query(TravelReview).first()
print(review.rating_stars)    # "★★★★☆" visual representation

📁 File Structure

├── models.py          # SQLAlchemy model definitions
├── database.py        # Database connection and configuration
├── examples.py        # Usage examples and sample data
├── requirements.txt   # Python dependencies
└── README.md         # This file

🛠️ Features

• Complete SQLAlchemy Models - Fully defined models with relationships
• Type Hints - Modern Python with type annotations
• Utility Methods - Convenient properties and methods on models
• Database Manager - Helper class for common operations
• Sample Data - Example data creation and queries
• Connection Management - Robust database connection handling
• GibsonAI Integration - Pre-configured for GibsonAI hosted databases

🔧 Configuration

The models are designed to work with the existing GibsonAI travel agency database schema. The connection string in database.py is pre-configured for the GibsonAI Production database:

# Default GibsonAI connection (replace with your actual credentials)
connection_string = "mysql+pymysql://us_PzN8AjvnKeSig5mgGZW:uWr90z6WZe9GPmbkOPMaPipYC4FDy5dz@production-mysql-assembly.cluster-colojrqwkt2v.us-east-1.rds.amazonaws.com/db_PzN8AjvnKeSig5mgGZW"

🧪 Running Examples

To see the models in action:

1. Create sample data:

   # In examples.py, uncomment:
   create_sample_data()
   

2. Run query examples:

   # In examples.py, uncomment:
   query_examples()
   

3. Try booking workflow:

   # In examples.py, uncomment:
   booking_workflow_example()
   

📦 Dependencies

• SQLAlchemy 2.0+ - Modern Python SQL toolkit and ORM
• PyMySQL - Pure Python MySQL client
• python-dotenv - Environment variable management
• mysql-connector-python - Official MySQL driver

🤝 Contributing

These models are designed to be extensible. You can:

• Add new model methods and properties
• Extend relationships
• Add validation logic
• Create additional utility functions
• Implement caching or other optimizations

📄 License

This code is provided as an example for working with GibsonAI travel agency databases.