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

Article 9: Database Connectivity

Welcome back to our journey of creating a Python-based car rental management system. In Part 9, we will explore database connectivity - a critical aspect of our project. We'll establish a connection between our Python application and the MySQL database we set up earlier. We'll also write SQL queries to retrieve and manipulate data.

Connecting to the Database

Let's begin by establishing a connection to the database. We'll use the mysql-connector library to facilitate this connection. Here's how you can do it:

import mysql.connector

# Database configuration
db_config = {
    'user': 'csproject',
    'password': '2020',
    'host': 'localhost',
    'database': 'car_rentals'  
}

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

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

In this code snippet:

We import the mysql-connector library.
Define the database configuration, including the user, password, host, and the name of the database (in this case, 'car_rentals').
Create a connection to MySQL using the provided configuration.
Create a cursor object to execute SQL commands.

Writing SQL Queries

Now, let's write some SQL queries to interact with our database. We'll start by inserting data into the 'customer' table and then fetching customer details.

Inserting Data:

# Sample data for inserting into the 'customer' table
data = ('C1', 'Bron Sam', 12345678, 87654321, 'bronsam@gmail.com', '123 Main St', 0, 'A')

# SQL query to insert data into the 'customer' table
insert_query = """
    INSERT INTO customer (c_id, name, mobile_1, mobile_2, email, address, reservation_count, status)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""

mycursor.execute(insert_query, data)
mycon.commit()

Fetching Customer Details:

# SQL query to retrieve customer details
select_query = "SELECT * FROM customer WHERE c_id = 'C1'"

mycursor.execute(select_query)

customer_data = mycursor.fetchone()

if customer_data:
    print("Customer ID:", customer_data[0])
    print("Name:", customer_data[1])
    print("Mobile 1:", customer_data[2])
    print("Mobile 2:", customer_data[3])
    print("Email:", customer_data[4])
    print("Address:", customer_data[5])
    print("Reservation Count:", customer_data[6])
    print("Status:", customer_data[7])
else:
    print("Customer not found.")

In the first code block, we insert a new customer into the 'customer' table using an SQL INSERT query. The execute() function takes care of inserting the data.

The second code block demonstrates how to retrieve customer details from the database. We execute a SELECT query and use fetchone() to get the customer's data. If the customer exists, their details are printed; otherwise, a "Customer not found" message is displayed.

Conclusion

In Part 9, we've covered the essential concept of database connectivity and how to write SQL queries in Python to interact with our MySQL database. We inserted a new customer into the 'customer' table and retrieved their details.

In Part 10, We'll be adding new Customers. Explaining how to add new customers to the system. Generating unique customer IDs.

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

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