Question

Establishing a method for collecting rent each month will make it easier to keep track of the rent collection process. Choose the number of houses and rent per house by your choice. Develop a python program to maintain the rent collection process. Update the database with the details of every month like the name of the tenant, house number, month of the rent, rent amount, EB bill amount, maintenance charge, etc., Print the details of rent collection for all houses from January 2019 to December 2019. Also, print the details of the month in which the tenant has joined/vacated house wise.

Answer 1

Program with explanation:

import mysql.connector

#following lines create a connection to the database

mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)

mycursor = mydb.cursor()

#create RentCollection table with all required fields

mycursor.execute("CREATE TABLE RentCollection (TenentName VARCHAR(255),HouseNumber int,MonthofRent VARCHAR(255),RentAmount int,EBbillAmount int,MaintCharge int,monJoined DATE)") #SQL command

#Get the number of houses as user input

no_of_houses = input("Enter number of houses: ")

#insert inital values to each house using INSERT command in sql
for x in range(no_of_houses):
sql = "INSERT INTO RentCollection (TenentName ,HouseNumber,MonthofRent ,RentAmount,EBbillAmount,MaintCharge) VALUES (%s, %s,%s, %s,%s, %s, %s)" #SQL command
val = tuple(map(int, input("Enter TenentName ,HouseNumber,MonthofRent ,RentAmount,EBbillAmount,MaintCharge,monJoined: ").split()))
mycursor.execute(sql, val)
mydb.commit()   #save the changes

#update required values to each house using UPDATE command in sql

for x in range(no_of_houses):
sql = "UPDATE customers SET TenentName=%S ,HouseNumber=%S,MonthofRent=%S ,RentAmount=%S,EBbillAmount=%S,MaintenanceCharge=%S WHERE HouseNumber=%S" #SQL command
val = tuple(map(int, input("Enter TenentName ,HouseNumber,MonthofRent ,RentAmount,EBbillAmount,MaintCharge, old houseno: ").split()))
mycursor.execute(sql, val)
mydb.commit() #save the changes

#print all rental information from table using SELECT command

mycursor.execute("SELECT TenentName ,HouseNumber,MonthofRent ,RentAmount,EBbillAmount,MaintCharge FROM RentCollection")   #SQL command
myresult = mycursor.fetchall()
for x in myresult:
print(x)

#print tenent information from table using SELECT command

mycursor.execute("SELECT TenentName ,HouseNumber,monJoined FROM RentCollection")    #SQL command
myresult = mycursor.fetchall()
for x in myresult:
print(x)