Exploring the World of Car Rental Management: Building a Python Application - Part 8

Article 8: Database Implementation

Welcome back to our series on building a Python-based car rental management system. In this segment, Part 8, we will dive into the practical side of database implementation. You'll learn how to set up a MySQL database using Python and create tables for cars, customers, and reservations.

Setting Up a MySQL Database with Python

We'll start by setting up the MySQL database using the mysql-connector library in Python. Ensure you have this library installed. If not, you can install it using pip:

pip install mysql-connector-python

Now, let's create a Python script for database setup. Below is a Python script that establishes a connection to MySQL and sets up our car rental database:

import mysql.connector

# Database configuration
config = {
    'user': 'csproject',
    'password': '2021',
    'host': 'localhost',
}

# Create a connection to MySQL
mycon = mysql.connector.connect(**config)

# Create a cursor object to execute SQL commands
mycursor = mycon.cursor()

# Create the car_rentals database
mycursor.execute("CREATE DATABASE IF NOT EXISTS car_rentals")

# Use the car_rentals database
mycursor.execute("USE car_rentals")

# Commit the changes and close the connection
mycon.commit()
mycon.close()

This script does the following:

1. Imports the mysql-connector library.

2. Defines the database configuration, including the user, password, and host.

3. Creates a connection to MySQL using the provided configuration.

4. Creates a cursor object to execute SQL commands.

5. Creates the car_rentals database if it doesn't already exist.

6. Switches to using the car_rentals database.

7. Commits the changes and closes the connection.

Creating Tables for Cars, Customers, and Reservations

Now that we have our database ready, let's proceed to create tables for cars, customers, and reservations.

# Create tables for cars, customers, and reservations
mycon = mysql.connector.connect(**config)
mycursor = mycon.cursor()

# Create the customers table
mycursor.execute("""
    CREATE TABLE IF NOT EXISTS customer (
        c_id VARCHAR(10) PRIMARY KEY,
        name VARCHAR(255),
        mobile_1 INT,
        mobile_2 INT,
        email VARCHAR(255),
        address VARCHAR(255),
        reservation_count INT,
        status CHAR(1)
    )
""")

# Create the cars table
mycursor.execute("""
    CREATE TABLE IF NOT EXISTS cars (
        car_id VARCHAR(10) PRIMARY KEY,
        company VARCHAR(255),
        model VARCHAR(255),
        year INT,
        engine CHAR(1),
        rear_ac CHAR(1),
        no_of_seats INT,
        infotainment_system CHAR(1),
        cost_per_day DECIMAL(10, 2),
        status CHAR(1)
    )
""")

# Create the reservations table
mycursor.execute("""
    CREATE TABLE IF NOT EXISTS reservations (
        r_id VARCHAR(10) PRIMARY KEY,
        car_id VARCHAR(10),
        c_id VARCHAR(10),
        reservation_start_date DATE,
        reservation_end_date DATE,
        duration INT,
        total_amount DECIMAL(10, 2)
    )
""")

# Commit the changes and close the connection
mycon.commit()
mycon.close()

This script does the following:

1. Creates the tables for customers, cars, and reservations.

2. Defines the table structure with attributes, data types, and primary keys.

3. Commits the changes and closes the connection.

With these tables in place, our database is ready to store customer information, car details, and reservations.

Conclusion

In Part 8, we've implemented the database for our car rental management system using Python and MySQL. We've now set up the database and created tables for customers, cars, and reservations. The database is a crucial part of our application, and it will store all the data needed for managing car rentals.

In Part 9, we will establish a connection between the Python application and the database. Start writing SQL queries for data retrieval and manipulation.

The Link to my code -> [github.com/bryanspacex/Rentals] (constantly updated)